次の方法で共有

Finding What Availability Group Listeners Applications Are Using To Connect

  • [アーティクル]

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.

[sql]
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
[/sql]

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!

Comments

  • Anonymous
    September 20, 2016
    I was using this today to check whether applications are using the correct listener names or even possibly connecting directly to nodes. I did this by altering the script to include which group/listener the respective database is in and reporting where they're mismatched.My question is how these DMVs look when read-only routing is in use and a read-intent connection has been redirected to another server; will it still show the listener as the dns_name, or will it show differently? I haven't yet been able to test this myself.
    • Anonymous
      September 20, 2016
      I managed to give this a test. When read-only routing is in use with read-intent connections they will be reported in the second result set (as if they were connecting directly to the server instead of using a listener).
      • Anonymous
        September 22, 2016
        Cody,That's a great idea! Yes, as you've witnessed in your testing they will look like they are connecting directly to the nodes. Why is this? When the connection goes through the listener and the application connection string is setup for read only routing, assuming the connection can be re-routed, SQL Server will reply to the client driver with the tcp endpoint specified in the read only routing configuration for the node it is being routed to. When this happens, the client driver will disconnect and create a new connection to the endpoint given back to the client driver.If you would like, send me an email and I can tell you how to set this up so you can see which are being read only routed... or I can make it another blog post :)
    • Anonymous
      October 12, 2017
      Can you share your modification?