1

I configured SQL Server AlwaysOn to redirect Read-Only to Secondary and tried to check with ApplicationIntent=ReadOnly setting in SSMS by connecting to the Listener. But my connection is always going to Primary only as revealed by

SELECT @@SERVERNAME

Does anyone know what is wrong with the settings or reason for this? I checked altering the settings but didn't work.

Setup Details:

SQL Server 2017
AG Properties Using Admin account to test the routing.

AO AG Properties

  • Have you specified the database you're connecting to? AG connections resolve at the DB level, not the server level. – George.Palacios Sep 9 at 12:45
  • @George.Palacios Thank you! It worked by mentioning "Initial Catalog" parameter along with ApplicationIntent. Further, I updated my Login's Default DB to point to one of AG Databases and then without "Initial Catalog" parameter also, my Reads were routed to Secondary Replica. --In 'thoughts'... – LonelyRogue Sep 9 at 13:13
1

Does anyone know what is wrong with the settings or reason for this? I checked altering the settings but didn't work.

You may verify the read-only routing configuration based on this post

Once all set (upon validation): If you're using SQL Login, try to mention default database that is part of AG, then your connection must be re-direct to secondary replica.

or mention following parameter from SSMS connection. This is how usually every application connects.

ApplicationIntent=READONLY; Initial Catalog=YourDatabase
  • Thank you! It worked by mentioning "Initial Catalog" parameter along with ApplicationIntent. Further, I updated my Login's Default DB to point to one of AG Databases and then without "Initial Catalog" parameter also, my Reads were routed to Secondary Replica. --In 'thoughts'... – LonelyRogue Sep 9 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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