$ guides / microsoft-sql-server / microsoft-sql-server-ag-failover-readiness ▌

Operations Guides

SQL Server AlwaysOn failover readiness: quorum, health checks, and the failover you assume works

An AG that reports “healthy” in the dashboard can still fail to failover when you need it. The synchronization state tells you data is flowing between replicas. It says nothing about whether the cluster can orchestrate a failover, whether the health detection policy will catch the specific failure you are about to have, or whether the cluster has already exhausted its automatic failover budget for the period.

Automatic failover is the intersection of cluster quorum, replica synchronization state, health detection policy, the failure-condition-level setting, and the WSFC failover threshold. Break any one and the AG sits in RESOLVING during the incident you built it to survive.

This article covers the conditions automatic failover actually requires, the cluster and health-check settings that silently disable it, and the periodic validation checks that catch a broken failover path before the incident does.

What automatic failover actually requires

Automatic failover is not triggered by the AG itself. The cluster layer (WSFC on Windows, Pacemaker on Linux) triggers it after SQL Server’s health detection reports a failure condition. For the failover to execute, every one of the following must be true at the moment of failure.

flowchart TD
    A[Primary failure detected] --> B{Cluster quorum present?}
    B -- No --> X[AG goes offline. No failover.]
    B -- Yes --> C{Failover threshold exceeded?}
    C -- Yes --> Y[AG stuck FAILED. Manual reset required.]
    C -- No --> D{Auto-failover target exists?}
    D -- No --> Z[Manual failover only.]
    D -- Yes --> E{Secondary SYNCHRONIZED?}
    E -- No --> W[No auto-failover. Potential data loss.]
    E -- Yes --> F{failure-condition-level met?}
    F -- No --> V[No failover triggered.]
    F -- Yes --> G[Automatic failover executes]

Synchronous-commit on both replicas. The primary and the failover target must both be configured for synchronous-commit (availability_mode = SYNCHRONOUS_COMMIT). Asynchronous-commit replicas are never automatic failover targets.

Automatic failover mode on both replicas. The failover target must have failover_mode = AUTOMATIC. A synchronous replica set to manual failover provides zero-data-loss protection but will not failover without human action.

Secondary in SYNCHRONIZED state. The secondary must have hardened all log records up to the current primary LSN. A secondary in SYNCHRONIZING is catching up and is not a valid automatic failover target.

Cluster quorum present. The cluster must have quorum. If the cluster loses quorum, the AG goes offline on every node regardless of replica health. This is the most commonly overlooked requirement.

Failure-condition-level met. SQL Server’s health detection (via sp_server_diagnostics) must report a condition matching the configured failure-condition-level. The default level does not detect database-level problems.

Within the WSFC failover threshold. The cluster must not have exhausted its automatic failover budget for the period. See the section on the failover threshold below.

Quorum and vote configuration

Quorum is the cluster’s agreement on which node should own the AG resource. Without quorum, the cluster cannot bring the AG online on any node, and no failover of any kind occurs.

Witness requirement for even-numbered clusters. A two-node cluster without a witness cannot sustain a node crash. If either node goes down unexpectedly, quorum is lost and the AG goes offline on both nodes. Any even-numbered cluster requires a witness, either a file share witness or a cloud witness, to break the tie. Cloud witness (available on Windows Server 2016 and later) is the recommended option for Azure VMs and multi-site deployments.

Dynamic quorum is not a substitute for a witness. Dynamic quorum and dynamic witness adjust vote weights as nodes leave and join the cluster. They protect against sequential graceful node shutdowns. They do not protect against simultaneous failures. If two of three nodes crash at once, the remaining node plus witness hold half the original vote count, and quorum is lost.

Vote configuration matters in multi-site deployments. The primary replica node and at least one automatic failover target should hold a quorum vote. The Always On Availability Group Wizard warns when a node that could become primary lacks a vote. In single-site deployments this warning can be ignored. In multi-site deployments it is critical.

Check the current quorum configuration:

# WSFC: cluster quorum and node votes
Get-ClusterQuorum
Get-ClusterNode | Select-Object Name, State, NodeWeight

# Pacemaker: cluster status and quorum
pcs status
corosync-quorumtool

On Linux with Pacemaker, quorum is managed by corosync. The concepts (vote count, witness requirement for even-numbered clusters) apply, but the tooling differs entirely.

Health check and timeout settings

