SQL Server on VM’s in my Lab

I’m trying to automate as much as possible in my lab and part of that is building SQL Servers.  I’ve taken to trying to scripting this out.  For me, that involves using PowerShell to build out this machine.  

#region Variable Declaration
$CurrentOperation = "Declaring Variables"; $Step = 1
$VMName       = "SqlServer"  # Server Name
$CPUCount     = 8              # Number of CPU's to give the VM
$CPUReserve   = 100             # Percentage of CPU's being reserved
$RAMBoot      = 16GB            # Startup Memory
$DiskFormat   = "Thin"         # Use Thin, Thick, or EagerZerodThick Disks
$VlanName     = "VM - VLAN 40" # VLAN assignment for the Network Adapter
# Sizes and count of the disks
$VHDSizes = [ordered]@{ "C" =  40GB; # Boot
                        "D" =  26GB; # Page 
                        "E" =  40GB; # Programs
                        "F" =  16GB; # TempDB
                        "G" =  40GB; # Data
                        "H" =  40GB; # Logs
                        "I" =  40GB; # Backups
                       } 
$GuestOS = "windows9Server64Guest" # OS Identifer of the Machine

Write-Progress -Activity "Building $VMName" -CurrentOperation $CurrentOperation -Status "Step $Step"
#endregion

#region Import the VMware Module & Remove the Hyper-V Module (if enabled)
$CurrentOperation = "Import Module"; $Step = 2
if ( -not ( Get-Module -Name "VMware.PowerCLI" -ErrorAction SilentlyContinue ) )
{
    Import-Module VMware.PowerCLI -Force
}
if ( Get-Module -Name "Hyper-V" -ErrorAction SilentlyContinue )
{
    Remove-Module -Name "Hyper-V" -Confirm:$false -Force
}
#endregion Import the VMware Module & Remove the Hyper-V Module

#region Connect to vCenter server via Trusted Windows Credentials
$CurrentOperation = "Connect to vCenter"; $Step = 3
Write-Progress -Activity "Building $VMName" -CurrentOperation $CurrentOperation -Status "Step $Step"
if ( -not ( $global:DefaultVIServer ) )
{
    Connect-VIServer -Server LABVCENTER01.demo.lab
}
#endregion Connect to vCenter server via Trusted Windows Credentials

# Find the host with the most free MHz or specify one by using:
# $VMHost = Get-VMHost -Name "ESX Host Name"
$CurrentOperation = "Calculating CPU Reservation"; $Step = 4
Write-Progress -Activity "Building $VMName" -CurrentOperation $CurrentOperation -Status "Step $Step"
$VmHost = Get-VMHost | Sort-Object -Property @{ Expression = { $_.CpuTotalMhz - $_.CpuUsageMhz } } -Descending | Select-Object -First 1

# Calculate the MHz for each processor on the host
$MhzPerCpu = [math]::Floor( $VMHost.CpuTotalMhz / $VMHost.NumCpu )

# Convert the Disk Sizes to a list of numbers (for New-VM Command)
$DiskSizes = $VHDSizes.Keys | Sort-Object | ForEach-Object { $VHDSizes[$_] / 1GB }

$CurrentOperation = "Creating Shell Virtual Machine"; $Step = 5
Write-Progress -Activity "Building $VMName" -CurrentOperation $CurrentOperation -Status "Step $Step"
# Create the VM
$VM = New-VM -Name $VMName -ResourcePool $VMHost -NumCpu $CPUCount -GuestId $GuestOS -MemoryGB ( $RAMBoot / 1GB ) -CoresPerSocket $CPUCount

# Remove the OOTB Disk
Get-HardDisk -VM $VM | Remove-HardDisk -DeletePermanently -Confirm:$false

# Add the first disk
$CurrentOperation = "Adding Disks"; $Step = 6
Write-Progress -Activity "Building $VMName" -CurrentOperation $CurrentOperation -Status "Step $Step"

$VM | New-HardDisk -CapacityGB $DiskSizes[0] | New-ScsiController -Type ParaVirtual
# Add the rest of the disks
For ( $i = 1; $i -lt $DiskSizes.Count; $i++ )
{
    $VM | New-HardDisk -CapacityGB $DiskSizes[$i]
}

# Convert OOTB NIC
$CurrentOperation = "Converting Network Interface"; $Step = 6
Write-Progress -Activity "Building $VMName" -CurrentOperation $CurrentOperation -Status "Step $Step"
$VM | Get-NetworkAdapter | Set-NetworkAdapter -Type Vmxnet3 -NetworkName $VlanName -Confirm:$false

# Setup minimum resources
# CPU is Number of CPUs * Reservation (as percentage) * MHz per Processor
$CurrentOperation = "Set Reservations"; $Step = 7
Write-Progress -Activity "Building $VMName" -CurrentOperation $CurrentOperation -Status "Step $Step"
$VM | Get-VMResourceConfiguration | Set-VMResourceConfiguration -CpuReservationMhz ( $CPUCount * ( $CPUReserve / 100 ) * $MhzPerCpu )


# Configure the Locked Memory & EFI Boot
$CurrentOperation = "Finalizing the Virtual Machine"; $Step = 8
Write-Progress -Activity "Building $VMName" -CurrentOperation $CurrentOperation -Status "Step $Step"
$ConfigSpecs = New-Object -TypeName VMware.Vim.VirtualMachineConfigSpec
$ConfigSpecs.MemoryReservationLockedToMax = $true
$ConfigSpecs.Firmware = "efi"
$VM.ExtensionData.ReconfigVM_Task($ConfigSpecs)

I’m sure that there are 50 or 60 things I’m missing because I’m not a DBA.  If you have recommendations, please let me know.  I’d be glad to include them.

Sidebar:  It was asked why I didn’t just templatize this and deploy it repeatedly.  Mostly it’s because what I’m supposed to do is build several servers that are *wrong.*  Like I need to have everything on the C:\ Drive.  Then I need to have data and logs cohabitate.  Then I need to have TempDB and Data cohabitate.  Then I need to try it without a page file.  Then I need to do the exact same build, but for SQL on Linux.  The number of permutations gets out of hand and creating a template for each would be overly time consuming.  It’s a cost/benefit thing for me.