Get Availability Group Details

Applicability:

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

Credits:

Author: ChillyDBA

Date: 7 Jun 2020

Description

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.

Code


--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