SQL Server Availability Group not synchronizing: NOT_HEALTHY replicas and failover risk

The symptom arrives as an alert or a dashboard color change: a synchronous-commit secondary replica is reporting synchronization_health_desc = NOT_HEALTHY or connected_state_desc = DISCONNECTED in sys.dm_hadr_availability_replica_states. The primary is still accepting writes, but the protection you assumed is degraded or gone.

In synchronous-commit mode, the primary waits for the secondary to harden log records before acknowledging commits. When the secondary drops or stops keeping up, the primary either continues unprotected or stops accepting writes entirely, depending on required_synchronized_secondaries_to_commit. Either way, your recovery point objective and your recovery time objective are both at risk.

This guide walks through reading the AG state DMVs correctly, separating real failures from transitional ones, and identifying the root cause. The state model has multiple layers (replica connectivity, synchronization health, database-level sync state, cluster quorum) and a NOT_HEALTHY result at the top layer can come from any of them.

What this means

The synchronization_health_desc column in sys.dm_hadr_availability_replica_states is a roll-up of the health of every database joined to that replica:

• HEALTHY: All joined databases are at their target sync state.
• PARTIALLY_HEALTHY: Some joined databases are not at their target state.
• NOT_HEALTHY: No joined databases are synchronized.

connected_state_desc is a separate column on the same DMV, with values CONNECTED and DISCONNECTED. You can be CONNECTED with synchronization_health_desc = NOT_HEALTHY (replica reachable but no databases syncing), or DISCONNECTED (replica unreachable and failover not possible without forcing quorum). Read both columns; they describe independent failures.

The failover risk depends on commit mode and configuration. The condition worth paging on is:

• A SYNCHRONOUS_COMMIT replica in DISCONNECTED or NOT_HEALTHY for a sustained period beyond the failover-transition window (roughly 120 seconds), AND
• No other healthy synchronous failover target exists.

When required_synchronized_secondaries_to_commit > 0, the primary blocks commits until that many sync secondaries acknowledge them. A disconnected sync secondary with this setting means the primary stops accepting new writes. Gate pages on sustained duration and the absence of alternatives to avoid false alerts during planned failovers, restarts, and AG resume operations.

stateDiagram-v2
    [*] --> DISCONNECTED: restart
    DISCONNECTED --> CONNECTED: endpoint reachable
    CONNECTED --> PARTIALLY_HEALTHY: some DBs sync
    PARTIALLY_HEALTHY --> HEALTHY: all DBs caught up
    HEALTHY --> PARTIALLY_HEALTHY: workload burst / redo lag
    PARTIALLY_HEALTHY --> NOT_HEALTHY: all DBs fall behind
    NOT_HEALTHY --> DISCONNECTED: session timeout

Note: DISCONNECTED in the diagram reflects connected_state_desc; the other states reflect synchronization_health_desc. They come from different columns on the same row.

Common causes

Quick checks

-- Replica state and synchronization health
SELECT
    ag.name AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.operational_state_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    ars.last_connect_error_description
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;

-- Database-level sync state (the layer beneath replica health)
SELECT
    ag.name AS ag_name,
    ar.replica_server_name,
    DB_NAME(drs.database_id) AS database_name,
    drs.synchronization_state_desc,
    drs.log_send_queue_size AS send_queue_kb,
    drs.log_send_rate AS send_rate_kbps,
    drs.redo_queue_size AS redo_queue_kb,
    drs.redo_rate AS redo_rate_kbps,
    drs.suspend_reason_desc
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name;

-- HADR endpoint state on this instance
SELECT name, endpoint_id, type_desc, state_desc, port
FROM sys.tcp_endpoints
WHERE type_desc = 'DATABASE_MIRRORING';

-- Endpoint permissions (for certificate-based auth)
SELECT ep.name, sp.name AS grantee, sp.type_desc
FROM sys.tcp_endpoints ep
JOIN sys.server_permissions perm ON ep.endpoint_id = perm.major_id
JOIN sys.server_principals sp ON perm.grantee_principal_id = sp.principal_id
WHERE ep.type_desc = 'DATABASE_MIRRORING';

