Get Availability Group Details

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