Check Availability Group Health:
SELECT
ag.name AS
[Availability Group],
ar.replica_server_name AS [Replica Server],
adc.database_name
AS [Database],
ags.is_local AS
[Is Local],
ags.is_primary_replica AS [Is Primary]
FROM
sys.availability_groups AS ag
INNER JOIN
sys.dm_hadr_availability_replica_states AS ars
ON ag.group_id
= ars.group_id
INNER JOIN
sys.dm_hadr_availability_replica_cluster_states AS arcs
ON
ars.group_id = arcs.group_id
AND
ars.replica_id = arcs.replica_id
INNER JOIN
sys.dm_hadr_database_replica_cluster_states AS adc
ON
arcs.group_id = adc.group_id
AND
arcs.replica_id = adc.replica_id
INNER JOIN
sys.availability_group_listeners AS agl
ON ag.group_id
= agl.group_id
INNER JOIN
sys.dm_hadr_availability_replica_states AS ags
ON
ags.replica_id = ars.replica_id
WHERE
ars.is_local = 1;
This script provides an overview of the availability group
health, including the availability group name, replica server name, database
name, and whether the replica is the primary replica or not.
Check Data Synchronization:
SELECT
ag.name AS
[Availability Group],
d.name AS
[Database],
drs.synchronization_state_desc AS [Synchronization State],
drs.synchronization_health_desc AS [Synchronization Health]
FROM
sys.availability_groups AS ag
INNER JOIN sys.dm_hadr_database_replica_states
AS drs
ON ag.group_id
= drs.group_id
INNER JOIN
sys.databases AS d
ON
drs.database_id = d.database_id;
This script shows the synchronization state and health of
the databases within the availability group. It provides information on whether
the data is synchronized or if there are any synchronization issues.
Check Availability Group Listener:
SELECT
ag.name AS
[Availability Group],
agl.dns_name AS
[Listener DNS Name],
agl.port AS
[Listener Port],
agl.state_desc AS
[Listener State]
FROM
sys.availability_groups AS ag
INNER JOIN
sys.availability_group_listeners AS agl
ON ag.group_id
= agl.group_id;
This script displays the availability group name, listener
DNS name, port, and the state of the availability group listener.
Check Failover Readiness:
SELECT
ag.name AS
[Availability Group],
drs.database_id AS
[Database ID],
d.name AS
[Database],
drs.is_failover_ready
AS [Is Failover Ready]
FROM
sys.availability_groups AS ag
INNER JOIN
sys.dm_hadr_database_replica_states AS drs
ON ag.group_id
= drs.group_id
INNER JOIN
sys.databases AS d
ON
drs.database_id = d.database_id;