-- Certificate expiry relevant to AG endpoint auth
SELECT name, subject, expiry_date,
       DATEDIFF(DAY, GETDATE(), expiry_date) AS days_until_expiry
FROM sys.certificates
WHERE expiry_date < DATEADD(MONTH, 3, GETDATE())
ORDER BY expiry_date;

-- required_synchronized_secondaries_to_commit setting
SELECT name, required_synchronized_secondaries_to_commit
FROM sys.availability_groups;

How to diagnose it

1. Confirm the failure is sustained, not transitional. Replica states flap during failover, restart, and AG resume. Re-query sys.dm_hadr_availability_replica_states 30 and 120 seconds after the first alert. If the state has returned to HEALTHY, log the event and move on. The 120-second gate matters: SQL Server’s session timeout default is 10 seconds, and recovery from a brief blip can take a minute.

2. Determine whether this is a connectivity problem or a sync problem. If connected_state_desc = DISCONNECTED, the replicas cannot talk. If connected_state_desc = CONNECTED but synchronization_health_desc = NOT_HEALTHY, the replicas are talking but databases are not syncing. These have different root causes and need different fixes.

3. Drill into the database layer. The replica’s health rolls up from sys.dm_hadr_database_replica_states. Look for synchronization_state_desc values other than SYNCHRONIZED (synchronous) or SYNCHRONIZING (asynchronous). NOT_SYNCHRONIZING is the failure state. SUSPENDED means data movement was explicitly suspended. The last_connect_error_description on the replica row and suspend_reason_desc on the database row both carry useful detail.

4. Check the secondary directly. Many NOT_HEALTHY causes live on the secondary, not the primary. Connect to the secondary replica and check:

   • The secondary’s error log around the time of failure
   • I/O stall on the secondary’s log and data files via sys.dm_io_virtual_file_stats
   • Secondary CPU and worker thread utilization
   • Whether the redo queue is growing faster than the redo rate can drain it

5. Verify the cluster layer. On Windows, check the WSFC cluster state and quorum with Failover Cluster Manager or PowerShell. On Linux, check Pacemaker with pcs status or crm_mon. The cluster and SQL Server can disagree about health; both layers must be healthy for failover to work.

6. Check the primary’s wait stats. If synchronous commit is configured, HADR_SYNC_COMMIT waits on the primary directly measure the replication latency penalty. A spike preceding the NOT_HEALTHY transition points to a secondary performance problem rather than a connectivity problem.

7. Look at the endpoint certificate. If you use certificate-based endpoint authentication (common in workgroup, cross-domain, or distributed AG setups), an expired certificate produces a DISCONNECTED state with cryptic error log messages. Check both replicas’ certificate expiry.

Metrics and signals to monitor

Fixes

Inter-replica network fault

Confirm DNS resolution, firewall rules, and port reachability for the HADR endpoint port on both replicas. The endpoint port comes from sys.tcp_endpoints. A Test-NetConnection -ComputerName <replica> -Port <port> on Windows or nc -zv <replica> <port> on Linux, run from both directions, is the fastest connectivity check.

If you find MTU or path-MTU issues causing fragmentation, the symptom is usually intermittent disconnections under load rather than a hard failure. Test with don’t-fragment-flagged pings to confirm.

Secondary I/O or CPU overload

If the redo queue is growing and the secondary’s log write latency is high, the secondary storage cannot keep up. Options, in order of preference:

• Move secondary log files to faster storage.
• Reduce concurrent readable-secondary query load that competes for I/O and CPU.
• Investigate readable-secondary queries holding schema stability locks that block redo (look for HADR_DATABASE_FLOW_CONTROL).
• Temporarily switch the replica to asynchronous commit as emergency relief. This accepts data-loss risk on failover and should only be used to unblock the primary.

Expired endpoint certificate

