First published on MSDN on Sep 06, 2016
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.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.