DBA Scripts: Check AG Replica Role and Synchronization State

Is Every Replica Actually Ready to Take Over

A failover just happened or the AG dashboard has gone red, and you need to know which replica is primary now and whether every secondary is connected and in sync. Run this on the primary: it reads each replica’s role, connection state, synchronization health and last connection error straight from the AG’s own DMVs.

Why Availability Group Replica State Matters

• connected_state_desc and synchronization_health_desc are the two fastest signals for whether a secondary is actually protecting you, not just configured
• A replica can stay listed in the AG topology while silently disconnected, last_connect_error_number and last_connect_error_timestamp are what expose that
• Availability mode (synchronous vs asynchronous commit) changes what “healthy” should even look like, a synchronous replica that’s behind is a much bigger problem than an asynchronous one running a little behind
• This is exactly the kind of check that’s easy to assume is fine because nothing has alerted, until the day a failover is needed and a replica turns out not to have been ready

When to Run This Script

• Routine SQL Server health checks on any instance participating in an Availability Group
• Immediately after any AG failover, planned or unplanned, to confirm every replica came back healthy
• After a network change, patch, or maintenance window touching any replica
• Before relying on a secondary for a planned failover or maintenance activity

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-AvailabilityGroupReplicaState
Category    : high-availability
Purpose     : Show AG replica health, connection state, and synchronization status for failover readiness.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-check-ag-replica-role-and-synchronization-state/)
Requires    : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

IF SERVERPROPERTY('IsHadrEnabled') = 0
    OR NOT EXISTS (SELECT 1 FROM sys.availability_groups)
BEGIN
    SELECT 'Always On Availability Groups is not enabled or no groups are configured on this instance.' AS status;
END
ELSE
BEGIN
    SELECT
        ag.name                          AS ag_name,
        ar.replica_server_name,
        ar.availability_mode_desc        AS commit_mode,
        ar.failover_mode_desc,
        ars.role_desc,
        ars.operational_state_desc,
        ars.connected_state_desc,
        ars.synchronization_health_desc,
        ars.recovery_health_desc,
        ars.last_connect_error_number,
        ars.last_connect_error_description,
        ars.last_connect_error_timestamp
    FROM sys.availability_replicas                      AS ar
    JOIN sys.availability_groups                        AS ag  ON ag.group_id   = ar.group_id
    JOIN sys.dm_hadr_availability_replica_states        AS ars ON ars.replica_id = ar.replica_id
    ORDER BY ag.name, ar.replica_server_name;
END

Run it on the primary. On a secondary, sys.dm_hadr_availability_replica_states returns only the local replica, so every other replica drops out of the result. Where Always On is not enabled, or no availability group exists, the guard at the top returns one status row instead of an error, so it is safe to run across a mixed estate.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check every AG replica's connection and sync health:
.\run.ps1 Get-AvailabilityGroupReplicaState

# To run against a remote sql server:
.\run.ps1 Get-AvailabilityGroupReplicaState -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/high-availability/always-on/Get-AvailabilityGroupReplicaState.sql
• powershell/wrappers/high-availability/Get-AvailabilityGroupReplicaState.ps1

Example Output

With no availability group to report, the whole result is one status row:

If you get this row on a server that should host a replica, check SERVERPROPERTY('IsHadrEnabled'). 0 means the Always On feature is off there. 1 means the feature is on but the login that ran the script can see no availability group, which is either no AG or a permissions gap (see the FAQ).

Understanding the Results

Best Practices

• Treat any DISCONNECTED or unhealthy synchronous replica as urgent: once the session timeout passes, the primary stops waiting for it, so a failover to it is no longer zero data loss
• Check this immediately after every failover, planned or unplanned, don’t assume the old primary rejoined cleanly as a secondary
• Pair with Availability Group Latency for the database-level detail behind any replica showing sync issues here

MS Docs: sys.dm_hadr_availability_replica_states · sys.dm_hadr_database_replica_states · sys.availability_groups · sys.availability_replicas · Availability modes

Related Scripts

You may also find these scripts useful:

• High Availability (hub)
• Availability Group Latency
• Log Reuse Waits
• Replication Status
• Transaction Log Size and Usage
• AG Failover Readiness and Readable Secondary Usage

Frequently Asked Questions

Summary

An Availability Group protects you only as far as its least healthy replica. Run this on the primary after every failover and in routine checks, and act on any row that is not CONNECTED and HEALTHY before a failover has to depend on it.