It has long been the recommended practice to patch your SQL Server environments staying as close as possible to the latest Service Pack that has been released by Microsoft for the particular version of SQL Server that you are running and supporting in your environments. A lot of people follow the practice of applying Cumulative updates only if and when there is a specific need to. If you are following this approach I recommend you reviewing your patching strategy and look at incorporating a regular patching strategy allowing you to keep up with the releases of the cumulative updates being released. The reason I am recommending this new approach to patching is Microsoft are continually releasing functionality to the version and allows you to take advantage of this with the patch upgrades, It also means your environment is staying as up todate as possible.
In this post we are going to walk through how to apply a SQL Server 2016 SP to an environment that is configured in a 3 node Windows Server Failover cluster utilizing Always On Availability Groups. This approach allows us to take advantage of the benefits that Availability Groups give us with minimizing our planned downtime for patching our SQL Server environment.
Always plan and test any patching of SQL Server in a non-production environment before you apply it into production to ensure that your process is correct, you have the ability to rollback if needed and you are able to test your applications ensuring that the patches do not adversely impact them. Planning Planning Planning is the key to all things with SQL Server Databases.
In this post I am not going to be talking about the full list of steps that you should be implementing prior to applying the Service Pack allowing you to have a roll back strategy if it is required.
For this post my demo environment consists of the following:
- 3 Hyper-V Windows Servers running Windows Server 2012 R2
- SQL Server 2016 RTM CU1
- Always On Availability Groups configured in a Synchronous mode
Now that we are ready to apply SQL Server 2016 SP1 to our SQL Server environment these are the steps that I undertake:
- In Management Studio connect to our secondary replica (Node 2)
- Expand AlwaysOn High Availability | Availability Databases
- Right click on the database(s) in the Availability Group and select “Suspend Data Movement”
- In Management Studio connect to our primary replica (Node 1) and confirm that the data transfer between Node 1 and Node 2 has been paused. Use the Dashboard to confirm.
- In Windows Explorer navigate to the location you have saved the SQL Server 2016 SP1 executable
- Double Click on the executable to start applying SQL Server 2016 SP1
- Accept the Terms and Conditions to proceed and click Next
- Select the Instances that you wish to apply the Service Pack to and click Next
- Once the file check has completed click Next
- Review the summary of what you are about to apply Service Pack 1 to and click Update
- Confirm that the Installation succeed and click Close
- In Management Studio connect to Node 2 and perform a refresh and confirm that the Version number has now changed to 13.0.4001.0
- In Management Studio connect to Node 2 and resume the data movement between the primary replica (node 1) and the secondary replica (node 2) that we have just applied the service pack against
- In Management Studio connect to the primary replica, Open the Dashboard and confirm that the data transfer resumes successfully. Once it is synchronized (if you are in a Synchronous commit environment) you can run through all of the above same steps for Node 3.
- Once Node 3 has completed with the installation of SP1 and it is synchronised/synchronising and the send and redo queues are back to normal we can now perform a failover from the current primary (Node 1) to Node 2
- In Management Studio connect to our new primary replica (Node 2) confirming that the failover is successful and that your Availability Databases are online and available. Confirm that synchronization is occurring from the primary replica to both of your secondary replica’s (nodes 1 & 3)
- We are now at the stage where we can repeat the steps we have undertaken on nodes 2 & 3 on node 1 to apply SQL Server 2016 SP1
- Once SP1 has completed successfully on node 1 and we have restarted the data transfer from node 2 back to node 1, having confirmed we are synchronized we are able to undertake our second failover from Node 2 to node 1 leaving us in the same position we were in prior to starting the patching process.
- It is now time for you to undertake your application testing against your newly patched SQL Server 2016 environment.
Now remember each environment is different and you need to plan before applying anything. This post is written without taking into consideration any environment outside of my demo environment.
Leave a Reply