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.
Best Answer
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.