How to identify available AGs to the server as Primary replica?
SELECT Groups.[Name] AS [AGname], [Primary_recovery_health_desc],[synchronization_health_desc], automated_backup_preference_desc,dtc_support,db_failover,is_distributed, count(AGDatabases.database_name) [# Databases] FROM sys.dm_hadr_availability_group_states States INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id INNER JOIN sys.availability_databases_cluster AGDatabases ON Groups.group_id = AGDatabases.group_id WHERE primary_replica = @@Servername Group by Groups.[Name] , [Primary_recovery_health_desc],[synchronization_health_desc], automated_backup_preference_desc,dtc_support,db_failover,is_distributed
How to identify available AGs to the server as Secondary replica?
SELECT Groups.[Name] AS [AGname], [secondary_recovery_health_desc],[synchronization_health_desc], automated_backup_preference_desc,dtc_support,db_failover,is_distributed FROM sys.dm_hadr_availability_group_states States INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id WHERE primary_replica != @@Servername
How to identify AG – Databases distribution of a Primary replica?
SELECT Groups.[Name] AS AGname, AGD.database_name AS Databasename FROM sys.dm_hadr_availability_group_states States INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id INNER JOIN sys.availability_databases_cluster AGD ON Groups.group_id = AGD.group_id WHERE primary_replica = @@Servername ORDER BY AGname ASC, Databasename ASC;
How to identify AG – Databases distribution of a Secondary replica?
SELECT Groups.[Name] AS AGname, AGD.database_name AS Databasename FROM sys.dm_hadr_availability_group_states States INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id INNER JOIN sys.availability_databases_cluster AGD ON Groups.group_id = AGD.group_id WHERE primary_replica != @@Servername ORDER BY AGname ASC, Databasename ASC;
A quick health check of your AG
SELECT DISTINCT Groups.name AS AGname, AGReplicas.replica_server_name, AGStates.role_desc, AGStates.synchronization_health_desc, ISNULL(AGReplicaStates.suspend_reason_desc,'N/A') AS suspend_reason_desc FROM sys.availability_groups Groups INNER JOIN sys.dm_hadr_availability_replica_states as AGStates ON AGStates.group_id = Groups.group_id INNER JOIN sys.availability_replicas as AGReplicas ON AGStates.replica_id = AGReplicas.replica_id INNER JOIN sys.dm_hadr_database_replica_states as AGReplicaStates ON AGReplicas.replica_id = AGReplicaStates.replica_id Order by Groups.name
A quick health check of your AG – DAG
SELECT ag.[name] AS [AG Name], ag.is_distributed, ar.replica_server_name AS [Underlying AG], ars.role_desc AS [Role], ars.synchronization_health_desc AS [Sync Status] FROM sys.availability_groups AS ag INNER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id INNER JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id
A quick health check of your AG – DB
select DB_NAME(database_id),* from sys.dm_hadr_database_replica_states where synchronization_health_desc != 'HEALTHY'In addition, SSMS provides a good dashboard to understand the Always On Availability Group and health status as below. I would highly recommend to refer for more details. I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!