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
cluster.exeis a legacy tool; on recent Windows Server versions, prefer theFailoverClustersmodule cmdlets.- Node, group and resource names above are generic examples to adapt.