SQL Server reports its health to the cluster via sp_server_diagnostics. The cluster uses this input, along with the lease mechanism, to decide when the primary is unhealthy and a failover should be triggered. Three settings control this behavior.

Failure-condition-level (default: 3, OnCriticalServerError). Controls which conditions trigger an automatic failover. Level 3 triggers on critical server errors such as serious internal errors. It does not trigger on database-level problems. A database going SUSPECT, a transaction log filling up, or a data file going missing will not trigger an automatic failover at the default level. Levels range from 1 (least sensitive, server down only) through 5 (most sensitive, includes any qualifying error condition).

Health-check-timeout (default: 30000 ms). How long the cluster waits for sp_server_diagnostics to respond before considering the primary unhealthy. The sp_server_diagnostics polling interval is one-third of this value, which is 10 seconds at the default. The documented minimum is 15,000 ms (15 seconds), which sets the polling interval to 5 seconds.

Lease timeout (default: 20000 ms). A separate mechanism where the SQL Server resource DLL and the SQL Server process exchange lease renewals. If either side stops responding, the lease expires and the cluster considers the resource failed. The lease timeout must be less than 2 * SameSubnetThreshold * SameSubnetDelay, the maximum-lease equation Microsoft documents, to prevent split-brain: if the subnet detection window expires before the lease, the cluster could bring the resource online on a second node while the original primary still holds the lease. At the default 20000 ms timeout, leases renew every 10 seconds (half the timeout). Lease timeouts can be triggered by resource pressure such as high CPU, low memory, or disk latency, not just SQL Server process crashes. Microsoft’s troubleshooting documentation confirms the default lease timeout is 20 seconds across SQL Server 2016-2022.

Check the current AG-level health detection settings:

SELECT
    name AS ag_name,
    failure_condition_level,
    health_check_timeout,
    db_failover
FROM sys.availability_groups;

Database-level health detection (DB_FAILOVER)

The default failure-condition-level operates at the server instance level. At the default level, sp_server_diagnostics reports server health but does not trigger failover for individual database failures. This is by design.

The consequence is significant. If a single database in the AG goes offline due to corruption, a full transaction log (error 9002), a deleted data file, or any other database-specific failure, the AG will not automatically failover. The primary instance is still healthy from the cluster’s perspective, so the health detection never fires.

DB_FAILOVER is a separate AG-level option that enables database-level health detection. When enabled, the AG treats any database transitioning to an unhealthy state as a failover condition.

DB_FAILOVER is OFF by default. Enable it per AG:

-- Warning: changes failover behavior. A single database going offline
-- will trigger an AG-level failover after this change.
ALTER AVAILABILITY GROUP [YourAG] SET (DB_FAILOVER = ON);

For production AGs where a single database failure should trigger failover, this should be ON. Evaluate the tradeoff before enabling it. If your AG carries databases with different criticality levels, DB_FAILOVER will failover the entire AG when any one database has a problem, which may not be what you want for a mixed-workload AG.

The WSFC failover threshold trap

WSFC tracks automatic failovers per resource over a rolling time window. The default policy is “Maximum Failures in Specified Period,” which allows N-1 failures, where N is the number of cluster nodes, within a 6-hour window.

Failover Cluster Manager shows these same defaults on the Failover tab of an availability group resource’s properties: “Maximum failures in the specified period” is n-1 and “Period” is 6 hours. Microsoft documents these as the default WSFC failure-threshold values and exposes them for change in the Failover Manager Console.

Once this threshold is exceeded, the AG resource enters a FAILED state and the cluster stops attempting automatic failovers entirely. No further automatic failover will occur until a human intervenes. The AG stays in RESOLVING or FAILED.

This is a silent failover blocker that operators frequently misdiagnose. The AG looks broken, the replicas look healthy, but the cluster has given up on automatic failover for this resource. Check the cluster log:

# Generate cluster log and search for failover threshold events
Get-ClusterLog -Node <NodeName> -TimeSpan 60
# Then search the cluster.log for: "failoverCount", "IsAlive", "MaximumFailures"

The immediate fix is to bring the AG resource back online manually, either in Failover Cluster Manager or via PowerShell, which resets the failover count. But the real fix is to understand why the AG failed over repeatedly in the first place. A flapping AG that keeps failing over has an underlying problem, and the threshold is masking it.

Periodic failover readiness validation

Failover readiness decays silently. Cluster configuration drifts, nodes lose votes, certificates expire, and the failover path you validated at deployment may not exist six months later. Run the following checks on a regular schedule.

