Quorum/Fencing, Split-Brain Prevention, Planned/Unplanned Failover, and HA Testing

Learning outcomes

HA design is not complete when a wizard turns green. It is complete when failure behavior is known, authority remains unambiguous, data-loss conditions are understood, clients reconnect inside an agreed objective, and operators can reverse unsafe actions. ServiceHub therefore closes the chapter with a drill model rather than another configuration screen.

1. Start with the failure, not the failover command

SELECT SERVERPROPERTY('IsHadrEnabled') AS hadr_enabled,       SERVERPROPERTY('IsClustered') AS is_fci,       SERVERPROPERTY('ServerName') AS server_name;GOSELECT ag.name,ar.replica_server_name,ars.role_desc,       ars.connected_state_desc,ars.operational_state_desc,       ars.synchronization_health_descFROM sys.availability_groups AS agJOIN sys.availability_replicas AS ar ON ar.group_id=ag.group_idLEFT JOIN sys.dm_hadr_availability_replica_states AS ars ON ars.replica_id=ar.replica_id;GOSELECT DB_NAME(database_id) AS db,synchronization_state_desc,       synchronization_health_desc,log_send_queue_size,redo_queue_size,       last_hardened_time,last_redone_timeFROM sys.dm_hadr_database_replica_states;GO

2. Planned, automatic and forced failover are not synonyms

A planned manual failover without data loss is a controlled role change to a synchronized synchronous secondary. Automatic failover requires an eligible automatic-failover pair plus cluster/health conditions. Forced failover is for disaster recovery when synchronization cannot be guaranteed; it can lose transactions. The runbook must say who can authorize forced failover, how the old primary is fenced, and how divergent history will be handled if it returns.

USE ServiceHubLab;GOIF OBJECT_ID(N'lab16.FailoverDrill',N'U') IS NULLCREATE TABLE lab16.FailoverDrill(  drill_id bigint IDENTITY PRIMARY KEY,  scenario varchar(80) NOT NULL,  started_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),  expected_primary sysname NULL,  observed_primary sysname NULL,  data_loss_possible bit NOT NULL,  client_reconnect_required bit NOT NULL,  outcome nvarchar(300) NULL);INSERT lab16.FailoverDrill(scenario,expected_primary,data_loss_possible,client_reconnect_required,outcome)VALUES ('Planned synchronous failover',N'SQL-B',0,1,N'Run only after synchronized-state and client checks'), ('Forced DR failover of unsynchronized replica',N'SQL-C',1,1,N'Requires business authorization and old-primary fencing');SELECT * FROM lab16.FailoverDrill ORDER BY drill_id;GO

3. Fencing prevents two writable histories

During a partition, the most dangerous outcome is not temporary downtime—it is two owners independently accepting writes. WSFC quorum and cluster resource ownership are designed to avoid this. Pacemaker deployments use their own quorum and fencing mechanisms. Forced quorum or manual disaster actions must establish one authoritative side and isolate the other before service is resumed.

# Optional WSFC topology only; evidence collection is safer than immediately moving resources.Get-ClusterNode | Format-Table Name,State,NodeWeight,DynamicWeightGet-ClusterQuorumGet-ClusterGroupGet-ClusterResource# For SQL Server 2025 AG resources, review RestartThreshold policy deliberately.# (Get-ClusterResource '<AG resource>').RestartThreshold

SQL Server 2025 permits setting the AG resource RestartThreshold to 0 so WSFC can fail over immediately instead of first attempting a local AG resource restart for persistent issues. Treat that as a measured policy change: it trades local-restart opportunity for faster cross-node failover.

4. The user-visible RTO ends at successful application work

A database becoming PRIMARY does not mean the service has recovered. DNS/listener resolution, TLS certificate names, connection pools, transaction retries, application caches and upstream dependencies all matter. Use a synthetic transaction that exercises the real listener and a safe business path, and measure from failure injection to accepted application operation.

1. Record UTC start time and current primary/commit marker.2. Start a low-rate synthetic transaction through the listener.3. Inject exactly one approved failure: service, node, network, or planned role move.4. Record cluster authority and AG role/synchronization transitions.5. Record listener/DNS resolution and client reconnect/retry events.6. Confirm one writable primary and run an application write + read acceptance check.7. Measure outage/RTO and compare the last committed marker for data loss.8. Restore normal topology, rejoin/resynchronize former replicas, and verify backups/jobs.

Drivers should use documented listener settings. For multisubnet listeners, current Microsoft drivers support MultiSubnetFailover; use it according to the provider documentation. Retry only operations that are safe to retry—if the client lost the connection after commit, it might not know whether the transaction committed. Application idempotency keys can prevent duplicate business operations.

5. A progressive drill matrix

USE ServiceHubLab;GODROP TABLE IF EXISTS lab16.FciDependency;DROP TABLE IF EXISTS lab16.FailoverDrill;DROP TABLE IF EXISTS lab16.ReplicaModel;DROP TABLE IF EXISTS lab16.ClusterVote;GOIF SCHEMA_ID(N'lab16') IS NOT NULL   AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE schema_id=SCHEMA_ID(N'lab16'))   EXEC(N'DROP SCHEMA lab16;');GO

6. Production judgment

Production judgment. Keep a dated topology diagram, quorum/witness rationale, RPO/RTO objectives, preferred owners/failover pairs, listener/driver settings, backup ownership, synchronization/queue thresholds, forced-failover authority, fencing steps, rollback path and drill evidence. Test after meaningful infrastructure, patch, driver, security or topology changes.

Chapter 17 moves from HA to data movement: replication, log shipping, distributed availability and other mechanisms that can copy data for reporting/DR but have different consistency, operational and recovery contracts.

Authoritative references

• Failover and failover modes — planned, automatic and forced failover including SQL Server 2025 changes
• WSFC quorum modes and voting — authority and split-brain prevention
• Availability Group listener connectivity — client routing/failover behavior
• SQL Server 2025 editions and supported features — HA feature/edition boundaries