3.3 Configuring SQL Server on Azure VMs for Scale and Performance

VM Series Selection

With SQL Server on Azure Virtual Machines you own the whole stack, so sizing starts with the VM family. Microsoft's documented guidance is to start with memory-optimized VMs - the E-series, specifically the Ebdsv5/Ebsv5 families - for most production OLTP workloads. They offer a high memory-to-vCore ratio (8 GB per vCore), strong remote-storage throughput, and support for premium storage with host caching, which together match SQL Server's appetite for buffer pool memory and log write throughput. Other families have niche roles: D-series general-purpose VMs for dev/test or lightweight workloads, and M-series for very large memory footprints. Constrained vCore VM sizes are a licensing tool: they expose fewer vCores than the hardware while keeping the full memory and I/O capacity, which reduces per-core SQL Server licensing cost for memory-bound workloads.

The most common VM-sizing trap is ignoring the VM-level I/O ceiling. Every VM size caps uncached disk IOPS and throughput. You can attach disks rated for more IOPS than the VM can deliver and never see the performance you paid for - the VM throttles first. The fix is either a VM size with a higher storage cap or Premium SSD v2/Ultra disks whose performance you tune to sit under the VM ceiling. When an exam question shows I/O latency climbing while disk metrics look healthy, check whether the VM's uncached throughput limit is the bottleneck.

Storage: Premium SSD v2 and Ultra Disk

For data and log files you choose among managed disk types. Premium SSD is the mature baseline: capacity, IOPS, and throughput are bundled per disk size, and it supports host caching - set caching to ReadOnly for disks holding data files (read caching helps random read-heavy workloads) and to None for disks holding log files, because log writes must be durable and write caching is not used. Premium SSD v2 decouples performance from capacity: you provision IOPS and throughput independently of disk size (up to tens of thousands of IOPS), which is ideal for smaller disks that need high performance, and it is cheaper per unit of performance - but it does not support host caching. Ultra Disk is the top tier: the highest IOPS and throughput, the ability to change IOPS and throughput dynamically at runtime without deallocating the VM, and sub-millisecond latency - again with no host caching, and with deployment constraints (data disks only, supported regions/zones only, no availability sets).

Storage Pools and Striping

A single premium disk often cannot saturate a large VM's storage bandwidth, so the standard pattern is a storage pool: attach several premium disks and stripe them with Storage Spaces into one virtual disk, then format a single NTFS volume with a 64 KB allocation unit size (matching SQL Server's 8 KB pages read in 64 KB extents). Striping aggregates the IOPS and throughput of every member disk, so three P30 disks behave like roughly 15,000 IOPS of capacity rather than 5,000. Guidance details that show up on the exam: use one storage pool per workload purpose rather than mixing data and logs arbitrarily, keep stripe geometry at the Storage Spaces defaults (interleave 64 KB or 256 KB), and never put user databases on the OS disk or the temporary disk.

tempdb on Local SSD

Every Azure VM includes a temporary storage disk - the D: drive on Windows - backed by SSD physically attached to the host. It is fast, free, and ephemeral: its contents are lost whenever the VM is stopped/deallocated, moved to a new host, or resized. That volatility makes it useless for durable data but perfect for tempdb, which SQL Server recreates from scratch on every service start. Placing tempdb on D: is a documented best practice: it removes tempdb I/O contention from your premium storage volumes and saves the cost of provisioning remote disks for scratch data. Two operational details matter: first, if the folder holding tempdb files does not exist after a host move, SQL Server fails to start, so either let the SQL IaaS Agent extension manage tempdb placement automatically or create the folder through a startup script or scheduled task; second, never store anything else on D: - an exam distractor offering user database files or backups on the temporary disk is always wrong.

SQL IaaS Agent Extension

The SQL IaaS Agent extension is a free extension that registers your VM with the SQL VM resource provider and is central to the DP-300 objectives for this area. Once registered you get: automated backup (scheduled full/log backups to blob storage with retention), automated patching (a maintenance window for OS and SQL Server updates), best-practice assessment (a scanned report of configuration deviations), storage and tempdb auto-configuration for marketplace images, Azure Key Vault integration, and a management blade in the portal. The extension runs in two modes: lightweight mode only reports edition and license metadata and never touches the SQL Server service; full mode installs the agent that unlocks the management features and requires a restart of the SQL Server service during installation - schedule it. A key exam point: many portal capabilities and licensing benefits are only available once the VM is registered with the extension.

Licensing: AHUB vs PAYG

Two licensing models apply. Pay-as-you-go (PAYG) bundles the SQL Server license into the per-second VM price - zero procurement, highest steady-state cost. Azure Hybrid Benefit (AHUB) lets you apply your own SQL Server licenses with active Software Assurance (or qualifying subscriptions) and pay only the base compute rate, saving substantially on long-running production VMs. The operational fact the exam tests: through the SQL IaaS Agent extension you can switch the license type between PAYG and AHUB at any time, with no downtime and no redeployment - it is a metadata change on the registration. You do not need to rebuild the VM from a bring-your-own-license marketplace image. Dev/test subscriptions get free SQL Server licensing on dev/test images, another cost lever worth recognizing in scenario questions.