List AlwaysOn availability groups, replicas and listener
DMV query showing, for each AlwaysOn availability group, its listener and the state of each replica.
The following query lists the AlwaysOn availability groups with their listener and the state of each replica.
SELECT C.name,
dns_name,
ip_configuration_string_from_cluster AS ListenerIp,
CS.replica_server_name,
CS.join_state_desc,
RS.role_desc,
RS.operational_state_desc,
RS.connected_state_desc,
RS.synchronization_health_desc
FROM sys.availability_groups_cluster AS C
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS CS
ON CS.group_id = C.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS RS
ON RS.replica_id = CS.replica_id
LEFT OUTER JOIN sys.availability_group_listeners AS GL
ON C.group_id = GL.group_id;
| Column | Content |
|---|---|
name |
Name of the availability group |
dns_name |
DNS name of the listener |
ListenerIp |
IP configuration of the listener |
replica_server_name |
Instance hosting the replica |
join_state_desc |
Join state of the replica in the group |
role_desc |
Role of the replica (PRIMARY, SECONDARY) |
operational_state_desc |
Operational state of the replica |
connected_state_desc |
Connection state of the replica |
synchronization_health_desc |
Synchronization health (HEALTHY, PARTIALLY_HEALTHY, NOT_HEALTHY) |
Notes
The outer join also returns the groups that have no listener, with empty listener columns.