It is very important for regularly update the SQL Server instance using latest service packs or Cumulative Updates with important fixes specific to the SQL Server instance and application. There needs some 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 about some important pre-requisite steps that needs to be followed before applying a service pack or Cumulative update or a hotfix to a particular SQL Server instance. Below are some of the important prerequisite tasks which are to be taken care before installing service pack or cumulative update or hotfix for critical SQL Server instance on standalone or clustered instance.
– Patches which may be Service Pack or Cumulative Update or Hotfix are first need to be applied on test SQL Server instance and then application needs to be tested to make sure it works fine without any problems.
– Run DBCC CheckDB on all the system and user databases and make sure there is no corruption on any of the databases.
– Backup all System and User database before applying the patches. The timing of the backups are important as all the backups(FULL, DIFF, LOG) should be stored and available for few days. Also, before hand it would be great to know how much time does it take to restore the databases in case of any problems. This can be tested on a test server to get a rough idea.
– Also backup Analysis Service databases and important configuration files. Backup Reporting Services encryption keys and important configuration files.
– Ensure there is sufficient free space in all the drives including C:\ drive on both the nodes.
– Download and keep the Service Pack or Cumulative Update or Hotfix file on all nodes of cluster and extract the contents of the file to a folder. Double check that the downloaded patch version, build, platform(x86, x64) etc.
– Make sure you have a valid domain account which has local administrator permission on all the cluster nodes.
– Coordinate with all teams(Application team, SysAdmin team, Network Team) Before starting the installation, so that application can be stopped before starting the patch installation and reboot of the servers can be performed by the System Administrator.
– Communicate to all stake holders about the estimated downtime and reboot of the servers which may cause restart of other services installed on that servers too.
This is applicable on below versions of SQL Server
SQL Server 2005
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.