First published on MSDN on Sep 06, 2016
Background Information
While on site helping with AlwaysOn Availability Groups, it was tasked to a DBA to meet with all of the application owners and figure out what listener they were currently using in their connection strings. The DBA looked over at me and said, "I wish there was a DMV that showed me connection strings, it'd make my life much easier right now!" While there isn't a DMV that holds connection string information, there are tidbits of information that we can use to get the listener used by the applications.
Windows Clustering and Availability Group Listeners
The way availability group listeners work is by having an associated client access point resource in the availability group cluster resource group. Each client access point can have one or more IP addresses associated with it, generally in an 'OR' dependency between all of the addresses. The client access point is what SQL Server calls the Availability Group Listener. Each of the client access points of which SQL Server is dependent upon will show up in the sys.availability_group_listeners DMV and have associated configuration information (IP addresses) in the sys.availaiblity_group_Listener_IP_Addresses DMV.
Gathering The Information
We now know where the listener configuration information is kept, but how do we bring that back to know what the client used?
There is a DMV that holds just this information, called sys.dm_exec_connections and we can gather more information about who the connection is by joining to sys.dm_exec_sessions . The connections DMV holds the IP address (if TCP protocol is used) the client connected to. Since we know that we can grab the information about what IP addresses each listener has, we can figure out from the IP list which listener was used to connect. We can also gather helping information such as the login, client ip address, and potentially the client application name. Please note that some of the information populated in the DMVs are set in the client driver and some of the fields can be set by users or programmers and override the defaults (ex: Application Name) and lead to incorrect assumptions. I have rarely witnessed application programmers overriding these values.
Here is a query that can be used to pull back a subset of information about what clients are connected and which listener they used to connect. Additionally, a listener may not have been used and we may want to point this out to the application owner.
SELECT es.login_name ,es.program_name ,ec.client_net_address ,ec.client_tcp_port ,agl.dns_name ,aglip.ip_address ,agl.port FROM sys.availability_group_listeners agl INNER JOIN sys.availability_group_listener_ip_addresses aglip ON agl.listener_id = aglip.listener_id INNER JOIN sys.dm_exec_connections ec ON ec.local_net_address = aglip.ip_address INNER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id UNION ALL SELECT es.login_name ,es.program_name ,ec.client_net_address ,ec.client_tcp_port ,@@SERVERNAME AS [dns_name] ,sr.value_data AS [ip_Address] ,ec.local_tcp_port AS [port] FROM sys.dm_server_registry sr INNER JOIN sys.dm_exec_connections ec ON sr.value_name = 'IpAddress' AND ec.local_net_address = sr.value_data INNER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
The results above will give the login used, program name (if populated, notice the spoofed name in row 4), the address off the client application with port, the dns_name column is the listener or server address used in the client applications' connection string, the IP address associated with the dns_name, and finally the port the client connected to on the SQL Server. You can see in this example that clients are connecting using three different values. RORListen is a listener, RORListen2 is also a listener, and SQL2014N2 which is the stand alone server name.
Now you can work with your application owners if any changes need to be made with precision, rather than going through each and every application connection string!
Updated Apr 28, 2020
Version 4.0SQLPFE
Brass Contributor
Joined March 15, 2019
Core Infrastructure and Security Blog
Follow this blog board to get notified when there's new activity