How to connect to secondary replica

  • jdc

    Old Hand

    Points: 367

    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

  • Sue_H

    SSC Guru

    Points: 90287

    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

     

  • jdc

    Old Hand

    Points: 367

    This is how our AAG configured:

    Having this configuration, how do I restrict developers running select queries to go to secondary replica ?

    AAG

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32396

    1. 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.

  • jdc

    Old Hand

    Points: 367

    Thanks Beatrix,

    Can developers use "Secondary instance name" from management studio so that they can connect to Secondary replica databases?

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32396

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply