0

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?

Improve this question
4
  • 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
    Commented Mar 22, 2019 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
    Commented Mar 22, 2019 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
    Commented Mar 25, 2019 at 18:15
  • this is a good article in case someone is trying to verify ReadOnly dba.stackexchange.com/questions/98056/…
    – dani
    Commented Apr 5, 2019 at 15:00

1 Answer 1

Reset to default
1

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 and acknowledge you have read our privacy policy.

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