Halloween special – Azure ghost NVMEs

Azure VM Upgrades for SQL Server

I recently worked on a project to upgrade a client’s Azure virtual machines from the old series to the new generation family sizes. In this blog, I focus on the Azure VM E family, widely used for SQL Server workloads, but this also applies to the D family.

VM Families – The E and D Series

Some of the benefits of upgrading to the latest VM series include:

• New generation and improved/faster hardware
• Increased limits and throughputs
• NVMe local disks

Those familiar with setting up SQL Server on Azure VMs know that, in most scenarios, the locally attached disk is the ideal place for tempdb, though not always. In the old series, this used to be a Premium SSD disk; in the new generation of VMs, it’s a super-fast NVMe disk.

Handling Tempdb Storage in Azure VMs

Here is the gotcha – in older families, you didn’t have to initialise the temporary disk, it was always there after stopping (deallocating) and restarting the VM. You only had to perform a bit of magic to recreate the tempdb folders and start up the SQL Services. Microsoft later introduced the SQL IaaS extension to handle this process – with its own gotchas, though not for this blog.

Understanding the NVMe Disk Change

With the new families, the local disk isn’t there! Well, it is there, you just can’t see it – just like a ghost! This surprised me because I was used to the older VM families and took the disk for granted. I also struggled to find information in the MS documentation, it’s tucked away in the FAQ section:

“Unlike the previous D/E series VMs, the new v6 VMs come with raw, unformatted NVMe disks. Customers should initialise and format the disks into a file system of their preference after the VM starts up.”

So be aware, every time you stop a VM, you must initialise the disk and create a volume before creating the tempdb folders for SQL Server.

Automating Disk Initialisation with PowerShell

Here is a little treat: a PowerShell script to handle this process with a bit of logging, to help troubleshoot if something goes wrong. Stick it in a file on the server, set your SQL Server services to start manually and call the PS script at startup with a Windows scheduled task.

#Log execution details to output file
$OutputFileTimestamp = Get-Date -Format "yyyyMMdd_HHmm"
$OutputFile = New-Item "C:\Scripts\SQLStartup_$OutputFileTimestamp.txt"
Start-Transcript -Path $OutputFile -Append


#Initialise disk and create volume
$allocationUnit = 65536
Get-disk | Where-Object -FilterScript {$_.PartitionStyle -eq 'RAW' -and $_.IsSystem -eq $false -and $_.BusType -eq 'NVMe'} |
Initialize-Disk -PartitionStyle GPT -PassThru |
New-Volume -FileSystem NTFS -DriveLetter "D" -FriendlyName "Temporary Storage" -AllocationUnitSize $allocationUnit


#Create tempdb folder, if doesn't exist already
$folder = "D:\SQL\Tempdb"
if (!(Test-Path $folder))
{
New-Item $folder -ItemType Directory
New-Item D:\DATALOSS_WARNING_README.txt
Set-Content D:\DATALOSS_WARNING_README.txt 'WARNING: THIS IS A TEMPORARY DISK.

Any data stored on this drive is SUBJECT TO LOSS and THERE IS NO WAY TO RECOVER IT.

Please do not use this disk for storing any personal or application data.'

$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
Write-Host "[$timestamp] ✅ " $folder "created!" -ForegroundColor Green

}
else
{
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
Write-Host "[$timestamp]-" $folder "already exists!" -ForegroundColor Cyan
}

#Start SQL Server services, if not running already
$service = Get-Service 'MSSQLSERVER'

if ($service.Status -ne "Running") {
Start-Service 'MSSQLSERVER'
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
Write-Host "[$timestamp] ✅ " $service.name "started!" -ForegroundColor Green
Write-Host "."
} else {
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
Write-Host "[$timestamp]-" $service.name "already running!" -ForegroundColor Cyan
}

$service = Get-Service 'SQLSERVERAGENT'

if ($service.Status -ne "Running") {
Start-Service 'SQLSERVERAGENT'
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
Write-Host "[$timestamp] ✅ " $service.name "started!" -ForegroundColor Green
} else {
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
Write-Host "[$timestamp]-" $service.name "already running!" -ForegroundColor Cyan

Migration Requirements and Compatibility Notes

Other important things to be aware of:

• You can’t upgrade directly from v5 (or older) to v6 because the storage types are incompatible  (SCSI vs NVMe). If you are on an older series, you may have noticed that the new generation is not available in the resize options. You’ll need a side-by-side migration.
• Creating a new VM from one of the SQL Server images that use temporary storage for tempdb fails during setup, because the local drive isn’t ready for use. The setup instead creates tempdb on the C:\ drive. You’ll have to select another disk and then update the tempdb settings later manually
• If you were, you can no longer rely on the SQL IaaS extension to manage tempdb storage and start up your SQL Services, Disable the “Manage tempdb database folders on restart” setting in the SQL VM storage configuration.. Use the above method instead, which has always been my preferred one.

Hope this helps, and Happy Halloween!