SQL Server Always On failover: the checklist, not the theory

Microsoft documents how Always On works. What it does not give you is the page someone reads at 3am with a phone in one hand: check these things, in this order, before and after moving the primary.

This is that page. It assumes the availability group is already built and you need to fail over deliberately — a patch window, a host migration, or a planned test.

Before you touch anything

Failover is quick. The damage comes from failing over into a replica that was not ready, so the checks below are the actual work.

Is the target replica synchronised?

SELECT ag.name AS ag_name,
       ar.replica_server_name,
       drs.database_id,
       drs.synchronization_state_desc,   -- must be SYNCHRONIZED
       drs.synchronization_health_desc,  -- must be HEALTHY
       drs.log_send_queue_size,          -- KB still to ship
       drs.redo_queue_size               -- KB still to apply
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups   ag ON ag.group_id  = ar.group_id
WHERE ar.replica_server_name = 'TARGET-NODE';

SYNCHRONIZED and HEALTHY on every database in the group, not just the ones you care about. A single database in SYNCHRONIZING blocks an automatic failover and turns a manual one into data loss.

A non-zero redo_queue_size means the replica has received log records it has not yet applied. Failing over now means waiting for that redo to finish before the database comes online — which is exactly when someone assumes the failover has hung.

Is the replica configured to be a primary?

• •Availability mode: synchronous commit, if you intend zero data loss.
• •Failover mode: automatic or manual, matching what you are about to do.
• •Readable secondary setting: if it was read-intent only, connections behave differently once it becomes primary.
• •Backup preference: check whether backup jobs will follow the primary or stay pointed at the old node.

The things that do not live in the availability group

This is where planned failovers go wrong, because none of it replicates with the databases:

-- run on BOTH nodes and diff the output before you fail over
SELECT name, type_desc, is_disabled FROM sys.server_principals
WHERE type IN ('S','U','G') AND name NOT LIKE '##%'
ORDER BY name;

SELECT name, enabled FROM msdb.dbo.sysjobs ORDER BY name;

SELECT name, product, provider, data_source FROM sys.servers WHERE is_linked = 1;

If the diff is not empty, fix it before the window rather than during it. Jobs on the secondary should exist but be disabled, then enabled as part of the failover — not created from memory afterwards.

Will the application follow?

The listener is the whole point, and it only works if clients use it. Confirm connection strings actually name the listener rather than a node, and that MultiSubnetFailover=True is set where the replicas are in different subnets. Without it, a client can spend the DNS TTL trying the old address.

The failover

-- from the TARGET node, and read the message it prints
ALTER AVAILABILITY GROUP [AG_Name] FAILOVER;

-- if it refuses, it is protecting you. Do not reach for this
-- unless you have accepted the data loss out loud:
-- ALTER AVAILABILITY GROUP [AG_Name] FORCE_FAILOVER_ALLOW_DATA_LOSS;

FORCE_FAILOVER_ALLOW_DATA_LOSS does what its name says. After using it, every other replica must be resumed manually and may need reseeding. It is a disaster-recovery action, never part of a planned window.

Immediately after

• •Confirm the new primary owns the group and every database is ONLINE, not RECOVERING.
• •Enable the SQL Agent jobs on the new primary and disable them on the old one, or they run twice.
• •Verify the listener resolves to the new primary from a client machine, not from the server.
• •Check that data movement to the remaining replicas is resumed rather than suspended.
• •Run one real application transaction end to end. Connectivity is not the same as working.
• •Look at the error log on both nodes for the failover sequence — it records what actually happened, which is often not what you assumed.

-- who is primary now, and is everything actually online
SELECT ag.name,
       ar.replica_server_name,
       ars.role_desc,
       ars.operational_state_desc,
       ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ar.replica_id = ars.replica_id
JOIN sys.availability_groups   ag ON ag.group_id  = ar.group_id;

SELECT name, state_desc FROM sys.databases WHERE database_id > 4;

Failing back

Do not fail back immediately because the original node is "the proper primary". Wait until it has caught up and is SYNCHRONIZED, then fail back deliberately during a second window. Two failovers in quick succession is two outages, and the second one is the avoidable one.

If automatic failover is enabled, check the flexible failover policy before assuming the cluster will not move things on its own while you are still investigating.

What to test on a schedule

Record how long each took. An availability group whose failover time nobody has measured has an RTO of "we hope".

Where this fits

We run MSSQL Always On environments and PostgreSQL HA on Patroni, including the boring part: making sure logins and jobs match on both nodes before anyone needs a failover. €65/hour for audits and remediation, or a monthly retainer with 24/7 cover.