Best practices for updating a SQL Server 2012 Failover Instance
Part 1 – Preparing for the installation
Preparing for the Service Pack installation is the most important part. Skipping one or more of the preparations steps imposes a risk and is not a recommended action.
- Testing before deployment:
Service packs and hotfixes must be tested on a representative non-production environment prior to being deployed to production. Thus, you should perform the installation of the SQL Server 2012 Service Pack at your test environment first. Identify and resolve any issues that occur to the test environment before deploying the Service Pack to your production environment.
- Identify existing issues:
Use the SQL Server 2012 BPA tool to scan the instance you wish to update:
Microsoft® SQL Server® 2012 Best Practices Analyzer
Resolve any issues identified by this tool before proceeding.
- Validate the consistency of your databases:
Run a consistency check (DBCC CHECKDB) against every User and System database (i.e. master, model, msdb). Make sure that the check reports 0 allocation and 0 consistency errors for each database:
CHECKDB found 0 allocation errors and 0 consistency errors in database
If any allocation or consistency errors are detected for one or more of the databases, these should be repaired before the Service Pack installation takes place: DBCC CHECKDB / Database Integrity
- Backup your data:
Take a FULL backup from all the User databases (especially from the user databases that are involved in a replication topology) and of all the System databases (i.e. master, model, msdb), in case you need to roll-back the SQL Server instance to its former state after the installation finishes.
If you have installed Analysis Services, you should also backup this set of data:
- Make sure the System databases have free disk space inside their files:
If the autogrow option is not selected for the master and msdb system databases, these databases must each have at least 500 KB of free disk space. To verify that the databases have sufficient space, run thesp_spaceused system stored procedure on the master and msdb databases. If the unallocated space in either database is less than 500 KB, increase the size of the database manually or simply allow these databases to autogrow.
- Verify you have downloaded the correct Service Pack package:
Service pack package (PCU): <SQLServer2012><SPx> -KBxxxxxx-PPP-LLL.exe
- Verify there is enough free disk space at the nodes:
The recommended disk space requirements are approximately 2.5 times the size of the package to install, download, and extract the package. As SQL Server 2012 SP2 is almost 1016 MB, the recommended free disk space is 2540 MB. After installing a service pack, you can remove the downloaded package. Any temporary files are removed automatically.
- Make the Service Pack package available to each node:\
Note: Running the Service Pack package from a local disk of the node is the safest choice
- Use an account with adequate permissions to run the setup:
Use a domain account that is a local administrator at all the nodes of the Windows cluster. If you install SQL Server from a remote share, you must verify that this domain account has read and execute permissions on the remote share.
- Stop Services and Applications:
To avoid a possible restart of the system, stop all applications and services that make connections to the instances of SQL Server that are being upgraded, before installing SQL Server 2012 updates. These include SQL Server Management Studio, SQL Server Data Tools (SSDT). For a SQL Server failover instance, this means that you should failover the SQL Server cluster group (Role) to another node before updating the current node.
Part 2 – Performing the installation
Having made the proper preparations, the Service Pack installation should be a relatively simple task.
- Execute the SQL Server 2012 Service Pack package at the first passive node:
Double click the SQL Server 2012 Service Pack package and follow the instructions of the setup
On the Choose Directory for Extracted Files dialog, the default directory path is prepopulated:
<Current drive>\<Currentworking folder>\<Package name>
Click Browse if you wish to change the directory path for the extracted files.
The package decompresses to the specified installation path and would be left behind in the selected directory path after the installation completes. A user will be able to use the binaries in the extracted folder for future Installations. As this folder may prove useful for future maintenance operations, do not delete it if possible.
- After the setup finishes successfully, reboot this node as a best practice, even if the setup reports that a reboot is not required.
- Then repeat the same procedure at the next passive node. Failover the SQL Server cluster groups (Roles) as needed between the nodes, so that each time you patch a passive node. Don’t forget to reboot eachnode after the setup finishes successfully.
- Start the Services and Applications you had shut down as part of the installation’s preparation procedure.
Part 3 – Verifying the installation
Having finished the installation part, it is now time to verify that the Service Pack was installed and the SQL Server components were all updated successfully.
- Verify that the SQL Server services are online.
- Verify that the SQL Server build has been updated successfully:
The reported build for an instance patched with SQL Server 2012 SP2 should be 11.0.5058.0
- You could additionally examine the SQL Server setup logs of each node to make sure that all components were patched successfully:
- Test your applications to make sure that their functionality was not broken by the SP installation.
Part 4 – Repair & Rollback options
If the installation of SQL Server 2012 SP2 broke the functionality of your applications or if there is some other need to roll back the recently installed service pack, you may use these options:
- Uninstall the Service Pack:
- Repair the SQL Server 2012 failover instance:
- Reinstall the SQL Server 2012 failover instance:
If the SQL Server installation has been severely corrupted or if there occurs a similar catastrophic error, you may need to reinstall the SQL Server 2012 failover instance. But to reinstall the instance, you need tofirst uninstall it. You may use these steps in this case:
Microsoft® SQL Server® 2012 Service Pack 2 (SP2)
SQL Server 2012 SP2 Release Notes
Install SQL Server 2012 servicing updates
Best Practices for installing SQL Server servicepacks, hotfixes, cumulative updates
Best Practices for Applying Service Packs, Hotfixesand Security Patches
Upgrade and Update of Availability Group Servers withMinimal Downtime and Data Loss
Overview of SQL Server Servicing Installation
Un-Installing Service Pack for SQL Server
DBCC CHECKDB / Database Integrity
Remove a SQL Server Failover Cluster Instance (Setup)
Add or Remove Nodes in a SQL Server Failover Cluster(Setup)
Failover Cluster Instance Administration andMaintenance