Script: Check AG Replica Role and Synchronization State

This script returns the current role, synchronization state, health status, and failover configuration for replicas participating in an Always On Availability Group.

It is commonly used during failover validation, replica troubleshooting, and general high availability health checks.

---

The Script

---

Example Output

The result set shows one row per replica in each Availability Group.

Important columns include:

• role_desc – Indicates whether the replica is PRIMARY or SECONDARY
• synchronization_health_desc – HEALTHY, PARTIALLY_HEALTHY, or NOT_HEALTHY
• connected_state_desc – Shows whether the replica is currently connected
• availability_mode_desc – SYNCHRONOUS_COMMIT or ASYNCHRONOUS_COMMIT
• failover_mode_desc – AUTOMATIC or MANUAL

Together, these columns provide a quick operational view of AG configuration and health status.

---

Notes

• Only returns data if Availability Groups are enabled
• Useful before and after planned failovers
• Check synchronization_health_desc before initiating manual failover
• Combine with latency script for deeper replication visibility