Question :
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?
Answer :
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.