This month’s T-SQL Tuesday topic is being hosted by Mike Donnelly (B|T).  The topic of choice is a fantastic one, its simply just to teach something new.

So let’s do this.

Our current windows patching process is manual.  On a given weekend every month, we log into the inactive node of the production cluster, install the windows patches and then fail over the cluster.  Making the newly patched server the active node of the cluster.

With any type of manual process, I always look for ways to automated it and make life easier.  In this case I not only wanted to automate,  but also take this off my plate. So I spoke with the Server team, and they agreed to take over the task but only if we automated deployment of the patches . They also requested that first we ensure that the inactive node was the same server for every patching cycle.  That’s where Powershell came in, I used cmdlets  to perform the  needed actions on the cluster.

I envisioned the script working like this:

1. Prior to patching, the script would execute.
2. The script would determine the owner of an existing cluster resource.  The owner represents which server the resource is residing on.
3. If the resource owner isn’t correct, fail over the cluster automatically.  This would put the resource on the correct node of the cluster.
4. The now correct node is subsequently patched.
5. The script is called again after patching to move the active node onto the newly patched server.

Sounds straight forward right? Let’s hope so.

A quick Google search delivered a number of cmdlets for Powershell to manage a Windows Cluster.   You can find them here.  After looking at the list, I figured that I needed to look at:

• Get-ClusterGroup
• Move-ClusterGroup

Get-ClusterGroup

Microsoft tell us that this cmdlet is used to “Get information about one or more clustered services or applications (resource groups) in a failover cluster.”  Specifically, we can use this cmdlet to obtain who currently owns a particular cluster resource such as SQL Server.

Here is the basic syntax:

Get-ClusterGroup [-InputObject ] [[-Name] ] [-Cluster ] []

The output of this command will tell me the resource name, the owner node and the state of the resource, whether or not the resource is online or offline.

Move-ClusterGroup

Microsoft tells us that this cmdlet is used to “Move a clustered service or application (a resource group) from one node to another in a failover cluster.”  This is exactly what I need.  I can use this cmdlet to “move” the entire group to another node.

Here is the basic syntax:

Move-ClusterGroup [-InputObject ] [[-Name] ] [[-Node] ] [-Cluster ] [-Wait ] []

Given that I’ll have the cluster information from the Get-ClusterGroup cmdlet, I can call the move-clustergroup accordingly and tell it which node to move the cluster onto.

Development

Before getting started, it’s worth noting that if your local machine doesn’t have the failover clustering tools already installed, you’ll need to get them installed so that you can access the failoverclusters powershell module.  You can find documentation on how to add a role to your machine here.  Your mileage may vary depending on what operating system you are using.

Now that I’ve gotten my machine setup correctly with the appropriate role, I started to test out the cmdlets that I had found.   First I had to determine how to acquire the cluster information.   For purposes of this post, I am going to reference a cluster named “Cluster1″ however the example screen shots will be censored due to security reasons.  I am also interested in the “SQL Server (MSSQLSERVER)” resource group, which is the group the SQL Server cluster resources belong to.  Given those two pieces of information, this is the resulting query:

Get-ClusterGroup -Cluster "Cluster1" -Name "SQL Server (MSSQLServer)" | fl *

The “|” means that we will pipe the output of the get-clustergroup command into the format-list (fl) cmdlet.   The asterick specifies to display all the properties of the get-clustergroup output.

We can then set the output of this cmdlet to a variable like this:

$obj = get-clustergroup -cluster "Cluster1" -name "SQL Server (MSSQLSERVER)

With the output of the cmdlet into the form of a variable, we can now interrogate the variable for the name of the owner of the cluster group.    We use an IF() statement within Powershell to do the checking.  If the name of the owner is what we want, we then use the move-clustergroup cmdlet to tell the cluster to move to another node.

Given that we only have 2 node clusters, this would fail it over to the other node.  If you had multiple nodes, you can specify which node to move to by including the “-node [nodename]” switch.

#************************************************************
# Purpose: Failover cluster to ensure inactive node is correct
# Author: MorehouseJ
# Created: 4/10/2015
#***********************************************************
import-module failoverclusters
 
$obj = get-clustergroup -cluster Cluster1 -name "Cluster Group"
 
#flip 
If ($obj.ownerNode.Name -ne “server1"){
    get-clustergroup -cluster Cluster1 | move-clustergroup
}

Peer Review

Once I had a basic script constructed, I thought that it would be best to get someone much more versed in Powershell to look things over.  So I turned to my friend Mike Fal (B|T) and asked his advice.

He had some excellent pointers for me.  Namely, if I were to use parameters the script could be easily reused.  Mike also pointed that out I was making two separate calls to the Get-Clustergroup cmdlet which is a waste of resources.  I can call it once, set it to a variable (I was already doing this), and then reuse that variable where appropriate.  Mike also suggested putting in some logging so that some level of tracking was going on.

Based on Mike’s excellent suggestions, this is what the final script looks like:

#************************************************************
# Purpose: Failover cluster to ensure inactive node is correct
# Author: MorehouseJ
# Created: 4/10/2015
#************************************************************
param([string] $cluster
    ,[string] $node
    ,[string] $resource)
$obj = get-clustergroup -cluster $cluster -name $resource
 
#flip 
If ($obj.ownerNode.Name -ne $node){
    Write-Warning "Incorrect node for '$resource', failing over to $node."
    $obj | move-clustergroup -Node $node
    }

Summary

Now we have a complete script that can be easily reused, will move the nodes as needed and help to reduce the amount of manual efforts in the patching process.   This to me demonstrates the power of PowerShell (no pun intended).  I can’t wait to introduce more of these types of thing to our team.