If you are using SQL Server Availability Group and you have multiple availability groups and instances; if you want to list Availability Group Names, Database Names, IPs, Ports, DNS Names, you can query the system dmvs instead of looking through ssms (SQL Server Management Studio).
You can find these informations in the following T-SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT AG.name AS [Availability_Group_Name], ISNULL(agstates.primary_replica, '') AS [Primary_Replica_Server_Name], ISNULL(arstates.role, 3) AS [Local_Replica_Role], --arstates.role, dbcs.database_name AS [Database_Name], LEFT(agli.ip_configuration_string_from_cluster,29) as IP, gli.port AS Port, gli.dns_name AS [DNS_NAME] FROM master.sys.availability_groups AS AG LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates ON AG.group_id = agstates.group_id INNER JOIN master.sys.availability_replicas AS AR ON AG.group_id = AR.group_id INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id --AND arstates.is_local = 1 INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs ON arstates.replica_id = dbcs.replica_id LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id INNER JOIN sys.availability_group_listeners as gli on dbrs.group_id=gli.group_id INNER JOIN sys.availability_group_listeners as agli on agli.listener_id=gli.listener_id |
