Best practices while virtualizing Microsoft SQL servers using Hyper-V

-Limit min and max memory for SQL server
-Use fixed size VHDX
-Split data and log files into separate VHDX disks
-Use multiple SCSI controllers
-Right sizing and not over allocating resources
-Making use of multiple RAID disk groups (for sequential and random access)
-For tier-1 mission critical applications use RAID 10 for data, log files, and tempDB for best performance and availability
-For lower tier SQL workloads when cost is a concern, data and tempDB can be on RAID 5
-Use of SSDs or tiered storage for higher IOPS
-If using VMQ on Hyper-V environment, on the guest OS side you can enable vRSS for processing network load across multiple CPUs
-I prefer using fixed size memory for SQL VMs
-Exclude SQL DB related files  from (*.mdf, *.ldf, *.ndf, *.bak, *.trn) on access antivirus scan
-Disable content indexing on SQL data/ log/ tempDB drives
-Enable lock pages in memory (group policy setting)
-DB IFI (Instant File Initialization)
-Set OS power plan to high performance
-OS performance options - visual effects - adjust for best performance

Hyper-V VM deployment using powershell and VHDX templates

Following powershell script can be used to deploy virtual machines on a Hyper-V host.

CODE:

#Start
#VM name
[string]$vmname = Read-Host "Name of VM"
$vmcheck = Get-VM -name $vmname

#To check for duplicate VM on the host
if(!$vmcheck)
{
Write-Host "Above warning can be ignroed as there is no duplicate VM. Please proceed and enter following details. `n"
[int32]$gen = Read-Host "Generation type"
[int32]$cpu = Read-Host "Number of vCPU"
[string]$vmpath = Read-Host "Enter path for VM configuration files (Eg: E:\VM)"
[string]$dynamic = $null

while("yes","no" -notcontains $dynamic)
{
$dynamic = Read-Host "Will this VM use dynamic memory? (yes/no)"
}

#Dynamic memory parameters
if($dynamic -eq "yes")
{
[int64]$minRAM = Read-Host "Minimum memory (MB)"
[int64]$maxRAM = Read-Host "Maximum memory (MB)"
[int64]$startRAM = Read-Host "Starting memory (MB) [Note: Specify value between $minRAM and $maxRAM]"
$minRAM = 1MB*$minRAM
$maxRAM = 1MB*$maxRAM
$startRAM = 1MB*$startRAM

#Creating the VM with dynamic RAM
New-VM -Name $vmname -Path $vmpath -Generation $gen
Set-VM -Name $vmname -DynamicMemory -MemoryStartupBytes $startRAM -MemoryMinimumBytes $minRAM -MemoryMaximumBytes $maxRAM
}

else
{
#Creating the VM with static RAM
[int64]$staticRAM = Read-Host "Static memory (MB)"
$staticRAM = 1MB*$staticRAM
New-VM -Name $vmname -Path $vmpath -Generation $gen -MemoryStartupBytes $staticRAM
}

#Setting VM auto start to none and auto stop to shutdown
Set-VM -Name $vmname -ProcessorCount $cpu -AutomaticStartAction Nothing -AutomaticStopAction ShutDown

#Creating VM hard disk directory
New-Item -path $vmpath\$vmname -name "Virtual Hard Disks" -type directory

#Enabling processor compatibility configuration for migration
Set-VMProcessor $vmname -CompatibilityForMigrationEnabled $true
}#vmcheck ends here

else
{
Write-Host "A VM named $vmname already exists"
}

#End

Now the VM is created. But it doesn't have virtual hard disk (VHDX file). Assuming that you already have a syspreped VHDX template. Copy that VHDX template to the virtual hard disk folder of the VM that you just created. Rename it as per your standard. Now attach the disk to SCSI controller if Gen 2 or to IDE controller if Gen 1. Change the boot order and select hard drive as first boot entry. Connect the NIC to vSwitch. Now you can start your VM.

Reference:

techthoughts
starwindsoftware

Creating VHD/ VHDX templates

Templates can save you lot of time, instead of building a server from scratch. Following are the steps to create a VHD/ VHDX template that can be used on Hyper-V servers.

1.Create a virtual machine (say Windows Server 2008 R2 with 80 GB hard drive)

2.Make sure you create fixed disk

3.Install OS

4.Install Windows updates

5.Install Hyper-V integration services

6.Install any applications as per your requirement

Once you are done with all the above steps, now its time to sysprep your machine. This is shown below :

sysprep

Make sure you check Generalize and shutdown options. Once the system completes sysprep operation, it will automatically shutdown. You can now take a copy of this VHD disk, rename it as you wish and save it to a location where you keep your templates. Also, make sure to make this file as read-only, so that you can avoid booting it up accidentally.

Note : You can create VHD templates for OS earlier than Windows Server 2012 and VHDX templates for Windows Server 2012 and later.