How to install a Service Pack at a SQL Server 2012 Failover Instance - Best Practices

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:

• Analysis Services databases. By default, these are installed to C:\Program Files\Microsoft SQL Server\MSAS11.<InstanceID>\OLAP\Data\. For WOW installations, the default path is C:\ProgramFiles (x86)\Microsoft SQL Server\MSAS11.<InstanceID>\OLAP\Data\.
• Analysis Services configuration setting in the msmdsrv.ini configuration file. By default, this is located in the C:\Program Files\Microsoft SQL Server\MSAS11.<InstanceID>\OLAP\Config\ directory.
• The database that contains the Analysis Services repository. This step is required only if Analysis Services was configured to work with the Decision Support Objects (DSO) library.

-  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 the
sp_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

• x
  indicates the service pack number
• PPP
  indicates the specific platform
• LLL
  indicates the character abbreviation for the SQL Server language (e.g. LLL for
  English is ENU)

 -  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:\

• As a best practice, you should copy the Service Pack package locally at each node of the cluster.
• If that is not possible, you could choose to install the Service Pack from a network share.

    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 each
node 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:

• Logon to each node
• Navigate to folder C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log
• Open the Summary.txt file file
• Make sure each component was patched successfully (exit code should be equal to zero):

-   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: 

• You must perform the below steps for each cluster node:
• Make sure this is a passive node before running the steps.
• Go to "Programs and Features" options in Control Panel and click on "View installed updates"
• Highlight the SQL Server 2012 Service Pack 2 and click "Uninstall"
• Wait for the Uninstall Service Pack wizard to start
• Click "Next" after the update rules are verified.
• Select the features for which you need to remove the Service Pack and click "Next"
• Once the file check is completed, Click "Next"
• Verify the Summary and click "Remove"
• Wait for the process to complete
• Reboot the node

-   Repair the SQL Server 2012 failover instance:

• Repair must be run on each individual cluster nodes that is affected/damaged
• Launch the SQL Server Setup program (setup.exe) from SQL Server installation media.
• After prerequisites and system verification, the Setup program will display the SQL Server Installation Center page.
• Click Maintenance in the left-hand navigation area, and then click Repair to start the repair operation.
• If the Installation Center was launched using the start menu, you will need to provide the location of the installation media at this time.
• Setup support rule and file routines will run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
• On the Select Instance page, select the instance to repair, and then click Next to continue.
• The repair rules will run to validate the operation. To continue, click Next.
• The Ready to Repair page indicates that the operation is ready to proceed. To continue, click Repair.
• The Repair Progress page shows the status of the repair operation. The Complete page indicates that the operation is finished.
• If the repair fails, the only option left is to reinstall the 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 to
first uninstall it. You may use these steps in this case:

• Insert the SQL Server installation media. From the root folder, double-click setup.exe. To install from a network share, navigate to the root folder on the share, and then double-click Setup.exe.
• The Installation Wizard launches the SQL Server Installation Center. To remove a node to an existing failover cluster instance, click Maintenance in the left-hand pane, and then select Remove node from a SQL Server failover cluster.
• The System Configuration Checker will run a discovery operation on your computer. To continue, click OK.
• After you click install on the Setup Support Files page, the System Configuration Checker verifies the system state of your computer before Setup continues. After the check is complete, click Next to continue.
• On the Cluster Node Configuration page, use the drop-down box to specify the name of the SQL Server failover cluster instance to be modified during this Setup operation. The node to be removed is listed in the Name of this node field.
• The Ready to Remove Node page displays a tree view of options that were specified during Setup. To continue, click Remove.
• During the remove operation, the Remove Node Progress page provides status.
• The Complete page provides a link to the summary log file for the remove node operation and other important notes. To complete the SQL Server remove node, click Close.
• Reboot the node
• Reinstall the failover instance
• Restore the System & User databases
• Apply the Service Pack/Cumulative Update you were using before you tried to install SP2

References:

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 service
packs, hotfixes, cumulative updates

Best Practices for Applying Service Packs, Hotfixes
and Security Patches

Upgrade and Update of Availability Group Servers with
Minimal Downtime and Data Loss

Overview of SQL Server Servicing Installation

Un-Installing Service Pack for SQL Server

DBCC CHECKDB / Database Integrity

Microsoft® SQL Server® 2012 Best Practices Analyzer

Remove a SQL Server Failover Cluster Instance (Setup)

Add or Remove Nodes in a SQL Server Failover Cluster
(Setup)

Failover Cluster Instance Administration and
Maintenance

• Anonymous
  January 17, 2015
  Can we patch a standalone instance by stopping sql services ?

• Anonymous
  May 29, 2015
  Does the same procedure apply for a Cumulative Update?