Automating patching of Microsoft® SQL Server 2012 and 2014 in Windows clusters

You can use Server Automation Tasks in an Automation Plan to automate patching of SQL Server 2012 or 2014 in a Microsoft® Windows® cluster, with minimal impact on cluster availability. The automated patching is done in phases, one phase per node, and at least one node remains operational at all times during the patching. Note that this cluster patching solution patches SQL Server and not the underlying Microsoft® Windows® operating system.

Before you begin

  • The Windows cluster scripts must be installed on each node in the cluster. Use Server Automation Task ID 110 to download the scripts to each node in the cluster.
  • This cluster patching solution is supported only on Microsoft® Windows® 2008 Server Release 2 or newer.
  • This cluster patching solution runs only on Microsoft® Windows® Server clusters that have the Windows Failover Cluster Module feature installed.

About this task

You automate the patching process using an Automation Plan. To automate the patching, you can use Server Automation sample plan ID 351, which is designed to automate patching of a three node cluster. You can copy this plan and modify the copy for a cluster with a different number of nodes, if necessary. For information about the sample plan, see Sample Plan: Microsoft SQL Server 2012 / 2014 Cluster - Middleware patching. To create a new sample plan to automate the patching, you must add 7 steps to the plan for each separate node in the cluster. These 7 steps comprise a phase in the patching process during which a node is patched.

Complete the following procedure to create a new plan to automate the patching.

Procedure

  1. Create a new Automation Plan, adding Server Automation Task ID 138 Pre Patching Task For Non Hyper-V Clustered Microsoft Servers (Version 2008 R2 onwards) as the first step. When you are taking action, target the first node in the cluster.
  2. Add Server Automation Task ID 340 Make node unavailable as possible owner of resources in cluster as the second step in the plan. When you are taking action, target the first node in the cluster.
  3. Add Server Automation Task ID 115 Resume Node (Windows 2008-2012) as the third step in the plan. When you are taking action, target the first node in the cluster.
  4. Add a Fixlet, Task, or Baseline to patch SQL Server as the fourth step in the plan. When you are taking action, target the first node in the cluster.
  5. Add Server Automation Task ID 126 Restart Endpoint and Wait for Restart to Complete as the fifth step in the plan. When you are taking action, target the first node in the cluster.
  6. Add Server Automation Task ID 341 Make node available as possible owner of resources in cluster as the sixth step in the plan. When you are taking action, target the first node in the cluster.
  7. Add Server Automation Task ID 129 Post Patching task for Microsoft Server Clusters(Server 2008 R2 onwards including Hyper-V Clusters) as the seventh step in the plan. When you are taking action, target the first node in the cluster.
  8. For the second node in the cluster, add 7 more steps to the plan, replicating steps 1 to 7. When you are taking action, target the second node in the cluster for each of these steps.
  9. If the cluster is a three node cluster, add another 7 replica steps of steps 1 to 7 for the third node in the cluster. When you are taking action, target the third node in the cluster for each of these steps.
  10. Click Take Action to run the plan.