• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

PowerShell Script to Configure SQL Server Storage

May 8, 2012 by John Sansom 4 Comments

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:
  • Functions
  • Parameter definition for Functions
    • Strongly typed parameters
    • Assigning default parameter values
    • Parameter validation
  • The use of Error Handling via the Try-Catch block structure
  • Conditional logic

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!

Filed Under: Administration Tagged With: Automation, Disk Alignment, Powershell, Storage Configuration

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy