{"id":108370,"date":"2026-02-20T14:00:00","date_gmt":"2026-02-20T14:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=108370"},"modified":"2026-02-17T10:42:53","modified_gmt":"2026-02-17T10:42:53","slug":"how-to-optimize-planned-availability-group-failovers-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/how-to-optimize-planned-availability-group-failovers-in-sql-server\/","title":{"rendered":"How to optimize planned availability group failovers in SQL Server"},"content":{"rendered":"\n

We often perform planned availability group failovers in SQL Server<\/a> for maintenance<\/a>, patching, upgrades, and even hardware rotation. Typically, our failovers are fast, but sometimes they take longer \u2013 and it\u2019s not always intuitive why, as there are no obvious ties to time of day, database size, or transaction volume.<\/p>\n\n\n\n

Shaving even a handful of seconds from the process can improve the application and end user experience; it can also drastically reduce alert noise or, at least, how long alerts<\/a> have to stay muted. There\u2019s a lot of material out there about performing AG failovers correctly<\/a> (no data loss), but far less that focuses on shortening the disruption window. The difference is usually some combination of redo volume, checkpoint behavior, open transactions, and secondary readiness.<\/p>\n\n\n\n

I wanted to share some techniques I use to make planned failovers faster and more predictable. Some of these techniques are well documented, while others come from real-world patterns I\u2019ve observed across many SQL Server environments. I\u2019ll talk about what I do before, during, and after the failover to minimize disruption and increase the chance that end users are oblivious that anything happened.<\/p>\n\n\n\n

How to prepare for a SQL Server failover: a checklist<\/h2>\n\n\n\n

There are several items on my pre-failover checklist that can help with operational times. Some are set-once configuration options, and some are run-once tasks:<\/p>\n\n\n\n

Break apart your availability groups<\/h3>\n\n\n\n

Back when we had problems with failovers, one of the contributing factors was too many databases in an AG (nearly 400). When you fail over 400 databases at the same time, you\u2019re putting a lot of stress on the soon-to-be-primary, and it can\u2019t process all of them at once. Databases waiting to transition are stuck in limbo, and only increase the disruption window.<\/p>\n\n\n\n

The bottleneck is the set of parallel recovery threads allocated per database; more databases means more concurrent recovery workers competing for CPU and I\/O. You may see errors like this one while attempting to bring many databases online:<\/p>\n\n\n\n

Msg 35217, Severity 16, State 1\nThe thread pool for Always On Availability Groups was unable to start a new worker thread because \nthere are not enough available worker threads.  This may degrade Always On Availability Groups\nperformance. Use the \"max worker threads\" configuration option to increase number of allowable \nthreads.\n<\/pre><\/div>\n\n\n\n
That doesn\u2019t mean just increase max worker threads<\/a>, which may be counter-productive unless you also increase the number of CPUs able to conquer the work and somehow address bursts of concurrency.<\/p>\n\n\n\n
Note that, unless you move AGs to other replicas, breaking your AGs into smaller sets won\u2019t reduce the overall process time<\/em>, since you just have to fail over more AGs. But it sure will make each AG finish its own failover quicker.<\/p>\n\n\n\n
Enable indirect checkpoints<\/h3>\n\n\n\n
Any CHECKPOINT<\/code> on the primary can reduce redo time, because the secondary will have fewer dirty pages to recover. This does not mean I can eliminate redo altogether, since it depends on the redo backlog and how much log is being generated during the failover window.<\/p>\n\n\n\n
Depending on the version of SQL Server, and where the databases were originally created, they may still be set to use old-style checkpoints. The newer approach of using indirect checkpoints can have many performance benefits, most notably smoother I\/O patterns. I talk about some of the benefits in Why Enable SQL Server Indirect Checkpoints<\/a> and “0 to 60” : Switching to indirect checkpoints<\/a>, and you can see Microsoft\u2019s explanation of the changes in Changes in SQL Server 2016 Checkpoint Behavior<\/a>.<\/p>\n\n\n\n
To find any databases using legacy checkpoints:<\/p>\n\n\n\n
SELECT name \n  FROM sys.databases \n WHERE target_recovery_time_in_seconds = 0;\n<\/pre><\/div>\n\n\n\n
To fix:<\/p>\n\n\n\n
ALTER DATABASE <db name> \n  SET TARGET_RECOVERY_TIME = 60 SECONDS;\n<\/pre><\/div>\n\n\n\n
On modern versions of SQL Server, I haven\u2019t found any compelling reason to keep the legacy checkpoint behavior.<\/p>\n\n\n\n
Run manual checkpoints<\/h3>\n\n\n\n
Automatic checkpointing often lags in environments like ours, with more databases than CPU cores, because the engine cycles through databases cooperatively. Indirect checkpoints can help, but may struggle to keep up under extreme write pressure or when a lot of databases are involved.<\/p>\n\n\n\n
Before failovers, I\u2019ve been manually running checkpoints and, while this is anecdotal, have observed substantially reduced failover times as a result. Admittedly, I had to spend more time up front waiting for those checkpoints to run, but extra time before<\/em> the failover is better than spending more time on redo during<\/em> the failover.<\/p>\n\n\n\n
I can generate the commands to run checkpoints in each database for a given AG, with an overly cautious filter to triple-check that I\u2019m on primary:<\/p>\n\n\n\n
DECLARE @AGName sysname = N'<ag name>',\n        @sql    nvarchar(max);\nSELECT @sql = STRING_AGG\n              (\n                CONVERT(nvarchar(max), \n                CONCAT(N'EXEC ', QUOTENAME(name), \n                N'.sys.sp_executesql N''CHECKPOINT'';')), \n                char(13)+char(10)\n              )\n  FROM sys.databases AS d\n WHERE d.state = 0 \n   AND EXISTS\n   (\n     SELECT 1\n       FROM sys.dm_hadr_database_replica_states AS rs\n      INNER JOIN sys.availability_groups AS ags\n         ON rs.group_id = ags.group_id\n      WHERE rs.database_id        = d.database_id\n        AND ags.name              = @AGName\n        AND rs.is_local           = 1\n        AND rs.is_primary_replica = 1\n   );\nPRINT @sql;\n\/* EXEC sys.sp_executesql @sql; *\/<\/pre><\/div>\n\n\n\n
If this takes a while, I run it again to capture any new bursts of write activity that occurred after the first pass. And I fail over that AG before I start checkpoints on the next AG.<\/p>\n\n\n\n
Something I can add to the query to minimize any gaps is an ORDER BY<\/code> to sort the databases so that the busiest<\/em> databases run last (at least when I know or take the time to determine which databases are busiest, or have the highest page churn.) <\/p>\n\n\n\n
On our primary public system, there should be no surprise which database is our busiest; I run CHECKPOINT<\/code> in a loop against only that database, every dozen seconds or so, right up to the point I initiate the failover. But this is in a very specific, very busy database; once dirty page volume is already low, repeated checkpoints may provide diminishing returns and are generally likely to just move work instead of reduce work.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Disable long-running processes<\/h3>\n\n\n\n
Failover cannot complete until the secondary has applied all log records sent from the primary via the send queue. The send queue size directly influences failover time, as Microsoft documents in Monitor AG performance<\/a> \u2013 along with a detailed list of operations in the data synchronization process. Even a modest backlog slows recovery; quieting heavy workloads before failover allows the queues to clear.<\/p>\n\n\n\n
We disable our log backup<\/a> jobs shortly before the failover to eliminate additional log generation and movement. We avoid performing failovers anywhere near full or differential backups (if these conflict, we adjust the start date of those jobs to well after the expected end of the maintenance window). We also disable any jobs known to perform heavy writes or long-running transactions \u2013 think ETL-style jobs and index maintenance<\/a> \u2013 which can extend undo and redo time.<\/p>\n\n\n\n
Consider enabling Accelerated Database Recovery (ADR)<\/h3>\n\n\n\n
If you\u2019re reading this today and your failover is planned for tonight, it\u2019s almost certainly too late for this, but Accelerated Database Recovery (ADR) is a setting we have enabled everywhere and has helped in many aspects \u2013 including detaching recovery time from transaction length (I talk about how this helps in Accelerated Database Recovery in SQL Server 2019<\/a>). <\/p>\n\n\n\n
For failovers specifically, this makes undo asynchronous, though it can\u2019t change redo\u2026 so even with ADR, disabling long-running processes and anything else you can do to minimize redo time will still be a win.<\/p>\n\n\n\n
Disable read-only routing<\/h3>\n\n\n\n
If the primary can handle it, I disable read-only routing<\/a> several hours before the failover, or before whatever action is leading up to the failover (like patching, upgrading, or rebooting the readable secondary first). This doesn\u2019t make the failover faster, but it makes the pre-failover and secondary maintenance phases safer. <\/p>\n\n\n\n
Depending on how much read-intent traffic the applications are sending to the secondary, it can take a while to drain. The application may fail if it is using connection pooling and some of those sticky sessions are still holding and using connections to the secondary while patching or rebooting. <\/p>\n\n\n\n
If I can roll through the application nodes and recycle them or otherwise flush their connection pools, any re-established sessions should only be connected to the primary. I monitor the active connections on the secondary and make sure they are winding down to almost nothing (ignoring usual suspects like monitoring tools<\/a> and system sessions).<\/p>\n\n\n\n
Warm the secondary<\/h3>\n\n\n\n
It can be very helpful to warm up the plan cache<\/a> and buffer pool on the current secondary before<\/em> it becomes primary. This is especially true if I\u2019ve restarted the secondary before the failover, since its plan cache and buffer pool will both be empty (and because, hopefully, no read-only traffic is going there yet).<\/p>\n\n\n\n
I warm the plan cache by executing a battery of known, expensive SELECT<\/code> queries against the secondary (after it is restarted, if it is being restarted), to avoid a compile storm when it becomes primary and all those queries suddenly hit it all at once.<\/p>\n\n\n\n
An even bigger benefit is filling the buffer pool so that users bypass I\/O-related waits while those pages get loaded into memory. To do this, I perform a series of queries that cause full scans of our largest tables and most frequently used indexes. I like the COUNT_BIG(*)<\/code> approach, which forces a full index scan and minimizes per-row CPU overhead. I\u2019ve seen techniques out there like CHECKSUM_AGG(CHECKSUM())<\/code> which is likely to punish my CPUs a lot more.<\/p>\n\n\n\n
SELECT COUNT_BIG(*) \n  FROM dbo.<huge table> WITH (INDEX(1));\nSELECT COUNT_BIG(*) \n  FROM <huge table> WITH (INDEX(<heavily used index>));\n<\/pre><\/div>\n\n\n\n
This does not guarantee identical performance immediately after failover, but significantly reduces the initial shock of cold cache and compile storms.<\/p>\n\n\n\n
Note that these options aren\u2019t possible in an upgrade scenario; the secondary won\u2019t be readable after it has been upgraded but before it has become primary. Microsoft says, \u201cOnly replicas that are on the same major build of SQL Server will be readable.<\/a>\u201d In that scenario, it\u2019s still something I can do immediately after<\/em> it has become primary; it\u2019s certainly a better end user experience if I<\/em> suffer some of those resource pains instead of them.<\/p>\n\n\n\n
Utilize Query Store trace flags<\/h3>\n\n\n\n
In environments where I use Query Store<\/a>, there are two trace flags that can help reduce the time spent during failovers, service restarts, and reboots, especially if Query Store is large.<\/p>\n\n\n
\n