PowerShell v3: Get Volume Information for Clustered SQL Server Storage
Unfortunately, there are no native PowerShell Failover Cluster cmdlets which will give you the volume information for disks in a cluster. I've seen some bloggers use Get-ClusterSharedVolume but a Cluster Shared Volume or CSV is different from volumes used within a SQL cluster.
The script below, which was written with the help of this post on StackOverflow, gets the volume information for disks within specific SQL Server instances on all the clusters in a domain. This does not work with Availability Groups since they don't use shared storage.
1$clusters = Get-Cluster -Domain $env:USERDNSDOMAIN
2
3foreach ($cluster in $clusters.name) {
4 $sqlgroups = (Get-ClusterResource -cluster $cluster | Where-Object ResourceType -eq 'SQL Server')
5 if ($sqlgroups -ne $null) {
6 foreach ($sqlgroup in $sqlgroups) {
7 $sqlserver = Get-WmiObject -namespace root\MSCluster MSCluster_Resource -filter "Name='$sqlgroup'" -computername $cluster -Authentication PacketPrivacy
8 $resourcegroup = $sqlserver.GetRelated() | Where Type -eq 'Physical Disk'
9
10 foreach ($resource in $resourcegroup) {
11 $disks = $resource.GetRelated("MSCluster_Disk")
12 foreach ($disk in $disks) {
13 $diskpart = $disk.GetRelated("MSCluster_DiskPartition")
14 $clusterdisk = $resource.name
15 $diskstate = $resource.state
16 $diskdrive = $diskpart.path
17 $disklabel = $diskpart.volumelabel
18 $disksize = $diskpart.totalsize
19 $diskfree = $diskpart.freespace
20
21 switch ($diskstate) {
22 -1 { $diskstate = "Unknown"}
23 0 { $diskstate = "Inherited"}
24 1 { $diskstate = "Initializing"}
25 2 { $diskstate = "Online"}
26 3 { $diskstate = "Offline"}
27 4 { $diskstate = "Failed"}
28 128 { $diskstate = "Pending"}
29 129 { $diskstate = "Online Pending"}
30 130 { $diskstate = "Offline Pending"}
31 }
32
33 Write-Host "Cluster: $cluster"
34 Write-Host "SQL Resource: $sqlgroup"
35 Write-Host "Cluster disk: $clusterdisk"
36 Write-Host "Disk State: $diskstate"
37 Write-Host "Disk Drive: $diskdrive"
38 Write-Host "Disk Label: $disklabel"
39 Write-Host "Disk Size: $disksize"
40 Write-Host "Disk Free: $diskfree`n"
41 }
42 }
43 }
44 }
45}
I am writing this code for another project which stores the values in a database, which is why I'm using variables and Write-Host in the example. The output will look like this (well, hopefully yours will have a more advanced storage structure. These particular clustered instances were created for proof-of-concept):
1PS C:Usersadministrator> .Get-ClusteredDiskInfo.ps1
2Cluster: CLUSTER2
3SQL Resource: SQL Server
4Cluster disk: Cluster Disk 1
5Disk state: Online
6Disk Drive: Y:
7Disk Label: iscsivol
8Disk Size: 102269
9Disk Free: 102111
10Cluster: CLUSTER2
11SQL Resource: SQL Server (SQL2)
12Cluster disk: Cluster Disk 3
13Disk state: Online
14Disk Drive: F:
15Disk Label: iscsivol2
16Disk Size: 20476
17Disk Free: 20358