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:
- Connect to a cluster and get a list of nodes that belong to it.
- For each node in the cluster check which groups are available on a given node.
- 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.
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.
SqlChow 
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.
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).
Thanks for your reply. I eventually got my script working with the MoveToNewNode method.