Provisioning Azure VMs Repeatably: Why You Need a Gallery Image Right Now

Shannon Lowder

Shannon Lowder

— 3 min read

The second time I set up an Azure VM for SQL Server, I found myself doing the same 45-minute configuration dance I'd done the first time: attach data disks, format with the right allocation unit size, move database files, set MAXDOP, configure max server memory, set up backup jobs, create SQL logins. All from memory, all manual, all the same as last time but slightly different because I was working from a mental checklist rather than a documented one.

By the third VM I'd had enough. Repeatable infrastructure starts with documented, version-controlled configuration — and in 2012 on Azure, the right tool for this was a capture-to-image workflow. Here's how I built it and why it mattered.

The Problem With Manual Provisioning

Manual configuration has two failure modes. The first is inconsistency: VM #3 has MAXDOP set differently than VM #1 because you misremembered the setting. The second is undocumentability: when something breaks, you can't tell whether it's a configuration deviation from the baseline or a new problem, because you don't have a precise record of what the baseline was.

Both failure modes are invisible until they matter. Then they matter a lot.

The Azure VM Image Capture Workflow

Azure let you capture a running VM as a custom image, which you could then use to provision new VMs from a known baseline. The workflow:

  1. Build and configure a "golden VM" — everything installed, configured, and tested to the standard
  2. Run Sysprep on the VM to generalize it (remove machine-specific identifiers)
  3. Capture the VM as an image in your Azure Storage account
  4. Use the captured image as the base for new VM provisioning
REM On the golden VM, run Sysprep before capture
C:\Windows\System32\Sysprep\sysprep.exe /generalize /oobe /shutdown

After shutdown, use the Azure Management Portal to capture the VM as an image. The captured image includes the OS, SQL Server installation, and all the configuration you applied.

What Goes Into the Golden VM

The golden VM configuration I standardized on in 2012:

  • Windows Server 2008 R2 with all current patches applied at capture time
  • SQL Server 2012 with the latest service pack, Engine and Management Tools components only
  • SQL Server configured with MAXDOP = half of available cores, max server memory = total RAM minus 4 GB for the OS
  • SQL Server error log retention set to 30 days (default is 6, which isn't enough for post-incident forensics)
  • Windows and SQL Server event log forwarded to a storage account via a lightweight monitoring agent
  • SQL Server backup credential to Azure Blob Storage pre-created
  • SQL Server Agent backup jobs pre-configured (full weekly, differential daily, log every 15 minutes)
  • Baseline monitoring queries scheduled as SQL Agent jobs to capture DMV snapshots every 5 minutes

What's NOT in the golden VM:

  • Database files (those get created at provisioning time for the specific workload)
  • Application-specific SQL logins (environment-specific, added during deployment)
  • Machine name and IP configuration (Sysprep removes these)

The Configuration Script Companion

A captured image handled the installation and base configuration. Environment-specific configuration — machine name, SQL Server network alias, database placement, application logins — happened via a PowerShell script that ran on first boot of a new VM provisioned from the image.

# Post-provision configuration script (simplified)
param(
    [string]$SqlInstanceName,
    [string]$DataDriveLetter,
    [string]$LogDriveLetter,
    [string]$AppDbName
)

# Initialize and format data disks
Initialize-Disk -Number 2 -PartitionStyle MBR
New-Partition -DiskNumber 2 -UseMaximumSize -DriveLetter $DataDriveLetter |
    Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "SQLData"

# Create database directories
New-Item -Path "${DataDriveLetter}:\SQLData" -ItemType Directory
New-Item -Path "${LogDriveLetter}:\SQLLog" -ItemType Directory

# Set SQL Server default paths
$sql = "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'DefaultData', REG_SZ, N'${DataDriveLetter}:\SQLData'"
Invoke-Sqlcmd -Query $sql

Write-Host "VM provisioned and configured for SQL Server workload."

Why This Was Worth the Upfront Investment

A new SQL Server VM that used to take 45 minutes of manual work took 15 minutes with the golden image plus provisioning script: 10 minutes to spin up the VM from the image, 5 minutes to run the configuration script. More importantly, the configuration was identical to every other VM provisioned from the same image. Troubleshooting problems got faster because I could rule out configuration drift as a variable.

This is the same principle that makes container images valuable — but we're talking about 2012, before Docker existed, and the equivalent pattern for VM infrastructure. The insight is the same: deployable artifacts that encode your configuration are more valuable than documented procedures for applying configuration manually. The procedure gets followed inconsistently. The image gets applied exactly.

If you're spinning up VMs manually and building the "runbook" in your head, this is the investment worth making now. As always, I'm here to help.

Read more