Updating a SQL Cluster using System Center 2012 Configuration Manager and System Center Orchestrator (System Center 2012 Updated)
Current Runbooks and recorded MMS session can be found here:
I have been asked many times about updating my cluster updating series of Runbooks to work with the System Center 2012 suite, to provide the scripts, or to provide an export of the Runbooks. In this blog posting I will do all of this. Before getting started -
The Disclaimer: Everything posted in this blog has only been tested in my lab environments, is intended for demonstration / educational purposes, is most probably not appropriate for production use as is. Neither I nor anyone at Microsoft can provide support for the content posted here.
Disclaimer aside – these Runbooks have only been tested in my lab. These Runbooks while not only a great example of the power of Orchestrator but also of some core Orchstration concepts should be treated as examples from which to work from. Chances are if they were slammed into your environment without consideration or configuration they will not work properly. I will include in this blog some key configuration steps, however ultimately there may be an item or items unique to each individual environment that is not accounted for and will have to be evaluated ad hock. Additionally as you will see, being a demonstration series of Runbooks I have left out any error control and many other Runbook best practices. I’ve wanted to focus in on just the activities taking part in the cluster patching process. If working from this sample in a production environment you would without doubt want to include common error handling etc.
The 2012 Cluster Update Runbooks –
I have not simply ported the previously posted Runbooks to be used with the 2012 IP. This series of Runbooks has been reengineered to work more optimally with a cluster consisting of more than 2 nodes. Foundationally however it is very similar to the examples posted in my previous blog and thus I will refer you to that blog for a deep dive into the activities and a recorded demonstration.
Deep Dive Post - https://blogs.technet.com/b/neilp/archive/2012/02/10/updating-a-sql-cluster-using-system-center-configuration-manager-and-system-center-orchestrator.aspx
Pre-Configuration –
The following items must be secured before importing and executing these Runbooks.
- A SQL Cluster – this series of Runbooks would require some modification to work against anything other than a SQL Cluster.
- Configuration Manager 2012 with a configured Software update Group and a saved Template.
- Configuration Manager Agents on all Cluster nodes.
- Operations Manager 2012 with agents on all cluster nodes.
- Orchestrator with the System Center 2012 Integration Packs deployed.
- Connections from within the Orchestrator Designer to both CM and Ops. Manager.
- WinRM Configured on all nodes of the cluster (for remote PowerShell script execution) - https://msdn.microsoft.com/en-us/library/windows/desktop/aa384372(v=vs.85).aspx
Importing and Configuring the Runbooks –
The export can be found here – https://gallery.technet.microsoft.com/Patching-a-SQL-Cluster-8b9bd29a
Once Imported the following variables will need to be configured.
- From Email Address – this is the address that a notification email will be sent from.
- Password – this is the password that will be used when moving the cluster state. Additionally this could potentially be used as alternate execution credentials on any activities that would require these.
- SMTP Server – the SMTP Server name that will send the notification email.
- Update Group – the name of the CM Update Group to be used during Update Deployment. Alternatively this can be configured in the Deploy Software Updates activity using the wizard UI.
- Update Template – the name of the update template that will be used during Update Deployment. Alternatively this can be configured in the Deploy Software Updates activity using the wizard UI.
- User Name – this will be used when moving the cluster state. Additionally this could potentially be used as alternate execution credentials on any activities that would require these.
Once the variables have been configured each activity that is using System Center 2012 IP will need to be configured with the appropriate connection. See the included screen shots for each of these.
Screen Shots with brief descriptions and configuration flags (click on each for a better view) –
The main Runbook is relatively unchanged from the previous as detailed in the deep dive.
Runbook 2 – Configure Updates will require two activities to be configured with a connection to Configuration Manager. This Runbook is creating a collection and deploying software updates to the collection. Note that I am using the name of the cluster as the name of the collection. Note also that while I have added variables to handle the Deploy Software Updates activity, it may be easier to manually configure this activity using the provided GUI.
3 – Detect Cluster State – here we are determining the active and non-active nodes of the SQL cluster and storing these in two variables / as published data. Note that if there are more than two nodes in the cluster the Non Active Node data will be a comma delimited string.
4 – Update Non Active – this Runbook is not detailed in the Deep Dive blog posting. I’ve added this to better handle clusters with more than two nodes. Quite simply this trims the comma delimited string of non-active nodes and one at a time passes this data to the actual patching Runbook. Doing this ensures that one node at a time is updated. This also demonstrates one method by which to act against each ‘member’ of a comma delimited string.
5 – Apply Updates handles the actual application of updates to all nodes. This Runbook will be executed once for every node found in the cluster. There are two activities that will need to be configured with a connection to Configuration Manager. Note that two additional Runbooks are called from within Apply Updates. Also, not noted in the screen shot, there is a delay of four minute between refreshing the machine policy and running the Software Updates Deployment Evaluation Cycle. I found this appropriate for my lab; you may find this can be lowered. Better yet some data point would be introduced for verification that the Runbook is safe to continue.
5.1 and 5.2 -These two Runbooks are mostly identical, they start and stop maintenance mode for the server in Operations Manager and will both require the configuration of an Operations Manager connection.
.
6 - Move Cluster - Validates that the cluster service is running on a random non active node, and then moves cluster activity from the active node to the selected non active node.
7 – Clean Up – Deletes the collection, Update Deployment, and then sends a notification email. This will require a connection to Configuration Manager and also SMTP. The SMTP connection criteria is established, only the variables need to be filled out.
Closing - Enjoy the Runbooks.