How to connect to secondary replica
-
Hi,
I want developers to go to secondary replica for running select queries. So, I'm testing this by connecting to SSMS and providing "ApplicationIntent=ReadOnly" under options->additional parameters.
But it is still going to Primary. I'm using below query to check which replica I'm connected to and it is showing as Primary. Please advise.
SELECT ars.role_desc
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.availability_groups ag
ON ars.group_id = ag.group_id
AND ars.is_local = 1
-
It's hard to tell which piece is missing - the configuration, connecting to listener, etc. This blog has a walk through on how this connection works - try going through to see if you missed something:
End to End – Using a Listener to Connect to a Secondary Replica (Read-Only Routing)
Sue
-
This is how our AAG configured:
Having this configuration, how do I restrict developers running select queries to go to secondary replica ?
-
- Connections in Primary Role: change to Allow Read/Write connections.
2. Readable Secondary: change to Read-intent only
3. In the Availability Group settings under Read-Only Routing, check that you have set up the read-only routing URLs and populated the Read-only routing list. Most people miss this step.
4. Assuming that the developers are using SSMS, ensure they click on Options before logging in. They need to specify the database they want, and then under Additional Connection Parameters they must enter ApplicationIntent = ReadOnly.
-
Thanks Beatrix,
Can developers use "Secondary instance name" from management studio so that they can connect to Secondary replica databases?
-
Hi- the best thing to do is test it yourself, and I'd probably use the listener name, but it should work if they do as described:
Click on Options before logging in. Specify the database, and then under Additional Connection Parameters, enter ApplicationIntent = ReadOnly.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply