How to Patch a SQL Server Failover Cluster

How to Patch a SQL Server Failover Cluster

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
Patching SQL Server is one of the main administrative tasks in the DBA’s life.

However, patching SQL Server is a procedure that needs to be planned, tested, and performed very carefully.

Patching SQL Server Failover Cluster

Below you can find a suggested methodology for patching a failover clustered instance of SQL Server. This example assumes a two-node failover cluster but can be easily extended to failover clusters with more nodes as the principle is the same.

1. Plan ahead, decide the patches to be installed
2. Check the patch requirements, check if there is enough disk space on the cluster nodes (especially on the drives the system databases and SQL Server binaries are located), check the consistency of all your SQL Server databases on the instances to be patched,

3. Apply the patch(es) on your Test environment by following the below steps:
3.1 Notify the affected parties. Get the required approvals.
3.2 Backup sytem databases: master, model, msdb
3.3 Backup the resource database (more info)
3.4 Backup user databases
3.5 Failover all SQL cluster roles to Node A (minimal downtime might occur during the switching – inform users)
3.6 Install the patches on Node B (passive)
3.7 Restart Node B
3.8 Failover all SQL cluster roles to Node B (minimal downtime might occur during the switching – inform users)
3.9 Install the patches on Node A (passive)
3.10 Restart Node A

4. Certify that everything works well on the Test failover cluster after the installation of patches is completed (perform tests with Node A active, then Node B active, etc.). You need to get acceptance by all affected parties (i.e. IT users, application owners, etc.).

5. If everything works well on the Test environment and you have the green light to proceed with patching Production, then you may follow the below steps (if not, then perform patch rollback on all cluster nodes and restore system databases):
5.1 Notify the affected parties
5.2 Backup sytem databases: master, model, msdb
5.3 Backup the resource database (more info)
6.4 Backup user databases
6.5 Failover all SQL cluster roles to Node A (minimal downtime might occur during the switching – inform users)
6.6 Install the patches on Node B (passive)
5.7 Restart Node B
5.8 Failover all SQL cluster roles to Node B (minimal downtime might occur during the switching – inform users)
5.9 Install the patches on Node A (passive)
5.10 Restart Node A

6. Certify that everything works well on the failover cluster after the installation of patches is completed (perform tests with Node A active, then Node B active, etc.). You need to get acceptance by all affected parties (i.e. IT users, application owners, etc.). If there are issues, perform patch rollback on all cluster nodes and restore system databases.

Note: Along with planning the patch testing and deployment, you need to get the required approvals as the above process might cause some downtime (i.e. during the failover actions).

Additional resources:
Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

The SQL Server and .NET Hub


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.