SQL Server - Availability Group - AG Role Check by Database
--DB별 AG RoleCheck
SELECT DBS.database_id
, DBS.name
, isAGDB = CASE WHEN AG.group_id is null THEN N'N' ELSE N'Y' END
, replicaRole = CASE WHEN AG.group_id is NOT null AND AGS.primary_replica = @@SERVERNAME THEN N'Primary'
WHEN AG.group_id is NOT null AND AGS.primary_replica <> @@SERVERNAME THEN N'Secondary'
END
, AGGroupID = AG.group_id
, AGName = AG.name
, PrimaryReplica = AGS.primary_replica
, PrimaryRecoveryHealth_desc = AGS.primary_recovery_health_desc
, SynchronizationHealth_desc = AGS.synchronization_health_desc
FROM sys.databases AS DBS
LEFT JOIN sys.availability_databases_cluster AS ADC
ON DBS.name = ADC.database_name
LEFT JOIN sys.availability_groups AS AG
ON AG.group_id = ADC.group_id
LEFT JOIN sys.dm_hadr_availability_group_states AS AGS
ON AG.group_id = AGS.group_id
WHERE DBS.state = 0 -- 상태가 Online만
AND DBS.is_in_standby = 0 -- 로그전달 Standby 제외
AND DBS.is_read_only = 0 -- 로그전달 Read Only 제외
AND DBS.name not in ('tempdb')
/* 아래 주석 풀면 Non AG 조건 or AG인 경우 Primary Replica만 체크함 */
-- AND ( ( AGS.primary_replica IS NOT NULL
-- AND AGS.primary_replica = @@SERVERNAME -- AG인 경우는 스스로 Primary Replica인 경우만
-- )
-- OR ( ADC.database_name IS NULL )
--)
ORDER BY
DBS.database_id
--TWCha / Nexon GameDB Team