Move witness disk in SQL cluster

I ran into the issue where CAU (Cluster Aware Updating) was working as intended, but on my SQL cluster, this meant that the SQL cluster disks were still available in the Failover Cluster, but the witness disk was active on the non-active node. Which meant that if I did queries on the cluster, it would say the owner node is the one with the witness disk and I’m not able to query the SQL related things, because there is no SQL service on the owner node.

This blog from IP loging pointed me in the direction where I wanted to go. But I wanted a script that did this automatically for me, because I don’t want to be confronted with inventory scripts that didn’t work because the SQL cluster couldn’t be queried.

At first I need to know which is the cluster node that has the SQL server disks active and is running SQL. This can be achieved by running this command:

$SQLClusterGroup = Get-ClusterGroup | Where Name -EQ "SQL Server (MSSQLSERVER)"

After that I need to get the groups which aren’t in the SQL cluster:

$ClusterGroups = Get-ClusterGroup | Where Name -NE "SQL Server (MSSQLSERVER)"

Then I need to check if the Owner Node of the Cluster group is the same as the SQL Cluster group Owner Node.

If they are not the same, the owner node need to be transferred to the owner node of the SQL cluster.

Foreach($ClusterGroup in $ClusterGroups)
{
    If($ClusterGroup.OwnerNode -ne $SQLClusterGroup.OwnerNode)
    {
        Write-Host ("Current SQL cluster node is {0}, whlie the node for cluster group {1} is {2}. Moving the cluster group to the active node" -f $SQLClusterGroup.OwnerNode, $ClusterGroup.Name, $ClusterGroup.OwnerNode)
        Move-ClusterGroup $ClusterGroup.Name -Node $SQLClusterGroup.OwnerNode
    }
    Else
    {
        Write-Host ("Current SQL cluster node is {0}, which is the same as the node for the cluster group '{1}'. No changes are made" -f $SQLClusterGroup.OwnerNode, $ClusterGroup.Name)
    }
}

Since I’m also transcribing I want output to be shown, so it will be in the log. Now I’ve got my script, but need to schedule it. In this case I want it to be a clustered scheduled task, so only 1 of the nodes will run the script. For this I need to use the register-clusteredscheduledtask function, so it’ll be created at cluster level

$action = New-ScheduledTaskAction -Execute MoveClusterGroupsToActiveNode.cmd -WorkingDirectory C:\Scripts
$trigger = New-ScheduledTaskTrigger -At 3:30 -Daily
Register-ClusteredScheduledTask -Cluster CLUSTERNAME -TaskName MoveClusterGroupsToActiveNode -TaskType AnyNode -Action $action -Trigger $trigger

Replace the text “CLUSTERNAME” to the name of the cluster where you want this task scheduled. Change the time, frequency and working directory to meet your needs.

You can download both scripts here

If you like this script and want to support me so I can create and share more scripts, please consider donating to me through PayPal.