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
Start Here
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
- Understanding AG Latency, what the send and redo numbers actually mean before you act on them
- Creating SQL Logins on an AG, keeping logins and their SIDs consistent across replicas so a failover does not lock users out
🔗 Database Mirroring (deprecated, still seen in production)
- Mirroring Endpoint Health and Status, endpoint communication state, then mirroring state and queue latency per database
- Troubleshooting Database Mirroring Issues, the failure modes you actually meet, and the order to work through them
🛠️ Failover Cluster Instances
- Last Node Blip, when an FCI last moved nodes and why
- Get Last SQL Failover Time with PowerShell, the same question asked across a fleet from one prompt
📡 Replication
- Replication Status, publications, subscriptions, and subscription status
- Replication Agent Status, Log Reader and Distribution Agent activity, plus the undistributed command backlog
- Replication Monitoring Scripts, the wider set of replication checks collected in one place
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
Common Questions
An availability group shows healthy. Why check anything else?
Healthy means the replicas are communicating and synchronising. It does not tell you how much data you would lose on a failover, how long recovery would take, or whether the secondary has quietly fallen behind on redo. Those are separate measurements.
We still run Database Mirroring. Is it worth monitoring?
Yes. Deprecated does not mean retired, and mirroring in production needs the same discipline as anything current: endpoint health, send and redo queues, and witness state. Plenty of estates still depend on it. Troubleshooting Database Mirroring, in Related Scripts below, covers that in full.
Why does a script return “not configured” on my server?
Because that feature is not in use, and that is a genuinely useful answer. It is different from a script returning nothing because something is broken. Running an AG script on a mirrored instance should honestly report that there is no AG.
What is redo queue and why does it matter more than send queue?
Send queue is data waiting to leave the primary; redo queue is data that has arrived at the secondary but not yet been applied. Redo queue drives your recovery time on failover, so a large redo queue means a slow failover even when everything looks synchronised.
Does a readable secondary affect the primary?
It can. Read workloads on a secondary take schema stability locks that can hold up redo, which increases the redo queue and therefore your failover time. It is a trade, not a free read replica.
See Also
This pillar is part of 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