SQL SERVER – How to Apply Patch in AlwaysOn Availability Group Configuration?

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.

1. 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}
2. From the node acting as the primary replica (SQL1), change the failover mode to manual
3. Refresh the affected databases on the secondary replica (SQL2) and make sure that everything is green on the dashboard.
4. Apply the patch (service pack of CU) on SQL2.
5. 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.
6. Double check that patches have been applied, the cluster is healthy and AlwaysOn Availability Groups are functional.
7. Make sure that synchronization state is SYNCHRONIZED.
8. Fail over the availability group to the secondary replica (SQL2).
9. Refresh the affected databases on secondary Replica (former primary = SQL1) until the synchronization state is synchronized.
10. Apply the patch (service pack of CU) on SQL1.
11. 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.
12. Double check that patches have been applied, the cluster is healthy and AlwaysOn Availability Groups are functional.
13. Make sure that synchronization state is SYNCHRONIZED.
14. Fail over the availability group to the primary node (back to SQL1).
15. 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 (http://blog.SQLAuthority.com)