"Always On Availability Groups: Essential DBA Scripts for Monitoring and Checking SQL Server High Availability"

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;