How to Tell If Your Users are Connecting to the Availability Group Listener

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, AdventureWorks.mcp.com, with two IP addresses:   192.168.1.55 & 192.168.2.55.  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.

SELECT
     session_id
    ,local_net_address
    ,local_tcp_port
FROM sys.dm_exec_connections;
GO

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.

SELECT
     ec.session_id
    ,es.host_name
    ,es.program_name
    ,local_net_address
    ,local_tcp_port
FROM sys.dm_exec_connections ec
    JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id;
GO

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.