Patching SQL Server and Windows on Always On: A Rolling Update Guide

Applying SQL Server Cumulative Updates or Windows updates in an Always On environment requires more planning than on a standalone server. When the steps are taken in the right order, the interruption users experience across the entire process is limited to a single manual failover. When the order is wrong, the result can be replicas that stop synchronizing, unexpected failovers and extended downtime.

In this guide, we walk through how to plan and apply SQL Server and Windows updates in Always On availability group (AG) and failover cluster instance (FCI) environments, based on Microsoft’s rolling update approach. We also share the queries and commands you can use at each stage.

What Is a Rolling Update?

A rolling update means updating the nodes of a cluster one after another rather than all at once. Nodes that aren’t carrying the active workload are updated first, the workload is then moved to an updated node, and the former active node is updated last. According to Microsoft, this approach limits downtime on the primary to a single manual failover, whether you’re applying a new SQL Server version, a Cumulative Update or a Windows update. If you choose to fail back to the original primary afterwards, that becomes two failovers.

A prerequisite for this approach is that applications always connect through the availability group listener (or, for an FCI, the virtual server name) rather than an individual server name. Applications whose connection strings point directly at a node will try to reconnect to the old server after a failover and will remain unreachable until DNS or the connection settings are updated. In environments that span multiple subnets, adding MultiSubnetFailover=True to the connection string also shortens reconnection time after a failover. We recommend reviewing these connections before the maintenance window.

Before You Start: Preparation Checklist

Most problems that occur during an update could have been identified during preparation. Based on Microsoft’s guidance and on practical experience, we recommend completing the following steps before you begin:

• Validate the update in a test environment first. Check applications, jobs and critical reports in an environment similar to production, and read the known issues list for the CU you plan to apply. We cover this in detail in our article on dangerous SQL Server builds.
• Take a full backup of every availability database and run DBCC CHECKDB. Microsoft explicitly recommends both steps before a rolling update.
• Perform a practice manual failover on at least one synchronous-commit replica. This confirms, before the day of the update, that failover works as expected and that applications reconnect through the listener.
• Install any prerequisite software on all nodes in advance. This avoids unexpected restarts and longer maintenance windows during the update.
• Temporarily move the backup preference to the primary. A database that is being upgraded can’t be backed up. If your backup jobs run on secondary replicas, set the backup preference to the primary before updating the secondaries.
• Make sure Windows Update won’t restart cluster nodes on its own. Automatic restarts on cluster nodes can trigger unplanned failovers. See our article on Windows automatic updates for details.

You can check replica health with the following query. Before starting, every synchronous-commit replica should report HEALTHY:

-- Role, mode and health at the replica level
SELECT ag.name                          AS ag_name,
       ar.replica_server_name,
       ars.role_desc,
       ar.availability_mode_desc,
       ar.failover_mode_desc,
       ars.connected_state_desc,
       ars.synchronization_health_desc
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
     ON ar.group_id = ag.group_id
JOIN sys.dm_hadr_availability_replica_states AS ars
     ON ars.replica_id = ar.replica_id
ORDER BY ag.name, ars.role_desc;

Update Order for Availability Groups

The order Microsoft recommends starts with the least critical replica and works towards the primary. In a simple two-node synchronous AG, the process consists of updating the secondary, failing over and updating the former primary. For larger topologies, we recommend the following sequence:

1. Remove automatic failover. To prevent a node that restarts during the update from triggering an unintended failover, temporarily switch all synchronous-commit replicas to manual failover.
2. Update the asynchronous-commit secondary replicas.
3. Update the remote synchronous-commit secondary replicas.
4. Update the local synchronous-commit secondary replicas.
5. Manually fail over to an updated local synchronous-commit secondary. Before failing over, confirm that the target replica is in the SYNCHRONIZED state.
6. Update the former primary.
7. Restore the automatic failover settings and, if you wish, perform a second manual failover to return to the original primary.

The failover mode is changed from the primary replica. In the following example, the two synchronous-commit replicas in AG1 are switched to manual failover and then restored once the update is complete:

-- Run on the primary: remove automatic failover before the update
ALTER AVAILABILITY GROUP [AG1]
    MODIFY REPLICA ON N'NODE1' WITH (FAILOVER_MODE = MANUAL);
ALTER AVAILABILITY GROUP [AG1]
    MODIFY REPLICA ON N'NODE2' WITH (FAILOVER_MODE = MANUAL);

-- Keep backups on the primary while the secondaries are being updated
ALTER AVAILABILITY GROUP [AG1]
    SET (AUTOMATED_BACKUP_PREFERENCE = PRIMARY);

-- After the update, restore the original settings
ALTER AVAILABILITY GROUP [AG1]
    MODIFY REPLICA ON N'NODE1' WITH (FAILOVER_MODE = AUTOMATIC);
