PowerShell Script to Configure SQL Server Storage

Following on from the popularity of my last PowerShell post Script SQL Server Agent Jobs Using PowerShell, I thought I would share another script I created to make life being a DBA that little bit easier, in the hope that you too may enjoy the benefits.

You regular readers will know that I’ve been endeavouring to learn the art of PowerShell magic and am at a stage now where my skills are dangerous enough to make them perhaps worthy of sharing. On that note:

WARNING: This script will format the disk volumes on your server! Make sure you are 100% certain of what you are doing.

Of course you clever folks don’t need a disclaimer because you would never run code from the internet without first fully understanding what’s involved right!

One of the tasks we DBAs often need to perform are server builds and  I often find myself having the need to configure the disk storage (SAN LUNs). Some of the tasks I might need to perform include:

• Creating a new disk partition
• Ensuring partition alignment
• Labelling the partition
• Assigning either a mount path or drive letter
• Formatting with a desired allocation unit size

DBAs that have had to do this stuff will know that it’s pretty dull and involves a lot of clicking and running various utilities (i.e. DISKPART) in order to get done. When you’ve got multiple disks and servers to do, the time it takes can quickly add up. Enter PowerShell to the rescue then, for all of the above tasks can be performed by it and much more.

The script I have included below performs these tasks and although can be run as is, I would highly recommend that you use it as a either a guide or the basis for doing something similar in you own environments. One of the challenges I have had myself with PowerShell is the accessibility of the language (i.e. being able to leverage programming constructs from other languages) finding the right commands/structures/good examples of things that do similar tasks that I can use/tweak to do what I specifically need. I hope that this will server this purpose for you.

#Date:			30/04/12
#Author:		John Sansom
#Description:		PowerShell script to configure a new server disk (i.e. SAN Lun).
#Example Execution:	.\configureDisk.ps1

#Notes:			This script is intended to be run locally on the server where the storage is already presented and online.

Function Set-DriveFormat
{
	Param(
		[Parameter(Mandatory=$true)]
		[Int]
		$logicalDisk
	,
		[Parameter(Mandatory=$false)]
		[String]
		$driveLetter
	,
		[Parameter(Mandatory=$false)]
		[ValidateScript({Test-Path $_ -PathType 'Container'})]
		[String]
		$mountPath
	,
		[Parameter(Mandatory=$false)]
		[Int]
		$clusterSize = 65536
	,
		[Parameter(Mandatory=$true)]
		[String]
		$label
	)
	Process
	{
		#Use DISKPART Utility to prepare the Disk
		if( ($driveLetter) -and (!$mountPath) )
		{
			"Creating a disk with letter: $driveLetter"

			try{
				"select disk '$logicalDisk'","select partition 1","delete partition","create partition primary align=1024","assign letter='$driveLetter'" | DISKPART
			}
			catch [system.exception]
			{
				"Error executing the DISKPART command for logical disk: $logicalDisk"
			}

			try{
				"Formatting Disk with letter $driveLetter this can take some time ..."
				$driveLetter = $driveLetter + ":"
				$drive = Get-WmiObject -Class win32_volume -Filter "DriveLetter = '$driveLetter'"
				$drive.Format("NTFS",$true,$clusterSize,$label,$false)
			}
			catch [system.exception]
			{
				"Error formatting logical disk: $logicalDisk"
			}

		}
		elseif( (!$driveLetter) -and ($mountPath) )
		{
			"Creating a Disk with mount path: $mountPath"

			try {
				"select disk '$logicalDisk'","select partition 1","delete partition","create partition primary align=1024","assign mount='$mountPath" | DISKPART
			}
			catch [system.exception]
			{
				"Error executing the DISKPART command for logical disk: $logicalDisk"
			}

			"Formatting Disk with path $mountPath this can take some time ..."
			$drive = Get-WmiObject -Class win32_volume | Where-Object {$_.name -eq "$mountPath"}
			$drive.Format("NTFS",$true,$clusterSize,$label,$false)
		}
		else{"An Invalid Parameter Combination was specified. Ensure either a Mount Path or letter is given."}

  	}

} #end function Set-DriveFormat

#Call function using the desired parameters
Set-DriveFormat -logicalDisk 9 -driveLetter "J" -label "Data"

If you would you like to read more posts on PowerShell let me know!