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.
Suggested Patching Procedure:
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.).
Strengthen your SQL Server Administration Skills – Enroll to our Online Course!
Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
(Lifetime Access/ Live Demos / Downloadable Resources and more!)
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).
Watch our Video on How to Patch a SQL Server Failover Cluster Instance
Do not forget to subscribe to our YouTube Channel and never miss a video!
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks 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). Moreover, Artemakis teaches on Udemy, you can check his courses here.
Views: 9,486
Online Course Offer | “SQL Server 2019: What’s New – New and Enhanced Features“
Learn about the exciting new features and enhancements in SQL Server 2019.
Many live demonstrations on Windows and Linux included!
Available on: Udemy (* also available in the Udemy for Business Collection)
This site uses cookies for personalized content and the best possible experience. By continuing to browse this site, you agree to this use. Find out more.