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.

For your own PowetShell learning, some of the specific coding techniques/concepts that are being leveraged in the script include:

Script Source Code

#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!

Subscribe to Updates

Sign up to receive email updates with all my latest SQL Server content, including the famous Something for the Weekend (SFTW) each week


Comments

  1. Hi John,

    I’m installing a 6 node cluster at the moment with 206 LUNs. Your script helped me a lot!
    Just found a small issue in the format code of a mount point.

    In the comment “Formatting Disk with path $path this can take some time …” , the $path variable doesn’t exist. Should be changed into $mountPath

    The statement $drive = Get-WmiObject -Class win32_volume | Where-Object {$_.name -eq “$mountPath”} never returns the drive. The name of the mountpoint is not $mountPath but there is a “backslash” behind the name.
    When I change it into

    $drive = Get-WmiObject -Class win32_volume | Where-Object {$_.name -eq “$mountPath\”}

    the format of the mount point succeeded.

    Thanks for the script!

    Regards
    Pieter

    • Pieter – Awesome, glad you found it useful!

      You’re right sir, the $path variable had since been swapped for $mountPath in “my” version of this script :-) I’ll get the code above updated to reflect this. Good catch.

      Regarding your second point, that will certainly work, I myself include the trailing slash when defining the $mountPath parameter that is passed. I should probably have included an example execution of this form for clarity.

Leave a comment

*