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

3 Node Hyper-V 2012 R2 Cluster Design

Below diagram shows a traditional 3 tier highly available cluster with 3 Hyper-V nodes and 2 shared storage nodes (storage in Active-Passive/ Active-Active mode) all connected via network switches.

Compute

*Hyper-V nodes are running on DELL PowerEdge R630 rack servers

Networking

*Shared storage is accessed via MPIO over two separate VLANs (61 and 62)

*VM traffic is over NIC teaming (Switch independent and dynamic)

*Live migration/ cluster network is also teamed together

Storage

*We are using DELL PowerEdge R320 with Open-E DSS V7 as storage nodes

*Each node has 8 x 10K SAS drives with RAID 5

*Storage can be either in Active-Passive/ Active-Active cluster mode

*In Active-Passive mode, only one of the storage nodes will be active. That means resources of only one storage node will be utilized at a time

*In Active-Active mode, both servers will be active and serving storage traffic 



Powershell script to monitor Azure AD Sync Scheduler status

Following powershell script will hep you to monitor status of AAD Sync Scheduler. Recently we had a requirement to monitor AAD Sync Scheduler status, where we had 2 servers. Primary and secondary. On both primary and secondary servers SyncCycleEnabled is set to True. On primary IsStagingModeEnabled is set to False and on secondary the same is set to True. AAD Sync Scheduler helps in synchronising changes happening in your on-premises directory to Azure AD. To view current configuration settings and status you can use Get-ADSyncScheduler .

Primary server:
SyncCycleEnabled - True
IsStagingModeEnabled  - False

Secondary server:
SyncCycleEnabled - True
IsStagingModeEnabled  - True

Any changes to the above states will have to trigger a warning message. The server in staging mode (secondary server in our case) is active for import and synchronisation, but it doesn't run any exports. A server in staging mode (secondary) continues to receive changes from Active Directory and Azure AD. It always has a copy of the latest changes and can be failed over to become primary. If you make configuration changes to your primary server, it is your responsibility to make the same changes to the server in staging mode. SyncCycleEnabled : True, indicates that the scheduler is running the import, sync and export processes as part of its operation. So its important to monitor the status of it at regular intervals.  

Script

$localcyclestatus = Get-ADSyncScheduler | select -ExpandProperty SyncCycleEnabled
$localstagingstatus = Get-ADSyncScheduler | select -ExpandProperty StagingModeEnabled

if($localcyclestatus -eq $True -and $localstagingstatus -eq $False)
{
Send-MailMessage -SmtpServer "smtp01.xyz.com" -Subject "AAD Sync Scheduler Operating Normally on Primary Server" -Body "AAD Sync Scheduler Operating Normally on Primary Server" -From "primary@xyz.com" -To "alert@xyz.com"
}

else
{
Send-MailMessage -SmtpServer "smtp01.xyz.com" -Subject “AAD Sync Scheduler Warning on Primary Server” -Body "AAD Sync Scheduler Warning on Primary Server" -From "primary@xyz.com" -To "alert@xyz.com"
}

Similarly, to monitor the secondary server you just have to edit the if check statement. That is, SyncCycleEnabled and StagingModeEnabled will be True. If not trigger a warning email. You can run the script locally, or can execute it remotely using "invoke-command". For monitoring the Sync status in regular intervals you can configure it as a Windows schedule task.

Reference: Microsoft

How Windows Logon works

This post will give you a brief idea about how interactive logon works in Windows. Logon process is the first step in user authentication and authorisation. Following are the main components of interactive logon architecture.

-Winlogon
-GINA DLL (Graphical Identification and Authentication Dynamic Link Library)
-LSA (Local Security Authority)
-Authentication packages (NTLM and Kerberos)

Local logon and domain logon process are explained below.

Local logon:

Domain logon:

Reference: Microsoft technet

Note: Diagrams used are from technet article

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

What happens when you enable Intel VT

Lets consider the difference between virtualized and non-virtualized platforms.

Here VMM refers to Hypervisor. There are different privilege levels in the processor for instruction execution. These levels are called Rings (Ring 0, 1, 2, 3).

When you enable Intel VT:

• In a non-virtualized environment OS runs on ring 0. A single operating system controls all hardware resources
• Four privilege levels (rings) are employed on VT platforms
• When it is enabled hypervisor now runs on Ring 0 instead of an OS. Guest OS runs in Ring 1 or Ring 3
• VT allows the hypervisor to present each guest OS a virtual machine (VM) environment that emulates the hardware environment needed by the guest OS

When you enable Intel VT-x:

• Intel (VT-x) - is a hardware assisted virtualization technology
• Hardware support for processor virtualization enables system vendors to provide simple, robust, and reliable hypervisor software
• VT-x consists of a set of virtual machine extensions (VMX) that support virtualization of processor hardware for multiple software environments using virtual machine
• A hypervisor written to take advantage of the Intel®Virtualization Technology runs in a new CPU mode called “VMX Root” mode and the guest OS in the “VMX Non-root” mode. The VMM will manage the virtual machines through the VM Exit and VM Entry mechanism
• Hypervisor has its own privileged level (VMX Root) where it executes

Below figure shows difference in Ring levels of Intel VT and Intel VT-x

Reference: Intel

Anatomy of Hyper-V cluster debug log

• Get-ClusterLog dumps the events to a text file
• Location: C:\Windows\Clsuter\Reports\Cluster.log
• It captures last 72 hours log
• Cluster log is in GMT (because of geographically spanned multi-site clusters)
• Usage: Get-ClusterLog -timespan (which gives last "x" minutes logs)
• You can also set the levels of logs
• Set-ClusterLog -Level 3 (level 3 is default)
• It can be from level 0 to level 5 (increasing level of logging has performance impact)
• Level 5 will provide the highest level of detail
• Log format:
  [ProcessID] [ThreadID] [Date/Time] [INFO/WARN/ERR/DBG] [RescouceType] [ResourceName] [Description]