May 6, 2016 at 9:50 am
Hi guys!...
I'm implementing SQL Server 2015 Enterprise on my actual client, using Always On Availability Groups. Proceses like Reporting, ETL (extraction of data), and read workload are being redirected to the secondary replica using the Listener and the "initial catalog" and "applicationintent=readonly" parameters.
The problem is!... my client wants to Force all non-admin users to connect always to the secundary-redeable replica. BUT!... make this automatically. The client not want that the user have to add parameters to the connection in SQL Server Management Studio.
I'm testing Trigger logon with no results... really I can't find a way out to this.
It's this posible??
Thanks guys!!
May 10, 2016 at 2:54 pm
juanc.aguirre (5/6/2016)
Hi guys!...I'm implementing SQL Server 2015 Enterprise on my actual client, using Always On Availability Groups. Proceses like Reporting, ETL (extraction of data), and read workload are being redirected to the secondary replica using the Listener and the "initial catalog" and "applicationintent=readonly" parameters.
The problem is!... my client wants to Force all non-admin users to connect always to the secundary-redeable replica. BUT!... make this automatically. The client not want that the user have to add parameters to the connection in SQL Server Management Studio.
I'm testing Trigger logon with no results... really I can't find a way out to this.
It's this posible??
Thanks guys!!
No way that I know of. You can easily point reports and code to the readonly replica with the app intent switch but SSMS also requires the additional parameters. You could direct them directly to the server hosting the read-only and not go through the listener, but if you have to failover, they will suddenly be on the writeable replica.
May 11, 2016 at 1:32 pm
No way that I know of. You can easily point reports and code to the readonly replica with the app intent switch but SSMS also requires the additional parameters. You could direct them directly to the server hosting the read-only and not go through the listener, but if you have to failover, they will suddenly be on the writeable replica.
Aparently this is practically impossible to do in an "clean" way. I have a open support ticket with Microsoft Premier, and the answer was that the only way is to create a trigger in the logon procces to disconnect users that are attempting to connect to the primary node, but there is no way to redirect that connections to the secondary automatically. So, I will continue with my investigation for a couple of weeks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply