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 NULLDROP TABLE #AGResultsCREATE 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 lINNER JOIN sys.availability_group_listener_ip_addresses lipON l.listener_id = lip.listener_idSELECT @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_nameFROM master.sys.dm_hadr_cluster_membersWHERE 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 = nameFROM master.sys.availability_groups GroupsINNER JOIN master.sys.availability_replicas ReplicasON Groups.group_id = Replicas.group_idINNER JOIN master.sys.dm_hadr_availability_group_states StatesON Groups.group_id = States.group_idWHERE 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