Replica configuration and health state:

SELECT
    ag.name AS ag_name,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ag.failure_condition_level,
    ag.health_check_timeout,
    ag.db_failover,
    ars.role_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
ORDER BY ag.name, ar.replica_server_name;

Secondary synchronization and queue depth:

SELECT
    DB_NAME(drs.database_id) AS database_name,
    ar.replica_server_name,
    drs.is_local,
    drs.synchronization_state_desc,
    drs.redo_queue_size,
    drs.log_send_queue_size
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
ORDER BY DB_NAME(drs.database_id), drs.is_local DESC;

NT AUTHORITY\SYSTEM permissions on each replica (required for health detection):

SELECT permission_name, state_desc
FROM sys.server_permissions sp
JOIN sys.server_principals p ON sp.grantee_principal_id = p.principal_id
WHERE p.name = 'NT AUTHORITY\SYSTEM'
AND sp.permission_name IN ('CONNECT SQL', 'VIEW SERVER STATE', 'ALTER ANY AVAILABILITY GROUP');

Certificate expiry on AG endpoints:

SELECT name, start_date, expiry_date
FROM sys.certificates
WHERE expiry_date < DATEADD(day, 90, GETUTCDATE());

Additional checks to perform on schedule:

Quorum state. Verify the cluster has quorum and the witness is online. A witness that has been offline for weeks is a silent failover blocker that AG-level monitoring will never surface.

AG endpoint connectivity. Verify that the database mirroring endpoints on all replicas can connect to each other. Certificate expiry on endpoint authentication causes silent disconnection between replicas.

Failover threshold state. Confirm the AG resource is not in a state where the cluster has stopped attempting automatic failovers due to threshold exhaustion.

Periodic failover testing. The only definitive way to validate that automatic failover works is to perform a planned failover during a maintenance window. Fail over to each automatic target, confirm the application reconnects through the listener, verify data integrity, and fail back. If you have never performed a failover on this AG in production, you do not actually know if it works.

Signals to monitor for failover readiness

Signal	Why it matters	Warning sign
`synchronization_health_desc`	Whether the secondary is a valid failover target	Not HEALTHY on a synchronous replica
`connected_state_desc`	Secondary must be connected to primary to be a failover target	DISCONNECTED on a synchronous replica
Redo queue size	Determines RTO on failover (new primary applies pending log)	Growing queue or estimated catch-up exceeds RTO target
Send queue size	Primary accumulating un-replicated log means data loss on failover	Growing queue on a synchronous replica
WSFC cluster quorum state	Without quorum, no failover of any kind occurs	Quorum loss or witness offline
NT AUTHORITY\SYSTEM permissions	Required for health detection on every potential primary	Missing any of the three required permissions on any replica
Certificate expiry on AG endpoints	Expired certificates disconnect replicas silently	Expiry within 90 days
`HADR_SYNC_COMMIT` wait time	Latency cost of synchronous replication on primary commits	Sudden increase indicates secondary or network degradation

How Netdata helps

AG replica state as a continuous time series. Per-second sampling of synchronization health and connection state catches the transition from HEALTHY to PARTIALLY_HEALTHY before the incident, not after.
Redo queue correlated with secondary resource pressure. A growing redo queue paired with elevated CPU or I/O stall on the secondary tells you why the secondary cannot keep up, not just that it is falling behind.
HADR_SYNC_COMMIT waits alongside secondary I/O metrics. Correlating synchronous commit latency with secondary log write latency and network throughput separates network problems from secondary storage problems.
Cluster quorum alongside AG health. Monitoring both layers catches quorum loss, the most common silent failover blocker, that AG-only monitoring misses entirely.
Per-second granularity during failover events. When a failover occurs, per-second metrics show when the primary stopped responding, when the secondary took over, and how long the application was without a writable endpoint.

The Netdata SQL Server collector provides per-second visibility into these signals.

The Netdata solution

Microsoft SQL Server monitoring with Netdata

Netdata monitors SQL Server with per-second metrics, pre-built dashboards, and ML-powered anomaly detection. Correlate wait statistics, blocking chains, transaction-log and TempDB pressure, Page Life Expectancy, memory grants, per-file I/O stalls, and AlwaysOn send/redo queues against the rest of your stack so you catch the incidents in these runbooks before they page anyone.

See SQL Server monitoring → Start monitoring free