Find direct connections to database server instead of SQL Server Always On Listener

Question

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?

Answer 1

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

Answer 2

Use the Extended Events technique described here:

From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly?

Not necessarily an exact duplicate of this question, but the same techniques will be used to produce the answer. (I wish it was easier.)