SQL Server – Find Direct Connections to Database Server Instead of Always On Listener

availability-groupssql server

We recently created a SQL Server Always On Availability Group. I have told developers to change their application's connection strings from main server to the listener. I want to check which applications (or servers) connect to main server and which applications connect to the listener. How can i do it?

Best Answer

I covered this through two main posts. The first gives you an idea how to see what is being used, currently. The second tells you how to find which connections are read only routed by exploiting the fact that SQL Server can listen on multiple ports and that read only routing accepts whatever endpoint url you give it which is directly given back to the client - this means you can setup specific items just for read only routing and report back on it.

It is much more accurate and no need to go through extended events. You're specifically looking for the second post, but they are related and will give you a better picture when combined together.

  1. Finding What Availability Group Listeners Applications Are Using To Connect
  2. Finding Which Connections Have Been Read Only Routed