Renew the certificate on both sides following the standard Microsoft procedure. A documented real-world workaround when both CERTIFICATE and NEGOTIATE authentication methods are configured and the certificate has expired: alter the endpoint to use NEGOTIATE only and restart the endpoint to restore connectivity, then renew the certificate at leisure. Whether NEGOTIATE-only is appropriate depends on your domain and workload requirements.

Cluster quorum loss

On Windows, use Failover Cluster Manager or Get-ClusterQuorum to inspect the witness configuration. On Linux, pcs status shows quorum and node state. Add or restore a file-share, cloud, or disk witness to restore quorum. Without quorum, the AG cannot fail over and all replicas may report unhealthy.

SQL Server 2022 CU6 regression

SQL Server 2022 CU6 introduced a reported issue where, after applying CU6 or higher and restarting a replica host, some databases on the secondary remain in “Not Synchronizing” instead of returning to “Synchronized”. No official Microsoft fix has been confirmed as of the initial reports. If you are on CU6 or later and see this exact pattern after a host restart, check the build number and try restarting the SQL Server service on the secondary as the immediate workaround. Confirm against the latest CU release notes before treating this as the root cause.

Endpoint stopped or misconfigured

Check sys.tcp_endpoints for the HADR endpoint state. STARTED is required. If it is STOPPED, restart it with ALTER ENDPOINT ... STATE = STARTED. Verify the endpoint port, authentication order, and encryption settings match between primary and secondary.

Prevention

• Track certificate expiry as a first-class signal. Certificate-based endpoint authentication expires silently and produces confusing errors. Alert at 90, 30, and 7 days.
• Monitor the redo queue in time, not just size. Compute redo_queue_size / redo_rate to estimate catch-up time. Alert when it exceeds your RTO.
• Validate failover readiness with planned failover drills. An AG that has never been failed over is an assumption, not a fact.
• Pre-size secondary storage to match the primary. Asymmetric storage is a common root cause of secondary-induced sync issues.
• Keep session timeout at or above 10 seconds. Microsoft does not recommend going below this on heavily loaded systems because of false failures.
• On Linux, monitor Pacemaker as carefully as SQL Server. STONITH failures, node fencing, and resource agent health all affect AG availability. All cluster nodes must run the same Linux distribution.
• Gate pages on sustained duration and the absence of healthy alternatives. Brief NOT_HEALTHY during failover transitions is expected. Page only when the state is sustained beyond the failover-transition window and no other healthy sync target exists.

How Netdata helps

• Per-second AG replica state metrics let you see the exact transition point from HEALTHY to NOT_HEALTHY and correlate it with other signals, instead of relying on the cumulative DMV view or a 60-second scrape interval that misses the transition.
• Correlating HADR_SYNC_COMMIT wait time on the primary with secondary resource metrics (CPU, I/O latency, redo queue) pinpoints whether the secondary is the bottleneck before health flips.
• ML-based anomaly detection on log send rate and redo rate surfaces a degrading secondary before synchronization_health_desc reaches NOT_HEALTHY.
• Certificate expiry tracking as a native signal means expired endpoint certificates do not become a 3 a.m. discovery.
• Sustained-duration alert gating reduces noise during planned failovers, restarts, and AG resume operations, while still paging when failover capability is actually compromised.
• Cluster-aware dashboards that show SQL Server AG state alongside WSFC or Pacemaker health, so you can distinguish a SQL problem from a cluster problem without switching tools.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• SQL Server blocking chains: finding the head blocker before workers run out
• SQL Server buffer cache hit ratio low: when the working set no longer fits in memory
• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server database in SUSPECT or RECOVERY_PENDING: an offline database and how to recover it
• SQL Server Error 1205: transaction was deadlocked and chosen as the deadlock victim
• SQL Server Error 701: there is insufficient system memory to run this query
• SQL Server Error 823 and 824: I/O and logical consistency errors
• SQL Server Error 825: read-retry succeeded and the disk is failing
• SQL Server Error 9002: the transaction log for the database is full
• SQL Server high compilations per second: plan cache pollution and CPU burn