ALTER AVAILABILITY GROUP [AG1]
    MODIFY REPLICA ON N'NODE2' WITH (FAILOVER_MODE = AUTOMATIC);

The AUTOMATED_BACKUP_PREFERENCE setting only takes effect if your backup jobs check sys.fn_hadr_backup_is_preferred_replica. If you use your own backup scripts, you need to verify separately which replica they run on.

Verify Synchronization Before Failing Over

A manual failover should only be performed when every database on the target replica is in the SYNCHRONIZED state. The following query shows each database’s synchronization state, the amount of log waiting to be sent and redone, and whether it is ready for failover:

SELECT ag.name                        AS ag_name,
       ar.replica_server_name,
       drcs.database_name,
       drs.synchronization_state_desc,
       drs.is_suspended,
       drs.log_send_queue_size        AS log_send_queue_kb,
       drs.redo_queue_size            AS redo_queue_kb,
       drcs.is_failover_ready
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar
     ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups AS ag
     ON ag.group_id = drs.group_id
JOIN sys.dm_hadr_database_replica_cluster_states AS drcs
     ON drcs.replica_id = drs.replica_id
    AND drcs.group_database_id = drs.group_database_id
ORDER BY ag.name, ar.replica_server_name, drcs.database_name;

Once is_failover_ready is 1 for every database on the target replica and the queues are empty or very small, you can run the failover command on the target secondary replica:

-- Run on the updated target secondary
ALTER AVAILABILITY GROUP [AG1] FAILOVER;

-- If data movement is suspended after the former primary is updated
-- (run on the former primary, now a secondary, for each database)
ALTER DATABASE [SalesDB] SET HADR RESUME;

This command performs a planned failover without data loss. The FORCE_FAILOVER_ALLOW_DATA_LOSS option is intended for disaster scenarios only and should not be used during a planned update.

Microsoft also recommends always failing over to a synchronous-commit replica. If you fail over to an asynchronous-commit replica, the databases become vulnerable to data loss, and data movement is suspended automatically until it is resumed manually. In environments where the only secondary is a remote asynchronous replica deployed for disaster recovery, the commit mode should be switched temporarily to synchronous and you should wait for the replica to reach SYNCHRONIZED before failing over. Because synchronous commit increases latency to a remote site, we recommend doing this during a period of low traffic.

Updating Failover Cluster Instances (FCI)

The same rolling approach applies to FCIs: SQL Server Setup is run on each node in turn, starting with the passive nodes. According to Microsoft’s documentation, each node being updated is removed from the FCI’s possible owners list during the update, so that an unexpected failover cannot land on a node that hasn’t been updated yet. Once half or more of the nodes have been updated, Setup automatically moves the FCI to an updated node when the next node is updated. If you want to control this behavior, you can run the update from the command line with the /FAILOVERCLUSTERROLLOWNERSHIP parameter.

The interruption users experience during this process consists of a single failover plus the time needed for the database upgrade scripts to run. If FCIs also serve as replicas of an availability group, the passive nodes within each FCI are updated first, and the AG order described above still applies.

Windows Updates

Windows updates should follow the same node-by-node logic. There is, however, an important difference between AG and FCI environments:

• In FCI environments, SQL Server runs as a cluster role, so pausing the node to be updated with Suspend-ClusterNode -Drain safely moves its roles to other nodes.
• In AG environments, moving the primary role through the cluster isn’t recommended. Instead, first fail the AG over to another replica with T-SQL, and then pause the node that no longer hosts the primary role.

# FCI node: pause and drain roles
Suspend-ClusterNode -Name "NODE2" -Drain

# AG node: fail the AG over with T-SQL first, then pause without moving roles
Suspend-ClusterNode -Name "NODE2"

# Apply Windows updates and, if needed, the SQL Server CU; restart the server

# Bring the node back into the cluster (without automatically failing roles back)
Resume-ClusterNode -Name "NODE2" -Failback NoFailback

If both Windows and SQL Server updates are planned for the same maintenance window, applying them together on each node and finishing with a single restart avoids a second round of failovers.

Post-Update Validation

Once every node has been updated, we recommend completing the following checks before considering the process finished:

• Confirm with SELECT SERVERPROPERTY('ProductVersion') that every node reports the expected build. Microsoft supports instances of different versions within the same AG only for the duration of a rolling upgrade, and different CU levels shouldn’t coexist for long either.
• Run the replica and database health queries again, and make sure that no database remains suspended and that the queues have drained.
• Check that the automatic failover and backup preference settings have been restored.
• Review the error log and Agent job history, paying particular attention to the next runs of backup, CHECKDB and index maintenance jobs.
• For databases that use Change Data Capture or replication, check the update’s release notes to see whether additional steps are required.

An updated secondary replica may lag behind for a while until it has received and applied the log that accumulated on the primary during maintenance. How long this takes depends on the level of activity on the primary, and a temporary increase in network traffic should be expected. We recommend waiting for this synchronization to complete before moving on to the next node.