SQL Server

Options we have for patching SQL Server Instances — (Part 1)

Author: Basit A. Masood-Al-Farooq

In this first part of the two part article series, we will first explore the various SQL Server patch types. Next, we will explore the different options we have to patch SQL Server.

Types of patches available for SQL Server

The following are the main types of patches available for SQL Server:

Service Packs

A service pack is a rollup of several cumulative updates, and in theory, has been tested even more than cumulative updates. They are considered as the main revision level, and forms the minimum version supported for Microsoft support. According to the SQL Server Best Practices document, once the service pack has been released, you have 1 year to apply that service pack to remain at a supported level.

Cumulative Updates

A cumulative update is a rollup of several hotfixes and security patches, and has been tested as a group. They are regularly released for SQL Server, however, are not mandatory and do not form part of the support lifecycle timelines.

Security Patches/Hotfixes

These are additional patches to address the specific security/performance issue. They should only be applied if you are also experiencing or at risk of experiencing the specific problem for which they are released. That’s because they have not been tested extensively.

Risk associated with quality of patches

Recently, there have been several high-profile incidents around the quality of patches Microsoft released for SQL Servers. Some of the notable ones are:

§ SQL Server 2014 SP1 was pulled within a day of release

§ SQL Server 2012 SP1 caused some servers to go to 100% CPU

§ SQL Server 2012 SP1 introduced a data corruption bug

§ 2012 SP2 CU3 and 2014 CU5 caused some Availability Groups to stop replicating

Manual Process of Patching the SQL Server

The following section highlights the manual process of patching the instance of SQL Server.

Process for stand-alone instance of SQL Server:

The following are the two manual ways to apply patch on stand-alone SQL Server.

Interactive Mode

For stand-alone instance of SQL Server, the process to apply patch in an interactive mode is:

  1. Download the latest patch from Update Center for Microsoft SQL Server website. You can use the RSS feed on this page with Microsoft Update to automatically receive information about updates and to download the updates.
  2. Copy the patch executable to the network location.
  3. Logon to the Server where the instance of SQL Server is hosted, which you want to patch.
  4. Launch SQL Server Management Studio and backup the system databases. Once done, close the SQL Server Management Studio.
  5. Next, launch SQL Server Configuration Manager, and stop SQL Server service and SQL Server Agent service for the instance you are going to patch.
  6. Take note of all the start-up parameters of the SQL Server instance.
  7. Ensure no-one is connected to the instance of SQL Server, you are about to patch. Next, double-click the patch executable, to launch the SQL Server patch Setup installation wizard. The wizard typically includes following pages:
    1. Runs the patch update rules. Before, you continue, you must correct any reported errors (if any) on this screen. Warnings can be ignored.
    2. Accept the license terms on the License Terms page.

§ Avoid using “Send feature usage data to Microsoft” option in production environment, as this report may contain SQL Server hardware configuration and other environment specific information. For more information, please refer to Microsoft SQL Server privacy statement.

    1. Specify the features you would like to update on Select Features page.
    2. To avoid computer restart, stop the application and services, which Setup needs before performing the patch installation, on Check Files In Use page.
    3. Review and validated the features that will be updated on Ready to update page. Next, click update to start the installation process.
    4. Monitor the progress of installation on Update Progress page.
    5. After the update process completes, review the summary log file on Complete page. The summary log reveal the installation and other important notes. In case of failure or errors during installation, review this file. For more information, see View and Read SQL Server Setup Log Files.
  1. Restart the server and then apply more patches. SQL Server patch Setup installation wizard will tell you, if there were any files that are locked and if the reboot is required, post update.
  2. Finally, run consistency checks and perform other DBA administrative testing.
  3. Next, coordinate with business users and QA teams to ensure the application is performing as expected.

This process is same if you are applying SQL Server patch on multiple instances of SQL Server running on same physical/virtual server.

Quiet Mode

For stand-alone instance of SQL Server, the process to apply patch in a quiet mode is:

  1. Download the latest patch from Update Center for Microsoft SQL Server website. You can use the RSS feed on this page with Microsoft Update to automatically receive information about updates and to download the updates.
  2. Copy the patch executable to the network location.
  3. Logon to the Server where the instance of SQL Server is hosted, which you want to patch.
  4. Launch Command Prompt and type sqlcmd, to connect to default instance, and type sqlcmd-S myServerinstanceName, to connect to named instance. This launches SQLCMD session.
  5. Run Transact-SQL script to backup the system databases.
  6. Next, type EXIT, to exit the SQLCMD session.
  7. Ensure no-one is connected to the instance of SQL Server, you are about to patch, and then type the following command to install the patch non-interactive mode:
    1. Default Instance – /QUIET /INSTANCENAME=
    2. Named Instance – /QUIET /ALLINSTANCES
  8. Review the summary log inside SQL Binaries folder for any errors.
  9. Restart the server and then apply more patches.
  10. Re-establish the SQLCMD session, and run consistency checks and perform other DBA administrative testing.
  11. Once done, exit the SQLCMD session.
  12. Next, coordinate with business users and QA teams to ensure the application is performing as expected.

The quiet mode is useful if you have to apply a patch on number of instance because you can simply create a batch file and script the entire process.

Process for SQL Server clusters or instance of SQL Server that is part of Availability Group:

The following are the two manual ways to apply patches in this scenario:

Interactive Mode

The actual patch process is exactly similar to the one listed above for stand-alone instance. The only rule to follow is:

  1. RDP to passive cluster node or secondary availability group replica.
  2. Next, follow the interactive mode stand-alone instance of SQL Server patch process, to patch the instance of SQL Server.
  3. Perform failover once the install is finished.
  4. Next, create RDP session to active node or primary availability group replica, and follow the same process again.
  5. Perform failover once the install is finished.

Quiet Mode

The actual patch process is exactly similar to the one listed above for stand-alone instance. The only rule to follow is:

  1. RDP to passive cluster node or secondary availability group replica.
  2. Next, follow the quiet mode stand-alone instance of SQL Server patch process, to patch the instance of SQL Server.
  3. Perform failover once the install is finished.
  4. Next, create RDP session to active node or primary availability group replica, and follow the same process again.
  5. Perform failover once the install is finished.

Challenges we have to automate the patching

The following are the challenges we, if we would like to automate the patching:

  • How to identify the mission critical SQL Servers?
  • How to identify the SQL Servers that can be taken down in specific time windows?
  • How to identify the dependencies between SQL Servers? Such as:
    • Instances of SQL Servers that are partner in Log Shipping.
    • Instance of SQL Servers that are partner in clustering configuration.
    • Instances of SQL Servers that are partner in Database Mirroring.
    • Instances of SQL Servers that are partner in Availability Group.
  • How to identify instances of SQL Servers that have automatic failover mechanism. For such SQL Server instances, we should patch secondary node first and active node after?
  • Review SQL Server Agent jobs and identify jobs that could take longer and can clash with maintenance window?
  • Review the sys.configurations table on every SQL Server instance to patch, to identify what pending changes will take effect after the SQL Server instance will reboot after patching?
  • How we can devise the automated communication plan that will reach target audiences?
  • How we can ensure the relevant testing has been performed? Such as, smoke test?

Facebooktwittergoogle_plusredditpinterestlinkedinmail