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