Wednesday, May 17, 2017

How to Apply Patch in AlwaysOn Availability Group Configuration?

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.

2 comments:

  1. How to do it if it is in SQL Server Cluster?

    ReplyDelete
  2. Thanks for putting Patching steps.
    I found the same steps here at https://blog.sqlauthority.com/2017/03/04/sql-server-apply-patch-alwayson-availability-group-configuration/

    ReplyDelete