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.
