Windows Aware updating and SQL Server 2012 (Part II) – Step by Step

At this point we already know the concepts for Windows Aware Updating (CAU), on this post we will check how to use the plug-in Windows.HotfixPlugin to install LDR updates, cumulative updates and Service Packs, and the options that provides to obtain a better control of the process. NOTE: CAU can only be used from SQL Server 2012 Service Pack 1 and up.

As an example we will install the Service Pack 1 of SQL Server 2012 o the nodes Win20121 and Win20122 (This process is pretty similar with any other cumulative update or service pack), using the graphic tool, this will allow a better and simpler visualization of the process. We are using a 3 nodes Windows Server 2012 Cluster with 2 SQL 2012 RTM instances, that can only start on Win20121 and Win20122, and we will use the self-updating mode.

To avoid a very long post some print screens that show the previous and later versions of SQL and the cluster configuration are omitted. The intent is to see the CAU process.

 

Because the update will be installed in 2 of the 3 nodes, we’ll use the following folder structure:

 

\\WIN-0M99J4ILA2E\UpdatesDemo\Root5

        DefaultHotfixConfig.xml

        \<nodo 2 name>

               \SQL2012SP1

                       \<SQLServer2012SP1Package>.exe

\<nodo 4 name>

               \SQL2012SP1

                       \<SQLServer2012SP1Package>.exe

As described on the first post, we start the Cluster-Aware updating tool.

<root>

<DefaultRules>

</DefaultRules>

  <FolderRules>

    <Folder name="SQL2012SP1">     

      <Template path="$update$" parameters=" /ACTION=PATCH /allinstances     

               /QUIET /IAcceptSQLServerLicenseTerms"/>

      <ExitConditions>

        <Success>

          <ExitCode code="0"/>

        </Success>

        <Success_RebootRequired>

          <ExitCode code="3010"/>

        </Success_RebootRequired>

          <NotApplicable>

            <ExitCode code=" -2068578302"/> <!-- ERROR_PATCH_TARGET_NOT_FOUND -->

        </NotApplicable>

          <AlreadyInstalled>

            <ExitCode code=" -2068643838"/> <!-- ERROR_PATCH_ALREADY_APPLIED -->

        </ AlreadyInstalled >

      </ExitConditions>

    </Folder>

  </FolderRules>

</root>

And click on “Configure cluster self-updating options”.

And click on “Configure cluster self-updating options”.

Then click on next.

Enable the self-updating mode, and click next.

Now we configure the automatic update execution frequency. This is particularly useful with the Microsoft.WindowsUpdatePlugin plug-in, to install the security updates. The Microsoft.HotfixPlugin

Plug-in, when used for the installation of non-security updates, requires the configuration of the folder structure and downloading the installers, that’s why might be more practical a manual and supervised. execution, although a schedule can be useful on some cases like the update of non-production environment.

Then click next.

 

Configure aspects of execution like:

  • Max execution time in minutes (StopAfter), after this all the update process is stopped.
  • Warnning time in minutes (WarmAfter), after this a warning messages is generated, this can be used, for example, to warm that the finalization of the maintainance windows is near.
  • Order on which the nodes have to be updated (NodeOrder).
  • The plug-in to use (CaaPluginName), on this case Microsoft.HotfixPlugin
  • Additional parameters for the plug-in Microsoft.HotfixPlugin.
  • Other values, that can be consulted in https://technet.microsoft.com/en-us/library/jj134224.aspx

Then we click next.

Establish the options for the Microsoft.HotfixPlugin like the file share root, that host the folder structure and the updates to install

NOTE: for more information on the options for the Microsoft.HotfixPlugin see https://technet.microsoft.com/en-us/library/jj134213.aspx

We can see a summary of the configured options (we can use this on the option describe don the first part of the post to check the updates that will be installed during the execution), now we click on “Apply” to finish the configuration of CAU

Click Close.

Once we have configures the CUA, we can start its execution clicking on “Apply updates to this cluster”, this will start the wizard.

Click Next.

Here we can visualize the PowerShell command that you can use to start the update. We can also see the updates that will be installed clicking on “Preview the updates that will be applied to the cluster nodes”.

Before we start, and to validate the Service Pack will only be installed on the specified nodes on the folder structure let’s click on “Preview the updates that will be applied to the cluster nodes”.

You can see that the Service Pack 1 will only be installed on the node Win20121 and Win20122, as you establish on the folder structure. Click Close.

To start the update process click on “Update”.

Once the process is started click in Close.

This allows us to see the progress of the updates until it ends.

 

The services failover are not log on this window, however, they are executed after putting a node on maintenance mode. After the process ends, the instances will be put in its original state.

We need to take into consideration that this process is different to the recommended on the document SQL Server failover cluster rolling patch and service pack process available on https://support.microsoft.com/kb/958734, in that the half on the nodes been updated are not automatically removed as possible owners of the SQL Server resources, as also the moment on which the failover of the instances to the updated nodes is done. This is a functionality that can help us to facilitate the process and if configured correctly, using the options of the Microsoft.HotfixPlugin plug-in we can get a similar behavior to the recommendations on the document on many aspects, so we invite you to try it and find the configuration that better suit your needs.

There are a few considerations to take into consideration when using CAU on Always ON Environments with High Availability Groups, and also many other updates scenarios, that’s why we recommend to read the document Patching SQL Server Failover Cluster Instances with Cluster-Aware Updating (CAU) available on https://www.youtube.com/watch?v=XhVbLgf3rqE

I hope this two post son this series help you to understand how to use this new functionality on Windows Server 2012.

 

 

Other Recommended Readings:

Cluster-Aware Updating Overview

https://technet.microsoft.com/en-us/library/hh831694.aspx

 

Requirements and Best Practices for Cluster-Aware Updating

https://technet.microsoft.com/en-us/library/jj134234.aspx