Always on HA Automatic failover testing scenarios

Stopping the primary SQL Server service is a valid failure-path test, but automatic failover succeeds only when both replicas use synchronous commit and automatic failover and every database on the target secondary is SYNCHRONIZED; a persistent RESOLVING state means WSFC initiated recovery but could not promote the secondary.

Why the other tests succeeded

The tests exercise different paths:

• Manual failover succeeded: validates a planned role transition, but not WSFC health detection or automatic-failover policy.
• Server restart succeeded: validates node-loss detection and quorum behavior.
• SQL Server service stop failed: exercises resource health detection. WSFC can first try to restart an unhealthy resource on its current node and then fail it over according to its configured policies; therefore, stopping the service does not necessarily produce an immediate role change.^1^

A brief transition through RESOLVING is normal. A successful transition continues through PRIMARY_PENDING to PRIMARY_NORMAL. Remaining in RESOLVING means the automatic failover was unsuccessful and the databases normally become inaccessible or NOT SYNCHRONIZING.^2^

1. Check readiness before repeating the test

In SSMS, open Always On High Availability → Availability Groups → Show Dashboard and verify:

• Primary replica: Synchronous commit, Automatic
• Target secondary: Synchronous commit, Automatic
• Every availability database: Synchronized
• Secondary replica: connected and healthy

Both replicas must form an automatic-failover set. If either replica uses manual failover, or the target is not synchronized, automatic failover cannot occur.^3^

Run this on the target secondary immediately before the test:

SELECT database_name, is_failover_ready
FROM sys.dm_hadr_database_replica_cluster_states
WHERE replica_id IN
(
    SELECT replica_id
    FROM sys.dm_hadr_availability_replica_states
);

Every row must return is_failover_ready = 1. A zero for any AG database can prevent automatic failover.^2^

If a row is zero, correct that database’s synchronization state before testing again. Do not use forced failover as an HA test unless possible data loss is acceptable.

2. Capture the failed service-stop test

Immediately after reproducing the issue, generate cluster logs for both nodes from an elevated PowerShell window:

Get-ClusterLog -Node "PrimaryNode" -TimeSpan 15
Get-ClusterLog -Node "SecondaryNode" -TimeSpan 15

The logs are created under %WINDIR%\Cluster\Reports. Correlate them with:

• SQL Server error log on both replicas
• Event Viewer → Applications and Services Logs → Microsoft → Windows → FailoverClustering → Operational
• The exact service-stop timestamp

Search Cluster.log for the AG resource name and these indicators:

Failure threshold exhausted

If the log says:

Not failing over group ..., failoverCount ..., computedFailoverThreshold ...

open Failover Cluster Manager → Roles → AG role → Properties → Failover and inspect Maximum Failures in the Specified Period. Increase it only after determining why repeated failures consumed the threshold; raising it alone can hide an underlying recurring failure.^2^

Health-check permissions failure

If the log reports that the diagnostics command lacks permission, restore these permissions to the local NT AUTHORITY\SYSTEM login on the secondary instance:

• CONNECT SQL
• VIEW SERVER STATE
• ALTER ANY AVAILABILITY GROUP

The AG resource DLL uses that local account for health monitoring during promotion.^2^

3. Retest safely

1. Confirm the dashboard is healthy and every database is failover-ready.
2. Confirm WSFC has quorum.
3. Keep a separate administrative connection to both nodes.
4. Record the timestamp.
5. Stop the SQL Server service on the primary.
6. Observe whether WSFC first restarts the resource locally or transfers the AG role.
7. Confirm the target reaches PRIMARY_NORMAL, then test the application through the AG listener.

Do not change RestartThreshold, lease timeout, or health-check timeout without the SQL Server version and corresponding cluster-log evidence; those settings can materially change failure detection and availability behavior.

References

1. Windows Server Failover Clustering with SQL Server
2. Troubleshooting automatic failover problems in SQL Server Always On environments
3. Failover and Failover Modes (Always On Availability Groups)