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?”

SQL SERVER - How to Apply Patch in AlwaysOn Availability Group Configuration? alwaysonpatch-800x317

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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Add Failover Cluster Node Fails With Error – This SQL Server Edition Does Not Support the Installed Number of Cluster Nodes
Next Post
SQL SERVER – SQL Browser not Starting. Error – The SQL Configuration for SQL is Inaccessible or Invalid

Related Posts

16 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?

    Reply
  • If you are using a file share witness, where would the update of that instance fall into the steps above?

    Reply
  • Alexander Gofman
    August 15, 2017 8:26 pm

    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?

    Reply
  • 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?

    Reply
  • Nice Article

    Reply
  • 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.

    Reply
    • Sandesh Chaturvedi
      February 6, 2019 1:07 pm

      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.

      Reply
  • 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

    Reply
  • Muhammad ZOhaib Khan
    April 11, 2019 10:55 am

    Do we need to change the synchronization mode from synch to asynch after upgrading the secondary node and failover from primary to secondary.

    Reply
  • 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.

    Reply
  • 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)

    Reply
  • Rajitha Banoth
    October 24, 2019 5:57 pm

    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.

    Reply
  • 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

    Reply
  • Tony Covarrubias
    April 24, 2020 12:14 am

    You don’t mention turning off your backups during this process and then back on after.

    Reply
  • Is patching of the AlwaysOn through Azure automation possible?

    Reply

Leave a Reply

Menu