This is one of the common question which is asked via emails to me. “How to apply the patch in AlwaysOn Availability Group configuration?” OR “What are the steps we should do and things to take care while patching availability replica?”
There might be many articles which would provide same details, but I want to make it short and crisp.
I am going to pick step by step for an Availability Group with one secondary replica.
- Make sure that we have taken good recent OS backup with system state (or VMware snapshot with SQL services stopped), a good recent backup of all databases and a successful completion of a checkdb on the primary node. {This is not mandatory, but to avoid “Ouch” moment}
- From the node acting as the primary replica (SQL1), change the failover mode to manual
- Refresh the affected databases on the secondary replica (SQL2) and make sure that everything is green on the dashboard.
- Apply the patch (service pack of CU) on SQL2.
- Repeat the Windows Update and/or software updates until all available patches are applied. Do not move on with the patching steps until all patches and post patch reboot and configuration tasks are completed.
- Double check that patches have been applied, the cluster is healthy and AlwaysOn Availability Groups are functional.
- Make sure that synchronization state is SYNCHRONIZED.
- Fail over the availability group to the secondary replica (SQL2).
- Refresh the affected databases on secondary Replica (former primary = SQL1) until the synchronization state is synchronized.
- Apply the patch (service pack of CU) on SQL1.
- Repeat the Windows Update and/or software updates until all available patches are applied. Do not move on with the patching steps until all patches and post patch reboot and configuration tasks are completed.
- Double check that patches have been applied, the cluster is healthy and AlwaysOn Availability Groups are functional.
- Make sure that synchronization state is SYNCHRONIZED.
- Fail over the availability group to the primary node (back to SQL1).
- Change the failover mode to Automatic now (which we changed in Step b)
In case things do not go as planned, you have followed step a) so you know what needs to be done.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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?
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?