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
- The first query is a per-database view of the whole instance. Databases that are not in an availability group appear with
NOT REPLICATED. - The second query covers only the replicas of the local instance:
is_local = 1. Columns that have no value are replaced by a default in theISNULLcalls; the default forLocalReplicaRoleis 3.