LLI Wiki — My Digital Knowledge Base

Useful PowerShell commands for a Windows failover cluster and SQL Server

Cheat sheet of PowerShell and cluster.exe commands to manage groups, resources, disks and logs of a Windows failover cluster hosting SQL Server.

A cheat sheet of useful commands for administering a Windows Failover Cluster hosting SQL Server: installing features, moving groups, resources, disk space and cluster logs.

Install the Windows features

Install-WindowsFeature Net-Framework-Core -Source C:\install\sxs\

Install-WindowsFeature Application-Server,Failover-Clustering,RSAT-Clustering-MGMT -IncludeManagementTools
Add-WindowsFeature AS-Dist-Transaction,AS-Incoming-Trans,AS-Outgoing-Trans

Install a clustered SQL Server instance

On the first node, run the setup program with the InstallFailoverCluster action, then on each passive node with the AddNode action:

Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster
Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode

If SQL Server Configuration Manager cannot connect to the WMI provider (error 0x80041010, invalid class), recompile the provider MOF file:

mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

The path depends on the installed SQL Server version (100 here is SQL Server 2008 / 2008 R2).

Move cluster groups

With the legacy cluster.exe utility:

cluster group "cluster group" /properties
cluster group "cluster group" /move:nodename

With the FailoverClusters PowerShell module:

Import-Module FailoverClusters
Get-Command -Module FailoverClusters
Get-ClusterGroup

Move all active groups from one node to another:

Get-ClusterNode node01 | Get-ClusterGroup | Move-ClusterGroup -Node node02

# Variant: filter on the owner node and wait indefinitely for the move to complete
Get-ClusterGroup | Where-Object { $_.OwnerNode -eq "node01" } | Move-ClusterGroup -Node "node02" -Wait 0

Move a named group (for example an availability group):

Move-ClusterGroup -Name 'ag_app_001' -Node 'node01'
Move-ClusterGroup -Name 'Cluster Group' -Node 'node01'

Get-ClusterGroup shows the owner node and state of each group:

Name              OwnerNode State
----              --------- -----
ag_app_001        node01    Online
ag_app_002        node01    Online
Available Storage node01    Offline
Cluster Group     node01    Online

Cluster resources

# List resources and read their parameters
Get-ClusterResource
Get-ClusterResource "Cluster IP Address" | Get-ClusterParameter

# Possible owner nodes of a resource
Get-ClusterResource "SQL Server Agent" | Get-ClusterOwnerNode

# Stop the SQL Server resource
Stop-ClusterResource "SQL Server"

# Change the DNS record TTL of a network name resource
Get-ClusterResource cluster1FS | Set-ClusterParameter HostRecordTTL 300

Set several parameters of an IP address resource at once:

$res = Get-ClusterResource "IP Address"
$param1 = New-Object Microsoft.FailoverClusters.PowerShell.ClusterParameter $res,Address,192.0.2.10
$param2 = New-Object Microsoft.FailoverClusters.PowerShell.ClusterParameter $res,SubnetMask,255.255.255.0
$params = $param1,$param2
$params | Set-ClusterParameter

A few cluster.exe equivalents:

cluster /List
cluster.exe "clustername" group "SQL Server (MSSQLSERVER)" /status
cluster /cluster:%cluster% res /prop
cluster /cluster:%cluster% network "%PublicNetwork%" /priv
cluster restype "%resource_name%" /delete /type

Cluster volume size and free space

Size and free space of NTFS volumes (excluding C: and D:) on several nodes, as CSV:

wmic /node:"node01","node02" path Win32_LogicalDisk WHERE "FileSystem='NTFS' AND Name != 'C:' AND Name != 'D:'" GET SystemName,Name,Size,FreeSpace,VolumeName /format:csv

For a longer list of nodes, put them in a file:

echo node01 > clusternodes.txt & echo node02 >> clusternodes.txt & wmic /node:@clusternodes.txt path Win32_LogicalDisk WHERE "FileSystem='NTFS' AND Name != 'C:' AND Name != 'D:'" GET SystemName,Name,Size,FreeSpace,VolumeName

Local PowerShell equivalent:

Get-WmiObject Win32_LogicalDisk

Directory size in PowerShell

function directory-summary($dir=".") {
  Get-ChildItem $dir |
    ForEach-Object { $f = $_ ;
        Get-ChildItem -Recurse $_.FullName |
           Measure-Object -Property Length -Sum |
             Select-Object @{Name="Name"; Expression={$f}},Sum }
}

Size in MB of the items in the current directory:

Get-ChildItem | Select-Object Name, @{Name="MegaBytes";Expression={$_.Length / 1MB}}

Filter files modified during the last 7 days:

Get-ChildItem -Recurse | Where-Object { (-not $_.PSIsContainer) -and ($_.LastWriteTime -ge [DateTime]::Today.AddDays(-7)) }

Script debugging

Set-PSDebug -Trace 2 -Step
Set-PSDebug -Trace 0

Cluster debug log

Since Windows Server 2008, the CLUSTER.LOG text file of Windows Server 2003 no longer exists: the cluster log is based on Event Tracing for Windows (ETW) and must be generated as text on demand. This is done with cluster.exe or, from Windows Server 2008 R2, with the PowerShell cmdlets.

cluster.exe PowerShell Effect
cluster log /gen Get-ClusterLog Generates the log
/COPY:"directory" -Destination Copies the logs of all nodes to a single directory
/SPAN:min -TimeSpan Keeps only the last X minutes
/NODE:"node-name" -Node Targets one node, useful when the cluster service is down

Reference: Understanding the Cluster Debug Log in 2008 (Microsoft Ask the Core Team blog).

Notes

Mastodon LLIMastodon SocialFosstodon