A Patch in AlwaysOn Availability Group needs a sequence matched to the topology. I start this two-replica example with the secondary.
These steps describe a planned rolling update. A major-version upgrade has additional mixed-version restrictions, and a distributed group or multiple groups sharing replicas needs a topology-specific sequence.
- Record the topology, versions, commit modes and normal failover settings. Check the patch release notes, cluster health, tested database backups and integrity checks; rehearse the change.
- Temporarily remove automatic failover from synchronous-commit partners. Apply the selected, tested update to SQL2, the secondary, and complete its required restart. Don’t install unrelated updates simply because they are available.
- Verify SQL2 has the expected build and is healthy. For a planned failover without data loss, both partners must use synchronous commit and every database on the target must be synchronized. Connect to SQL2 and perform the planned manual failover.
- Confirm clients reconnect and SQL2 serves the workload. Patch SQL1, the former primary, and verify its health and data movement after restart.
- After SQL1 is synchronized, fail back only if the maintenance plan requires it. Restore the intended commit and failover settings and validate application behavior, monitoring and backup jobs.
A green dashboard alone is not enough. Confirm the target databases are synchronized and the target is ready for a planned failover. An asynchronous replica requires a different preparation, and forced failover can lose data. A VM snapshot is also not a substitute for a proven SQL Server restore path.
The original aim was a short checklist, not a promise of zero interruption. Allow for client reconnects and the availability effect of the chosen synchronization requirements while a replica is offline.
Reference: Microsoft rolling-update sequence.
A rolling update is not a zero-interruption promise, it is a sequence whose availability effects need planning.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
18 Comments. Leave new
If there is a file share witness (FSW) involved, where would the update and reboot of that instance take place in the scenario above?
If you are using a file share witness, where would the update of that instance fall into the steps above?
FileShareWitness is for Windows Clustering purpose. It was nothing to do with my above steps.
What do you mean in Step 3 by saying “Refresh the affected databases on the secondary replica and make sure that everything is green on the dashboard”? What do I have to do for this “Refresh”? What is the dashboard you are talking about?
If this process needed to be followed for an availability group with 2 secondary replicas, would it just be a matter of patching both secondary replicas (repeating steos 4 – 7 for both) before failing over the primary and patching it?
Nice Article
Hi Pinal
I assume that the point of switching to manual failover is to prevent that the primary replica will try to fail over if needs arise during the patching of the secondary replica.
But alas it doesn’t help. Whether you have M or A failover, if the quorum is lost the cluster services will shutdown.
So what is the point? Besides I see a risk of changing a working setup in the first place.
Hi Tonny,
The example that you are talking about is for Windows Cluster and not for Always on, These are two different concepts & Quorum is no where in picture in the case of Always On.
Can I take the same approach when the secondaries will be down for a day for maintenance purposes and the DR will be the primary for a day
Do we need to change the synchronization mode from synch to asynch after upgrading the secondary node and failover from primary to secondary.
I have SQL server 2012 which configured with Always On High Availability and now i want to update the latest service pack of SQL server do i need to follow the above steps, because if i update the Server 1 will it impact on the synchronization of the database on the server 2.
Dear Dave,
I have SQL server 2012 with synchronized database (like Active and Passive) the Server 1 is for Application uses and server 2 is Read only. now i want to upgrade the SQL server patch for the server 1 and will it impact synchronization.(patch upgrade to – 13.0.4574.0 – May 16, 2019)
Dear Dave,
I have SQL server 2012 configured Always on having one primary replica and four secondary replicas.
What will be patching process for it.
thank you.
Dave, Great article. Do we need to change to ‘M’ for SQL2 before patching? the steps are in numeric order but you had reference step a) and step b) in your last statement. Please clarify where they are. Thanks
You don’t mention turning off your backups during this process and then back on after.
Is patching of the AlwaysOn through Azure automation possible?
thanks, a few comments here:
1. for sql aoag, when perform fail over , it is recommended to initiate from SSMS
2. when reboot cluster nodes, need to drain it as recommended by MSFT
cheers
Besides limiting downtime, is there any other reason to perform the failover? What if downtime is not a concern? Do I still need to perform a failover?