How to Patch a SQL Server Failover Cluster

This article discusses how to patch a SQL Server Failover Cluster.

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.

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.

Test Environment

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.).

Production Environment

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).

Check our Online Courses:

• SQL Server 2019: What’s New
• Entity Framework: Getting Started (Ultimate Beginners Guide)
• SQL Server Fundamentals (SQL Database for Beginners)
• How to Import and Export Data in SQL Server
• Get Started with SQL Server in 30 Minutes

Additional resources: