SQL Server Always On AG Health Check

Purpose

Provides a health snapshot of all Always On Availability Groups on the current SQL Server instance:

1. AG and replica overall state
2. Database-level synchronisation status and queue sizes
3. Listener configuration
4. Recent failover history (if available)

Supported Platforms and Versions

SQL Server 2016, 2017, 2019, 2022.

Requires Always On Availability Groups to be configured. Script safely returns empty result sets if no AGs exist.

Required Permissions

VIEW SERVER STATE on the SQL Server instance.

Risk Level

Low. Read-only. No changes to AG configuration, replicas, or databases.

Script

-- ============================================================
-- SQL Server Always On AG Health Check
-- Permissions: VIEW SERVER STATE
-- Risk: Read-only
-- ============================================================

-- 1. AG configuration and role
SELECT
    ag.name                              AS ag_name,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ars.role_desc,
    ars.operational_state_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    ars.last_connect_error_number,
    ars.last_connect_error_description,
    ars.last_connect_error_timestamp
FROM sys.availability_groups                     AS ag
JOIN sys.availability_replicas                   AS ar  ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states     AS ars ON ar.replica_id = ars.replica_id
ORDER BY ag.name, ar.replica_server_name;

-- 2. Database synchronisation state and queue sizes
SELECT
    ag.name                              AS ag_name,
    db.name                              AS database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.is_suspended,
    drs.suspend_reason_desc,
    drs.log_send_queue_size,             -- KB unsent to secondary
    drs.log_send_rate,                   -- KB/s
    drs.redo_queue_size,                 -- KB not yet redone on secondary
    drs.redo_rate,                       -- KB/s
    drs.last_sent_time,
    drs.last_received_time,
    drs.last_hardened_time,
    drs.last_redone_time
FROM sys.dm_hadr_database_replica_states         AS drs
JOIN sys.availability_replicas                   AS ar  ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups                     AS ag  ON ar.group_id = ag.group_id
JOIN sys.databases                               AS db  ON drs.database_id = db.database_id
ORDER BY ag.name, db.name, ar.replica_server_name;

-- 3. AG listener configuration
SELECT
    ag.name                              AS ag_name,
    agl.dns_name                         AS listener_name,
    aglip.ip_address,
    aglip.ip_subnet_mask,
    agl.port,
    agl.ip_configuration_string_from_cluster
FROM sys.availability_group_listeners            AS agl
JOIN sys.availability_groups                     AS ag  ON agl.group_id = ag.group_id
JOIN sys.availability_group_listener_ip_addresses AS aglip ON agl.listener_id = aglip.listener_id
ORDER BY ag.name;

-- 4. Replica cluster state (quorum and voting)
SELECT
    member_name,
    member_type_desc,
    member_state_desc,
    number_of_quorum_votes
FROM sys.dm_hadr_cluster_members
ORDER BY member_name;

Interpreting Results

Replica state: All replicas should show SYNCHRONIZED (synchronous mode) or SYNCHRONIZING (asynchronous) with CONNECTED state. Any DISCONNECTED or NOT SYNCHRONIZING state requires immediate investigation.

Queue sizes: log_send_queue_size indicates unsent redo — this should trend toward zero. A growing send queue suggests network saturation or the secondary cannot accept redo fast enough. redo_queue_size indicates lag on the secondary apply side.

Suspended databases: is_suspended = 1 means the replica is no longer receiving or applying log for that database. Identify the suspend reason and investigate before the queue grows beyond recovery.

Rollback Steps

Not applicable — read-only script.