none
SQL server AOAG RRS feed

  • General discussion

  • Hi,

    We have configured AOAG with one secondary replica (Read-only option=YES).

    I have given read & write access to the application user on primary DB. I can’t give it on secondary as it is a read-only copy.

    One application is able to connect to the primary by using listener hostname at connection string without any issues.

    Another application wants to use the secondary replica for only read statements. Hence, we are using secondary hostname at connection string. We are able to connect to the secondary server name by using SQL login. However, DB is not accessible. How to access the secondary DB with a SQL login?

    I guess AOAG works as follows.

    If we keep option: Read-only=YES, and at connecting string if you mention listener name and applicationintent=readonly, the AOAG will connect to secondary replica automatically when we run select statements. If it is yes, how to we will come to know that it is using secondary or primary.

    Please correct me if I am wrong.


    • Edited by mito access Thursday, December 15, 2016 10:06 AM
    Thursday, December 15, 2016 9:49 AM

All replies

  • We are able to connect to the secondary server name by using SQL login. However, DB is not accessible. How to access the secondary DB with a SQL login?

    It sounds like you have mismatched SIDs for the SQL Login. Check sys.server_principals on the primary and secondary for the SQL Login and compare it to the SID on the secondary. Most likely they won't match and you'll need to drop the login on the secondary and re-create it using the 'SID=' clause of CREATE LOGIN.

    I guess AOAG works as follows. If we keep option: Read-only=YES, and at connecting string if you mention listener name and applicationintent=readonly, the AOAG will connect to secondary replica automatically when we run select statements. If it is yes, how to we will come to know that it is using secondary or primary.

    Not quite.

    If Read Only Routing has been configured, a secondary marked as readable is available, the listener is used in the connection string, application intent has been specified as readonly, and a default database is specified then read only routing will automatically redirect the connection to a different replica.

    The way to check this is test it using SSMS or any other application and run 'select @@SERVERNAME', where you should see that you're not connected to the primary.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Thursday, December 15, 2016 7:45 PM
    Answerer