Aug 272013
You’ve spent a lot of time planning and building out a new SQL Server 2012 environment complete with Availability Group Listeners, but how can you be sure the end users are connecting to the listener and not directly to the SQL Server instance?
So why would we care about this?  To begin with, if the users are not connecting to the listener, then upon a failover to another replica, those users would have to connect to a different SQL Server instance name.  Having a single point of connection is crucial for the high availability process to work correctly.
In a previous blog post, we setup an Availability Group Listener,, with two IP addresses: &  We’ll use this one for our example.

The DMV, sys.dm_exec_connections, contains information about each connection to a SQL Server instance, and can be used to answer our question.
Open a TSQL connection to either the Availability Group listener, and execute the following command.

FROM sys.dm_exec_connections;

The local_net_address and local_tcp_port columns will display the IP address and port number of the client’s connection target.  This will be the connection string the users entered to connect to the SQL Server instance.
If the IP address and port number match the Availability Group IP, then you’re in good shape.  If they do not match, then some users are likely connecting directly to the SQL Server instance, and that will need to be changed.
By joining the sys.dm_exec_sessions DMV, you’ll also be able to get the hostname and program name of each connection.

FROM sys.dm_exec_connections ec
JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id;

As you can see in this picture, we have one connection on session_id 62 that is connecting directly to the SQL Server instance and not the to the Availability Group Listener.  At this point, I would track down that user, and have them use the correct connection string.
Using this DMV will allow you to verify the users are connecting to SQL Server using the correct connection strings, and help prevent unneeded outages during a failover between replicas.

  2 Responses to “How to Tell If Your Users are Connecting to the Availability Group Listener”

  1. Hi Patrick Keisler,Really useful this solution. It’s to find out what exactly, I’m looking for.. if possible would you please add the SQL query text also running by any SSMS users who are connected with the listener. Again thank you very much for your help towards the SQL DBA community!

  2. CP Patel,

    Patrick’s solution has already given you the infomation you need to find the active query. Look at the session_id. using Activity Monitor or whoisactive.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



This site uses Akismet to reduce spam. Learn how your comment data is processed.