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'
SQLZealots 