Collection of Powershell clustering commands

Here are a collection of Powershell Windows clustering commands that I’ve found to be quite useful:

Start by opening a Powershell console under Administrator rights and running

import-module failoverclusters

And then to see the cluster resources on a cluster named SQLCLUSTER run

get-clusterresource -cluster sqlcluster

pscluster1

With the file share witness we have a node and file share majority quorum configuration. Let’s see where the file share is located:

get-clusterresource -cluster sqlcluster | where-object {$_.ResourceType -like “File Share Witness”} | get-clusterparameter

pscluster2

Visually correlating the virtual server and instance names to form a server\instance name pair is quite difficult. Let’s use this to see if we can do this with script:

get-clusterresource -cluster sqlcluster | where-object {$_.ResourceType -like “SQL Server”} | get-clusterparameter -cluster sqlcluster VirtualServerName,InstanceName

pscluster3

This is better but we can get the name pair by grouping on the first column:

get-clusterresource -cluster sqlcluster | where-object {$_.ResourceType -like “SQL Server”} | get-clusterparameter -cluster sqlcluster VirtualServerName,InstanceName | group-object ClusterObject | select-object @{Name = “SQLInstance”;Expression = {[string]::join(“\”,($_.Group | select-object -expandproperty Value))}}

pscluster4

We’d like to see the nodes that the SQL Servers are on and their current state:

get-clusterresource -cluster sqlcluster | where-object {$_.ResourceType -like “SQL Server”} | foreach-object {($ownernode,$state) = ($_.OwnerNode,$_.State); $_ | get-clusterparameter -cluster sqlcluster -Name VirtualServerName,InstanceName | group-object ClusterObject | select @{Name=”SQLInstance”;Expression = {[string]::join(“\”,($_.Group | select-object -expandproperty Value))}},@{Name=”OwnerNode”;Expression={$ownernode}},@{Name=”State”;Expression={$state}}}

pscluster5

Turning to the availability groups in the first screenshot it seems each one gets its own resource group. Let’s filter just the availability groups and their resources (some may have availability group listeners defined, others may not):

get-clusterresource -cluster sqlcluster -inputobject (get-clusterresource -cluster sqlcluster | where-object {$_.ResourceType -like “SQL Server Availability Group”}).OwnerGroup

pscluster6

Here in the first column you can see the availability group name (MyHAGroup), the listener’s IP address (192.168.81.10) and the listener name (SQLHA1). This matches all the details you would see within the SQL Management Studio under the Availability Group configuration.

These quick one liners may be useful if you’re dealing with multiple SQL instances consolidated onto the same Windows cluster.

Advertisements
  1. #1 by Sam on July 22, 2013 - 3:16 am

    Is there a Powershell command for retrieving the size and free space of clustered Physical Disk Resources upon which SQL is running?
    Get-ClusterResouce -Cluster -Name | Get-ClusterParameters doesn’t have anything for size/free space

    • #2 by sqlscope on July 31, 2013 - 2:14 pm

      Simply use the Win32_Volume methods to retrieve the size and free space just like you would do for any standalone SQL Servers. For the computer name parameter specify the virtual SQL Server name.

  2. #3 by Hunter on December 10, 2013 - 10:57 pm

    Thank you, was looking for virtualname\instance.

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: