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:
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.).
Learn more tips like this! – Enroll to the Online Course!
Check our online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).
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!
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).
Featured Online Courses:
- Essential SQL Server Administration Tips (new)
- The Philosophy and Fundamentals of Computer Programming (new)
- Introduction to Azure SQL Database
- 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
Related SQL Server Administration Articles:
- Essential SQL Sever Administration Tips
- How to Patch a Standalone SQL Server Instance
- The SQL Server Browser Service and UDP Port 1434
- The Maximum Number of Concurrent Connections Setting in SQL Server
- Top 10 SQL Server DBA Daily Tasks List
- There is no SQL Server Failover Cluster Available to Join
- Encrypting a SQL Server Database Backup
- Free Online Service: SQL Server Latest Service Pack Info
- Free Online Service: SQL Server Backward Compatibility Check
- MSDN Article: How to install a Service Pack at a SQL Server 2012 Failover Instance – Best Practices
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check out Artemakis’s eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). 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).