It is very important to regularly apply patches to the SQL Server instance using latest service packs or Cumulative Updates which contains important fixes specific to the SQL Server instances and applications. There needs good planning before applying the service pack or cumulative update to a SQL Server instance and also a series of steps need to be followed for applying the patches. Missing any of the steps may sometimes result in serious problems. Most often or not the installation of the patches completes successfully without any problems, but when it fails we should be in a position to be able to roll back the changes. In this article we will see the steps that are required to be followed before applying a service pack or Cumulative update or a hotfix to a particular SQL Server Cluster instance. Below are some of the important steps which are to be performed while applying service pack or cumulative update or hotfix for critical SQL Server instance on clustered instance. This is more often or not applicable to the standalone SQL Server instances as well. Also we will look into steps to verify or validate that the patches are installed properly and SQL instance is working fine after the patch installation.
Steps to Install patches to SQL Server Clustered Instance
– Copy the Service Pack or Cumulative Update or Hotfix to all the cluster nodes to which SQL Server can failover to.
– Run the patch by choosing Run-as-Administrator option on passive node. If there are multiple passive nodes, then start by running on one of the passive node.
– Follow the patch install wizard and start the installations and monitor for its successful completion.
– Reboot the node where the patch has been successfully applied.
– Stop all the applications from connecting to the SQL Server instance.
– Failover the SQL Server instance on to the node which has been patched.
– Test the applications to make sure there are no issues.
– Run the patch by choosing Run-as-Administrator option on the new passive node. Repeat the same on each remaining passive nodes.
– Reboot the nodes after patch has been applied on each node.
– Test the application.
Steps to Verify or Validate the installation of Service Pack or CU or hotfix for SQL Server
– Open cluster manager and verify that all the SQL Server and SQL Agent cluster resources, Disk resources, Network Name and IP address resources are online.
– Connect to SQL Server instance from SQL Server Management Studio and check the version and build number to ensure that the latest patch version is reflected.
– Check SQL Server error log to make sure there are no errors in errorlog.
– Check Event Viewer on all cluster nodes to make sure there are no errors after patch installation.
– Check the SQL Server patch installation log file to make sure there are no errors reported.
– Test the applications throughly.
This is applicable on below versions of SQL Server
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.