I configured my HA SQL Server to have a readonly. When I entered the following command

sqlcmd -S servername -E -d db_name -K ReadOnly

It points correctly to the secondary server.

Then in SSMS I have my applicationintent=ReadOnly. However, when I run a trace all the queries point to the listener instead of the secondary HA server.

Is this a normal behaviour or am I missing something in my readonly configuration?

  • I'm not sure if there is another way to do this, but when creating a trace, you can just connect to secondary instance name instead of the listener. – Danilo Braga Mar 22 '19 at 19:30
  • How are you tracing? Read-Only client connections connect initially to the AG Listener (which is always on the Primary), which, if read-only routing is configured, passes back the name of the secondary they should connect to instead. – David Browne - Microsoft Mar 22 '19 at 20:50
  • thanks i traced both servers instead of just the listener and indeed the readonly queries were routed to the secondary, so the setup was done correctly. – dani Mar 25 '19 at 18:15
  • this is a good article in case someone is trying to verify ReadOnly dba.stackexchange.com/questions/98056/… – dani Apr 5 '19 at 15:00

Without knowing the exact parameters you've used, it's hard to be sure. But based on the example code you provided for sqlcmd:

sqlcmd -S servername -E -d db_name -K ReadOnly

I would bet you used the instance name of the current secondary replica - which would explain why you saw all the traffic there in your trace.

When you connected via SSMS, I imagine you used the listener name - which would go through the listener to connect to the primary, and then connect to the appropriate secondary replica (based on read only routing rules, load balancing, if there are any secondary replicas actually available, etc).

PS: to clarify one thing regarding this statement:

However, when I run a trace all the queries point to the listener instead of the secondary HA server

The listener is neither the primary nor secondary, so the "instead of" relationship you mention here is a little confusing. The listener is a virtual network name that facilitates connecting to the different replicas in the AG.

| improve this answer | |

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.