LLI Wiki — My Digital Knowledge Base

List availability groups and the local replica role

T-SQL query listing the availability groups of an instance with the current primary replica and the role of the local replica.

List the availability groups defined on the instance, with the name of the current primary replica and the role of the local replica in each group.

Query

SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
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
ORDER BY [Name] ASC

Notes

Mastodon LLIMastodon SocialFosstodon