I don’t know about all of you, but it seems like I get a request to build a new SQL server every couple weeks. Whether or not we should have that many SQL servers is a different matter, but we do, so I got tired of building the same thing over and over. You could just build a VM template just for SQL deployments or you could do it using Powershell like I am writing about. Our SQL DBAs have a certain configuration they want to be laid down on each new SQL server we pump out. The problem.. its 12 disks. yes, 12. So I have to add 12 disks to the VM, then initialize them, then format them and mount to the correct location. It takes a while and its boring so I want to get it over with ASAP and move on to more youtu… err work..
Before we start, assumptions here are that you already have a VM deployed from a template and its running. For this script, I have added a 2nd SCSI controller (Paravirtual) to the VM for all the SQL Disks.
Adding disks to the VM
This will add the disks I need to the VM. If you looking at this and thinking; you’re not using RDMs for SQL? You’re using Thin provisioning for SQL? You’re not using different Datastores for each disk? Are you crazy?. The answer, yes, yes I am. But let us stay on topic and not get into the weeds for this post.
Connect-VIServer vcenter.domain.com $VM = get-vm -name yourvmname New-HardDisk -CapacityGB 50 -StorageFormat Thin -Controller "SCSI controller 0" New-HardDisk -VM $VM -CapacityGB 250 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 250 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 250 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 250 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1" New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1"
Do all the “stuff” inside the VM
The script is pretty easy to read and is pretty self-explanatory. Basically, it is doing all the steps I would have done by hand before.
#Bring Disks Online get-disk | where operationalstatus -eq Offline | Set-Disk -IsOffline:$false #Find RAW online disks and init with GPT get-disk | where PartitionStyle -eq RAW | Initialize-Disk -PartitionStyle GPT #Make mount point dirs mkdir D:\DBDataFiles01 mkdir D:\DBDataFiles02 mkdir D:\DBDataFiles03 mkdir D:\DBDataFiles04 mkdir D:\DBLogFiles01 mkdir D:\DBLogFiles02 mkdir D:\DBLogFiles03 mkdir D:\DBLogFiles04 mkdir D:\TempDB01 mkdir D:\TempDB02 mkdir D:\TempDB03 mkdir D:\TempDB04 $i = get-disk -Number 2 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBDataFiles01" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBDataFiles01 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 3 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBDataFiles02" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBDataFiles02 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 4 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBDataFiles03" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBDataFiles03 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 5 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBDataFiles04" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBDataFiles04 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 6 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBLogFiles01" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBLogFiles01 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 7 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBLogFiles02" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBLogFiles02 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 8 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBLogFiles03" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBLogFiles03 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 9 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBLogFiles04" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBLogFiles04 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 10 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\TempDB01" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel TempDB01 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 11 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\TempDB02" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel TempDB02 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 12 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\TempDB03" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel TempDB03 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True $i = get-disk -Number 13 New-Partition -DiskNumber $i.Number -UseMaximumSize Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\TempDB04" Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel TempDB04 –Confirm:$false Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True
A couple interesting notes. The “Set-Partition -NoDefaultDriveLetter:$True” needs to be run or the first time you reboot the VM all your disks will get a drive letter assigned to them. Since we are using mount point here, we don’t need a drive letter.
Make sure the disk numbers match up to what is shown in disk management or via get-disk if your on core with no GUI.
I realize there are probably different ways to code this to make it shorter. If that is something you want to improve upon then knock yourself out. This was the quick and easy way I threw it together to GSD (get stuff done).
Jonathon Harper 