LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon