1

For AlwaysON AG, if Readable Secondary is set to 'Yes', do you use the listener name or the secondary replica instance name to connect for read-only purposes?

I know when Read Secondary is set to 'Read-intent only', you are required to specify ApplicationIntent=readonly and the database to be routed to the secondary replica. However, my understanding is that if you set it to 'Yes' instead of 'Read-intent only', you have to explicitly specify the secondary replica instance name instead of the listener name. Is that correct?

Improve this question
0

1 Answer 1

Reset to default
1

If you want to access a secondary replica in an Always On configuration, the Readable Secondary property must be set to "Read-Intent" or "Yes".

If you set "Read-Intent" then your connection string must specify ApplicationIntent=ReadOnly otherwise the connection is rejected. If you set "Yes", your connection is allowed without ApplicationIntent=ReadOnly, however, any non-read queries you run will raise an error.

If you set "No" then all connections to the secondary will return an error. NOTE: That these conditions only apply when specifying an AG-joined database in the connection string. If you connect to a system database or non-AG database then you can connect to a secondary without issue, but you can't access the AG-joined databases. See here for more info.

Lastly, when connecting you have two options. Firstly, you can simply connect to the secondary replica via the normal name for that replica. Secondly, you can connect via the listener name.

When connecting via listener name, if you have configured read-only routing and your connection string specifies ApplicationIntent=ReadOnly then you will be routed to a readable secondary for your connection. If you do not specify ApplicationIntent=ReadOnly or you have not configured read-only routing, then you will connect to the primary replica. See here for more info.

Improve this answer
1
  • Thank you, HandyD for the answer. Just wanted to confirm if my client does not have the option to specify ApplicationIntent=ReadOnly, then I cannot use the listener name and instead have to explicitly mention the secondary replica's name. Your answer has confirmed that for me. Your help is much appreciated.
    – RaptorsDBA
    May 9, 2019 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.