Automating middleware patching of Microsoft SQL Server 2008 Release 2 in Windows clusters

You can use Server Automation Tasks to automate patching of a SQL Server 2008 Release 2 cluster, with minimal impact on the cluster availability. Unlike some of the other Server Automation cluster patching solutions, this solution patches Microsoft® SQL Server and not the underlying Microsoft Windows® operating system.

Before you begin

  • The Windows cluster control scripts must be installed on each node in the cluster. Use Server Automation Task ID 110 to install the scripts to each node in the cluster.
  • This cluster patching solution is supported only on Microsoft Windows Server 2008 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 350, 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 2008 Release 2 Cluster - Middleware patching. To create a new sample plan to automate the patching, complete the following procedure.

Procedure

  1. Create a new Automation Plan and add Task ID 342 Add cluster nodes to Administrators group as the first step. When you are taking action, target every node in your cluster.
  2. Add a second step to the plan, selecting Server Automation Task 138 Pre Patching Task for Non Hyper-V Clustered Microsoft Servers (Version 2008 R2 onwards) for the second step. When you are taking action, target the first node in the cluster.
  3. Add a third step to the plan, selecting Server Automation Task ID 340 Make node unavailable as a possible owner of resources in a cluster for the third step. When you are taking action, target the first node in the cluster.
  4. For the fourth step in the plan, add Server Automation Task ID 115 Resume Node (Windows 2008-2012). When you are taking action, target the first node in the cluster.
  5. For the fifth step in the plan, add a Fixlet, Task, or Baseline to patch SQL Server. When you are taking action, target the first node in the cluster.
  6. For the sixth step in the plan, add Server Automation Task ID 126 Restart Endpoint and Wait for Restart to Complete. When you are taking action, target the first node in the cluster.
  7. For the seventh step in the plan, add Server Automation Task ID 341 Make node available as possible owner of resources in cluster. When you are taking action, target the first node in the cluster.
  8. For the eighth step in the plan, add Server Automation Task ID 129 Post Patching task for Microsoft Server Clusters (Server 2008 R2 onwards including Hyper-V Clusters). When you are taking action, target the first node in the cluster.
  9. Add 7 more steps to the plan, identical to steps 2 to 8 for the second node in the cluster. When you are taking action to run the plan, target the second node. If you have more than two nodes in the cluster, add a further 7 steps, mirroring steps 2 to 8.
  10. Add a final step to the plan, selecting Server Automation Task ID 343 Remove cluster nodes from Administrators group for the final step. When you are taking action, target every node in the cluster.