Cool Tips‎ > ‎Clustering‎ > ‎

Get Availability Group Details


                 SQL Server 2000:        N/A
                 SQL Server 2005:        N/A
                 SQL Server 2008:        N/A
                 SQL Server 2008R2:    N/A
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Tested


Author: ChillyDBA
Date:    7 Jun 2020


Even with a strong naming convention, an experienced (ie old) DBA like me has trouble context-switching between the terminology of legacy SQL Server Clusters and new SQL Server Availability Groups.
This is especially challenging in a mixed version environment like my current one, where all SQL Server versions from 2000 (!) to 2017 are in operation concurrently

To help myself, I wrote a little helper code to return a summary of the Availability Group setup.
To reduce the complexity, it is hard-coded to return details of up to 4 nodes only, as this will hopefully cover the majority of cases.  I would have no way of testing code that operated on a dynamic number of nodes anyway.


--select * from master.sys.dm_hadr_cluster --select * from master.sys.dm_hadr_cluster_members --select * from master.sys.dm_hadr_cluster_networks --select * from sys.availability_group_listener_ip_addresses --select * from sys.availability_group_listeners --select * from sys.availability_groups IF (SELECT OBJECT_ID('tempdb..#AGResults')) IS NOT NULL DROP TABLE #AGResults CREATE TABLE #AGResults ( ResultID INT IDENTITY(1,1) ,ResultName VARCHAR(100) ,ResultValue VARCHAR(100) ,Instructions VARCHAR(100) ) --****************************************************** --*** NOTE: Assumes only one AG on the server *** --****************************************************** DECLARE @AG_Listener_Name VARCHAR(100) ,@AG_Listener_IP VARCHAR(100) ,@AG_Listener_Subnet VARCHAR(100) ,@WFCS_Cluster_name VARCHAR(100) ,@WFCS_Node1 VARCHAR(100) ,@WFCS_Node2 VARCHAR(100) ,@WFCS_Node3 VARCHAR(100) ,@WFCS_Node4 VARCHAR(100) ,@AGPrimaryServerName VARCHAR(100) ,@SnapshotDate DATETIME ,@AG_Group_Name VARCHAR(100) ,@AG_Faiover_Command VARCHAR(100) SELECT @AG_Listener_Name = UPPER(l.dns_name) -- AS AG_Listener_Name ,@AG_Listener_IP = lip.ip_address -- AS AG_Listener_IP ,@AG_Listener_Subnet = lip.ip_subnet_mask -- AS AG_Listener_Subnet FROM sys.availability_group_listeners l INNER JOIN sys.availability_group_listener_ip_addresses lip ON l.listener_id = lip.listener_id SELECT @WFCS_Cluster_name = UPPER(cl.cluster_name) -- AS WFCS_Cluster_name FROM master.sys.dm_hadr_cluster cl ;WITH cte_Nodes AS ( SELECT ROW_NUMBER() OVER (ORDER BY Member_Name) AS RowNumber, UPPER(Member_Name) AS Member_name FROM master.sys.dm_hadr_cluster_members WHERE member_type_desc = 'CLUSTER_NODE' ) SELECT @WFCS_Node1 = (SELECT Member_Name FROM cte_Nodes WHERE RowNumber = 1) ,@WFCS_Node2 = (SELECT Member_Name FROM cte_Nodes WHERE RowNumber = 2) ,@WFCS_Node3 = (SELECT Member_Name FROM cte_Nodes WHERE RowNumber = 3) ,@WFCS_Node4 = (SELECT Member_Name FROM cte_Nodes WHERE RowNumber = 4) SELECT @AGPrimaryServerName = replica_server_name ,@SnapshotDate = GETDATE() ,@AG_Group_Name = name FROM master.sys.availability_groups Groups INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id WHERE primary_replica = replica_server_name -- create the manual AG faillover command SELECT @AG_Faiover_Command = 'ALTER AVAILABILITY GROUP ' + @AG_Group_Name + ' FAILOVER' INSERT #AGResults(ResultName, ResultValue, Instructions) VALUES ('Availability Group Listener IP', @AG_Listener_IP, '') ,('Availability Group Listener SUBNET', @AG_Listener_Subnet, '') ,('Availability Group Listener Name', @AG_Listener_Name, 'Connect to SQL Server using this name') ,('Availability Group Name', @AG_Group_Name, 'Connect to SQL Server using this name') ,('Windows Failover Cluster Name', @WFCS_Cluster_name, 'DO NOT Connect to SQL Server using this name') ,('Windows Failover Node Name #1', ISNULL(@WFCS_Node1, 'n/a'), '') ,('Windows Failover Node Name #2', ISNULL(@WFCS_Node2, 'n/a'), '') ,('Windows Failover Node Name #3', ISNULL(@WFCS_Node3, 'n/a'), '') ,('Windows Failover Node Name #4', ISNULL(@WFCS_Node4, 'n/a'), '') ,('Availability Group Primary Server Name', ISNULL(@AGPrimaryServerName, 'n/a'), '') ,('Availability Group Manual Failover Code', ISNULL(@AG_Faiover_Command, 'n/a'), 'MUST be run on any node other than ' + @AGPrimaryServerName) ,('Snapshot Date', CONVERT(VARCHAR(20), @SnapshotDate, 113), '') SELECT * FROM #AGResults ORDER BY ResultID

Andy Hughes,
Jun 7, 2020, 8:49 AM