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, Log Shipping, 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. The scripts are grouped by HA technology, with a note on which one each 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
- The Server Network Address Cannot Be Reached (Error 1418), the error a mirror or AG setup stops on, and the order to check the endpoint, port, CONNECT grant, name resolution and firewall
🛠️ 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
🚚 Log Shipping
- Log Shipping Status, backup, copy and restore ages side by side on the monitor server, and which of the 3 jobs stopped when a 14420 or 14421 alert fires
- Writes Fail Because the Database Is Read-Only (Error 3906), the write error a STANDBY log-shipping secondary returns, and what to confirm before switching it to READ_WRITE
How They Fit Together
A sensible order for a routine HA review, once you know which technology a given server actually uses:
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
Frequently Asked 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 Issues, in the Database Mirroring card above, 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.
Summary
High availability on SQL Server isn’t a single check, it’s several technologies, each with its own failure modes, and “configured” doesn’t mean “ready.” These scripts cover all of them: Always On Availability Groups, Database Mirroring, Failover Cluster Instances, Log Shipping, 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.
Related
- Backups and Recovery
- Migration and Deployment
- SQL Server Migration Runbook: Availability Groups and Failover Clusters
- SQL Server Login Migration: What Gets Silently Left Behind
- SQL Server Recovery Models Explained
- HADR_SYNC_COMMIT Wait Type in SQL Server
- Why Is the Database in “In Recovery” Mode?, stuck In Recovery after a failover
- PREEMPTIVE_HADR_LEASE_MECHANISM Wait Type in SQL Server, an unexpected failover
- HADR_DATABASE_FLOW_CONTROL Wait Type in SQL Server
- DBA Scripts: The Complete Guide
Leave a Reply