Part of the DBA-Tools Project.
High availability isn’t one technology, SQL Server ships several: Always On Availability Groups (the current, supported approach), Database Mirroring (deprecated since 2012 but still running in plenty of environments), Failover Cluster Instances, and Replication (which trades availability guarantees for data distribution instead). Whichever one a given server uses, the same underlying questions apply: is it healthy right now, would a failover actually succeed, and would you know if it silently stopped keeping up?
This post is the map. Eleven scripts across four HA technologies, grouped by what they check, with a note on which technology each one applies to since running the wrong script against the wrong feature just returns an honest “not configured” result rather than a useful answer.
Why High Availability Matters
- “Configured” and “actually able to fail over successfully right now” are different claims, a replica or mirror can look healthy while carrying a data-loss or recovery-time cost nobody’s quantified
- Each HA technology fails differently: an AG replica falls behind on redo, a mirror endpoint stops communicating, an FCI node blips and restarts, replication agents silently build a backlog, none of these look like a hard outage until they’re tested
- Legacy technology (Mirroring, and Replication used purely for HA) doesn’t get retired just because it’s deprecated, it needs the same monitoring discipline as anything current
- A script returning “not configured” honestly is a genuinely useful result on a server that doesn’t use that feature, distinct from a script returning nothing because something is broken
The Scripts, Grouped by Technology
Always On Availability Groups
- AG Replica Role and Synchronization State, current health and sync state per replica
- Always On Availability Group Latency, send and redo queue latency per replica
- AG Failover Readiness and Readable Secondary Usage, quantified RPO/RTO per database, plus whether a secondary is actually reachable for reads
Database Mirroring (deprecated, still seen in production)
- Mirroring Endpoint Health and Status, endpoint communication state, then mirroring state and queue latency per database
Failover Cluster Instances
- Last Node Blip, when an FCI last moved nodes and why
Replication
- Replication Status, publications, subscriptions, and subscription status
- Replication Agent Status, Log Reader and Distribution Agent activity, plus the undistributed command backlog
How They Fit Together
A sensible order for a routine HA review, once you know which technology a given server actually uses:
- Confirm the technology and current state first: AG Replica Role and Synchronization State, Mirroring Endpoint Health and Status, or Replication Status, whichever applies
- Check latency and lag next: Always On Availability Group Latency for AG, the queue columns in Mirroring Status for mirroring, Replication Agent Status for replication
- Quantify the real cost of a failover with AG Failover Readiness, RPO and RTO in real numbers, not an assumption from a green health indicator
- Check secondary/subscriber usage isn’t being wasted, Readable Secondary Usage for AG, subscriber status for replication
- For FCIs, Last Node Blip stands alone, run it any time uptime looks shorter than expected
Best Practices Across the Series
- Know which HA technology (if any) a given server actually uses before running these scripts, a “not configured” result is the correct, honest answer on a server that doesn’t use that feature
- Don’t equate “healthy” with “failover-ready,” quantify RPO and RTO explicitly rather than trusting a sync-state label alone
- Give deprecated technology (Mirroring, HA-only Replication) the same monitoring discipline as current technology, it’s still what’s actually running until a migration completes
- Treat a climbing backlog or queue (undistributed commands, redo queue, log send queue) as an active, worsening problem the moment it’s found trending upward, not just when it crosses an alert threshold
See Also
This pillar is one of ten in DBA Scripts: The Complete Guide, the map across the whole series organized by the question you’re actually asking.
Summary
High availability on SQL Server isn’t a single check, it’s four different technologies with four different failure modes, and “configured” doesn’t mean “ready.” These eleven scripts cover all four: Always On Availability Groups, Database Mirroring, Failover Cluster Instances, and Replication, grouped by what they check rather than treated as one undifferentiated pile.
Start by confirming which technology a given server actually uses, then work through health, latency, and quantified failover cost in that order, whichever HA feature you’re running.
Leave a Reply