Detecting Cluster Group Stacking using PowerShell


To Clarify, when I say stacking, all I mean is to find those cluster groups that are not running on the preferred owners i.e. those that are stacked on a different node than the preferred owner.

After the monthly patching cycle completes, we used to spend a considerable amount of time figuring out if all the clustered SQL*Server instances were running on their preferred owners or not. During the latest cycle of patch deployment, I was hard pressed for time and wanted to see if I could put something together, quick and dirty, to display the information easily.

Basically, what I did was improve an old script that I had written back in Jan, 2012; which got me the preferred owner names of a given cluster group. I made some changes to that and started building around it. The script presented below is essentially a simple looped re-write of the original. The script works in the following way:

  1. Connect to a cluster and get a list of nodes that belong to it.
  2. For each node in the cluster check which groups are available on a given node.
  3. For each cluster group on the node get the preferred node details and check if the current node is its preferred node.
Function Search-ClusterNodesForStacking
{
   <#
		.SYNOPSIS
		This script provides options for an administrator to check if any cluster
		services are stacked i.e. not running on their preferred nodes.

		.DESCRIPTION
			 The script works in the following way:
			 1. Connect to a cluster and get a list of nodes that belong to it.

			 2. For each node in the cluster check which services are available on a
				given node.

			 3. For each clustered service on the node, check if the current node is
				its preferred node.

		.PARAMETER ClusterName
			 Name of the Cluster which needs to be validated.

			 Required                         true
			 Position                         named
			 Default value
			 Accept pipeline input            false
			 Accept wildcard characters       false

		.EXAMPLE
			Search-ClusterNodesForStacking -ClusterName SKYNETCL
			------------------------------------------------------------
			ClusterName: SKYNETCL
			------------------------------------------------------------
					NodeName:  SKYNETA
					NodeState:  ONLINE
							ActiveGroup: Available Storage
							GroupState: OFFLINE
							Preferred Node:<-StackingDetected
							PhysicalNode:  SKYNETA
							ActiveGroupName:  Available Storage
							ActiveGroupDescription:
							PreferredNode:
							ActiveGroup: SKYSQL014
							GroupState: ONLINE
							Preferred Node: SKYNETA <- Good
							ActiveGroup: SKYSQL015
							GroupState: ONLINE
							Preferred Node:SKYNETB<-StackingDetected
							PhysicalNode:  SKYNETA
							ActiveGroupName:  SKYSQL015
							ActiveGroupDescription:
							PreferredNode: SKYNETB
			------------------------------------------------------------
					NodeName:  SKYNETB
					NodeState:  ONLINE
							ActiveGroup: Cluster Group
							GroupState: ONLINE
							Preferred Node:SKYNETA<-StackingDetected
							PhysicalNode:  SKYNETB
							ActiveGroupName:  Cluster Group
							ActiveGroupDescription:
							PreferredNode: SKYNETA
			------------------------------------------------------------
					NodeName:  SKYNETC
					NodeState:  ONLINE
							ActiveGroup: SKYSQL017
							GroupState: ONLINE
							Preferred Node: SKYNETC <- Good
							ActiveGroup: SKYSQL018
							GroupState: ONLINE
							Preferred Node: SKYNETC <- Good
							ActiveGroup: SKYSQL019
							GroupState: ONLINE
							Preferred Node: SKYNETC <- Good
							ActiveGroup: SKYSQL016
							GroupState: ONLINE
							Preferred Node: SKYNETC <- Good
			------------------------------------------------------------

		.NOTES
			 Since, the function shows the information on screen we will not accept
			 pipeline input.
   #>
	[CmdletBinding()]
	param(
		[Parameter(Mandatory = $true, HelpMessage="Enter cluster name which needs to be validated.")]
		[ValidateNotNullOrEmpty()]
		[string]$ClusterName
	)
	BEGIN
	{
		Function Get-ClusterState
		{
			param(
				[int]$state = $( Throw "Please specify cluster state.")
			)
			$getState = "";
			switch($state){
				-1 { $getState = "STATE_UNKNOWN"}
				0 { $getState = "ONLINE"}
				1 { $getState = "OFFLINE"}
				2 { $getState = "FAILED"}
				3 { $getState = "PARTIAL_ONLINE"}
				4 { $getState = "PENDING"}
				default { $getState = "ERROR GETTING STATE"}
			}
			$getState
		}
	}
	PROCESS
	{
		[string] $virtualClusName = "";
		[boolean] $bStacking, $bFound = ($False, $False);
		[string] $sPrefNode = "";

		#Get the cluster nodes and name.
		$Cluster = Get-WmiObject -namespace "Root\MSCluster" -Class "MSCluster_Cluster" -computerName $ClusterName -Authentication "PacketPrivacy" -Impersonation "Impersonate";
		$virtualClusName = $Cluster.Name.ToString();
		$clusterNodes = Get-WmiObject -namespace "Root\MSCluster" -query "Select * from MSCluster_Node" -computerName "$($virtualClusName)" -Authentication "PacketPrivacy" -Impersonation "Impersonate";
		$clusNodeNames = $clusterNodes | Select-Object Name, Description, @{Name="ClusState"; Expression={Get-ClusterState $_.State}}

		Write-Host ("-" * 60) -ForegroundColor Yellow -BackgroundColor Black
		Write-Host "ClusterName: $($virtualClusName)" -ForegroundColor Green
		Write-Host ("-" * 60) -ForegroundColor Yellow -BackgroundColor Black

		#Loop through the collection of physical cluster nodes
		$bStacking = $False
		Foreach ($node in $clusNodeNames)
		{

			Write-Host "`tNodeName: " $node.Name -ForegroundColor Yellow
			Write-Host "`tNodeState: " $node.ClusState -ForegroundColor Yellow

			#Get the Active Group for each node
			$qryStr = "ASSOCIATORS OF {MSCluster_Node='" + $node.Name + "'} WHERE AssocClass=MSCluster_NodeToActiveGroup"
			$clusNodeAssoc = Get-WmiObject -namespace "Root\MSCluster" -query $qryStr -computerName $virtualClusName -Authentication "PacketPrivacy" -Impersonation "Impersonate"
			$clusGroupNames = $clusNodeAssoc | Select-Object Name, Description, @{Name="ClusState"; Expression={Get-ClusterState $_.State}}

			#Loop through each Active group
			Foreach ($group in $clusGroupNames)
			{
				If($group.Name -ne $null)
				{
					If ( ($group.Description -ine "") -or ($group.Description -eq $null)){
						Write-Host "`t`tActiveGroup: $($group.Name)" -ForegroundColor Magenta
						Write-Host "`t`tDescription: $($group.Description)" -ForegroundColor Magenta
					} Else{
						Write-Host "`t`tActiveGroup: $($group.Name)" -ForegroundColor Magenta
					}
					Write-Host "`t`tGroupState: $($group.ClusState)" -ForegroundColor Magenta

					#Get the preferred node for this Active Group.
					#If there isn't one we just skip this because we can't detect stacking.
					$qryStr = "ASSOCIATORS OF {MSCluster_ResourceGroup='" + $group.Name + "'} WHERE AssocClass=MSCluster_ResourceGroupToPreferredNode"
					$clusGroupAssoc = Get-WmiObject -namespace "Root\MSCluster" -query $qryStr -computerName $virtualClusName -Authentication "PacketPrivacy" -Impersonation "Impersonate"
					$clusPrefNodes = $clusGroupAssoc | Select-Object Name, Description, @{Name="ClusState"; Expression={Get-ClusterState $_.State}} -First 1

					#Loop through this collection to determine if this Active Group is on a preferred node
					Foreach ($prefNode in $clusPrefNodes){
						if($prefNode -ne $null)
						{
							$sPrefNode = [string]$prefNode.Name
							If ([string]$prefNode.Name.ToUpper() -eq [string]$node.Name.ToUpper()){
								#$sPrefNode = [string]$prefNode.Name
								$result = "`t`tPreferred Node: " + $sPrefNode + " <- Good"
								Write-Host $result -ForegroundColor Green
								$bFound = $True
							}else{
								$bFound = $False
							}
						}
					}

					If ((-not $bFound) -and ($clusGroupNames.Count -ine 0)){
						$stckDet = "`t`tPreferred Node:" + $sPrefNode + "<-StackingDetected"
						Write-Host $stckDet -ForegroundColor Red
						$bStacking = $True
						Write-Host "`t`tPhysicalNode: " $node.Name  -ForegroundColor Red
						Write-Host "`t`tActiveGroupName: " $group.Name -ForegroundColor Red
						Write-Host "`t`tActiveGroupDescription: "  $group.Description -ForegroundColor Red
						Write-Host "`t`tPreferredNode:" $sPrefNode -ForegroundColor Red

					}
					$bFound = $False
				}else{
					Write-Host "`t`tActiveGroup: Unable to get group name" -ForegroundColor Cyan
				}
			}
			Write-Host ("-" * 60) -ForegroundColor Yellow -BackgroundColor Black
		}
	}
	END
	{
	}
}

As always, feedback and comments are welcome.

Advertisements
About

By profession, I’m a SQL Server Database Administrator. I love to poke my nose into different corners and see how stuff looks in there. I keep looking for new things to do as my mind refuses to settle on one topic.

Tagged with: , , , , , , , , ,
Posted in PowerShell
3 comments on “Detecting Cluster Group Stacking using PowerShell
  1. Neil GOrdon says:

    That’s a very clever and useful script. I’m trying to add one more step., Move the group (i.e. a sql server instance) to the preferred mode when it is not on it. I have tried this (amongst other things):
    $clusGroupAssoc.MoveToNewNode($sPrefnode) but I get syntax errors. How would you do it?

    Thanks for any help you can provide.

    • SqlChow says:

      Sorry, for getting back so late on this. Could you kindly post the error you are getting?
      Also, the “MoveToNewNode” method expects two parameters NodeName and Timeout. NodeName is a string and timeout is an int. So, the method call would look like:
      $clusGroupAssoc.MoveToNewNode($sPrefnode, 120).

      • Neil GOrdon says:

        Thanks for your reply. I eventually got my script working with the MoveToNewNode method.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: