LLI Wiki — My Digital Knowledge Base

Availability group role of each database and sync state

Show for each database whether it is replicated (primary or secondary) and list the synchronization state of the local replica's databases.

Two queries show, for each database of an instance, whether it belongs to an availability group and what role its local copy has. The first gives one row per database. The second lists the synchronization state of the databases held by the local replica.

Replication role and group of each database

AGType is NOT REPLICATED for a database outside any availability group, PRIMARY when the local copy is the primary, and SECONDARY otherwise. AGName is N/A when the database is not replicated.

-- Get for a each database a row with:
-- name, AGType, AGName
-- name: name of the database
-- AGType: NOT REPLICATED/PRIMARY/SECONDARY
-- AGName: N/A if NOT REPLICATED, otherwise name of the AG Group the database is part of

select DISTINCT sd.name, 
(
case 
 when
  hdrs.is_primary_replica IS NULL then  'NOT REPLICATED'
 when exists ( select * from sys.dm_hadr_database_replica_states as irs where sd.database_id = irs.database_id and is_primary_replica = 1 ) then
	'PRIMARY'
 else
    'SECONDARY'
 end
) as  AGType,
COALESCE(grp.ag_name,'N/A') as AGName
 from sys.databases as sd
 left outer join sys.dm_hadr_database_replica_states  as hdrs on hdrs.database_id = sd.database_id
 left outer join sys.dm_hadr_name_id_map as grp on grp.ag_id = hdrs.group_id

Synchronization state of the local databases

SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined]
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
ORDER BY AG.name ASC, dbcs.database_name

Notes

Mastodon LLIMastodon SocialFosstodon