SQL Server on Azure VM Best Practices: Size, Storage, Config

Introduction

Running SQL Server on an Azure VM gives you full engine control and compatibility. It also hands you the responsibility for performance, reliability, and cost. Those outcomes depend on choices across compute, storage, SQL configuration, and day-two operations.

Most problems trace back to the same few mistakes: sizing from database capacity alone, stacking data, log, and TempDB on one volume, and expanding disks through the wrong path.

This guide takes a workload-first approach. It covers VM sizing, separating data and log I/O, handling TempDB and disk expansion safely, using the SQL IaaS Agent Extension, and monitoring utilization over time.

Key Takeaways

  • Size the VM from measured CPU, memory, I/O, and availability needs, not database size.
  • Separate OS, data, log, and TempDB storage, then validate disk performance.
  • Treat caching, storage pools, and deallocation behavior as storage design decisions.
  • Revisit utilization, recoverability, and storage cost as the workload changes.

Decide Whether SQL Server on an Azure VM Fits the Workload

When an Azure VM is the right model

Choose a VM when you need:

  • Full SQL Server engine control
  • Legacy feature compatibility
  • Custom instance configuration
  • Lift-and-shift migration
  • Third-party integration

Microsoft's Azure SQL overview frames the trade-off clearly.

Deployment Who manages what
SQL Server on Azure VM You manage the OS, SQL Server, patching, backups, and HA/DR
Azure SQL Database Microsoft manages the OS, engine maintenance, backups, and built-in HA
SQL Managed Instance Microsoft manages patching and backups, with near-100% SQL Server compatibility

Define requirements before provisioning

Write down the facts before you click Create:

  • Workload type, peak concurrency, and performance objectives
  • Current data and log size, plus expected growth over time
  • RPO, RTO, compliance requirements, and maintenance windows
  • Performance baseline from the source system or a representative test (CPU, memory pressure, disk latency, IOPS, throughput, waits, log behavior)

Plan licensing, region, and lifecycle constraints

Pay-as-you-go includes the SQL license charge. Azure Hybrid Benefit requires eligible cores with active Software Assurance or qualifying subscription licenses. Neither wins universally, so check current Microsoft terms against your own entitlements.

Then validate the practical limits:

  • Region, VM family, and zone availability
  • Total regional vCPU quota and per-family quota (quota does not guarantee capacity)
  • Disk limits and networking design
  • Supported SQL Server and Windows or Linux combinations

Choose the Right Azure VM Size for SQL Server

Match resources to the workload profile

OLTP, reporting, data warehousing, and mixed workloads stress different resources. OLTP leans on latency and log throughput. Warehousing leans on memory and sequential throughput.

Separate compute bottlenecks from storage bottlenecks before you resize. A bigger VM does not fix a bad disk layout.

Compare VM families

Microsoft positions each family differently, per its SQL VM size guidance:

  • Memory-optimized (E, M): most production SQL workloads; M-series for the largest memory needs
  • General purpose (D): smaller workloads and dev/test
  • Compute-optimized (F): CPU-bound workloads with modest memory
  • Storage-optimized (L): storage-intensive analytics and ETL

Examples of documented limits show how much sizes differ:

Example size vCPU / memory Max data disks Uncached IOPS / MBps
E32bds_v5 (NVMe) 32 / 256 GiB 32 130,000 / 3,700
E32bs_v6 32 / 256 GiB 64 143,000 / 4,070
D32ds_v6 32 / 128 GiB 64 51,200 / 1,696
F32als_v6 32 / 64 GiB 32 57,600 / 1,440

Azure SQL VM size comparison showing memory disks IOPS and throughput

These are examples, not recommendations. Confirm current specs in Microsoft's VM size documentation. When you compare sizes, check:

  • vCPU and memory
  • Maximum data disks
  • Uncached and cached IOPS
  • Throughput and accelerated networking
  • Temporary storage and regional availability

Size for memory and headroom

Set max server memory only after reserving memory for Windows, agents, monitoring, backup tooling, and antivirus. Then monitor available memory.

Size for peaks, maintenance operations, failover capacity, and growth. Average utilization is a trap.

Validate with testing

Test representative queries, concurrency, batch windows, index maintenance, backups, and failover before production. Resize or redesign when you see:

  • Sustained CPU pressure or large memory grant waits
  • High storage latency or disk queue depth
  • Throughput ceilings at the disk or VM level

Build a resize and rollback plan

Resizing a running VM restarts it, so plan a maintenance window. Check quota and target-size availability first. Deallocation may be required if the size is not available on the current cluster.

Record the previous size, configuration, performance baseline, and rollback steps. Verify performance afterward against that baseline.

Design Azure Storage for SQL Server Performance and Growth

Separate storage by workload

Data files, sequential log writes, TempDB, and OS activity have different I/O patterns. Microsoft explicitly recommends separating data, log, and TempDB.

  • Never put production data or log files on the OS disk
  • Give backups and diagnostics their own paths where practical
  • Reserve capacity for growth, maintenance, and recovery operations

Choose disk types from measurements

Disk Capacity Max IOPS / MB/s Host caching
Premium SSD 4-32,767 GiB 20,000 / 900 Available
Premium SSD v2 1 GiB-64 TiB 80,000 / 2,000 None
Ultra Disk 4 GiB-64 TiB 400,000 / 10,000 None

These are disk maximums, not guaranteed VM throughput. Capacity, provisioned performance, VM limits, and workload demand all apply, and the slowest layer wins.

Ultra Disk is LRS-only and does not support availability sets. Verify regional availability before designing around it.

Configure layout, formatting, and caching

  • Use 64-KB NTFS allocation units
  • Keep Storage Spaces interleave at 64 KB on SQL VMs; striping remains bounded by VM limits
  • Use ReadOnly caching for data disks and None for log disks
  • Never use ReadWrite caching on SQL data or log disks

Then verify. Run workload tests and watch Azure Monitor for latency, IOPS, and throughput against your targets.

Azure SQL storage design showing separate data log TempDB and OS paths

Handle TempDB and ephemeral storage safely

Local temporary storage is fast, but ephemeral. A normal restart preserves it. Maintenance, redeployment, or stop/deallocation can destroy it.

The SQL IaaS Agent Extension can recreate TempDB folders and permissions for supported configurations. Test TempDB initialization after both a reboot and a full deallocation. Some uninitialized ephemeral NVMe configurations carry extra restrictions.

Microsoft removed the Full and Lightweight mode choice in March 2023. The extension now installs features based on need. Check the registered state, extension health, and permissions before relying on automation.

Expand storage through the correct path

If you use SQL VM managed storage pools, expand by adding disks through the SQL virtual machine storage workflow, respecting the original column count. Microsoft warns against enlarging existing pool-member disks. The added capacity can sit unused while you pay for it.

Before any production resize:

  1. Take a verified backup
  2. Write a change plan and confirm the maintenance window
  3. Make the change through the supported path
  4. Validate capacity and performance afterward

Four-step Azure SQL storage resize workflow from backup to validation

Control capacity and cost

Track these four numbers separately:

  • Required usable capacity
  • Free-space headroom
  • Provisioned performance
  • Unused allocation

Don't overprovision capacity just to buy IOPS.

Premium SSD v2 bills capacity, IOPS, and throughput separately. Microsoft's published East US example prices 1,024 GiB with 15,000 IOPS and 1,200 MB/s at $186.71 per month (LRS). That is a pricing illustration, not a full VM estimate.

Premium SSD v2 pricing example showing capacity IOPS throughput and monthly cost

Review regularly:

  • Database growth and autogrowth events
  • Unattached or idle disks
  • Snapshot retention and backup storage

Configure SQL Server, Availability, and Resilience

Essential instance and database settings

All of these are workload-dependent and need testing:

Setting Guidance
MAXDOP NUMA-dependent; 8 for a single NUMA node with more than eight logical processors
Cost threshold Default of 5 is a starting point; evaluate over a full business cycle
TempDB files One per logical processor up to eight; check contention before adding
Instant file initialization Enable for the SQL service account
Lock pages in memory Part of the SQL VM checklist; understand the memory trade-offs
Page compression Use where appropriate, weighing CPU cost

Set explicit file growth increments, watch for repeated autogrowth, and disable autoshrink.

Use the SQL IaaS Agent Extension intentionally

Marketplace SQL images register with the extension automatically. Migrated or custom VMs need registration to get Azure visibility, license management, and optional automation such as patching, backups, and storage configuration.

Verify extension health and documented behavior before changing storage.

Build a backup and restore strategy

Define full, differential, and log backups from RPO, RTO, recovery model, and retention. A configured backup is not a tested one. Test restores, integrity checks, and point-in-time recovery, and validate credentials and network paths.

Options include:

  • Azure Backup: centralized, SQL-aware protection with long-term retention and cross-region restore to the paired region
  • SQL IaaS Automated Backup: scheduled backups with retention up to 90 days
  • Native backup to URL: Blob storage as a backup destination

Immutable vaults help against ransomware and accidental deletion, but locked immutability is irreversible.

Design high availability and disaster recovery

  • Availability Groups protect databases through replicas
  • Failover Cluster Instances protect the whole instance using shared storage; Azure shared disks are a documented option
  • Availability sets and zones protect against infrastructure failures

Microsoft's HA guide quotes 99.95% for availability sets and 99.99% for zones, subject to SLA conditions.

Azure availability sets and zones SLA comparison for SQL Server resilience

Plan listener connectivity too. Multi-subnet designs can skip load balancer dependencies; single-subnet designs need a load balancer or distributed network name. Microsoft recommends a cloud witness unless you use shared storage.

Test failover, document manual procedures, and remember HA is not a backup.

Secure and govern the deployment

  • Use private virtual network access and NSG filtering
  • Enforce TLS, TDE, and Key Vault integration
  • Use Microsoft Entra authentication on SQL Server 2022 and later
  • Enable Defender for SQL and apply least-privilege access
  • Apply Azure Policy and tagging for consistent configuration (tags are not inherited)

Operate, Monitor, and Optimize the SQL VM

Monitor the complete performance path

Track metrics across every layer:

  • Azure: VM cached and uncached IOPS and bandwidth consumed percentage, plus disk queue depth
  • OS and SQL: CPU, available memory, Page Life Expectancy, disk latency, and waits such as PAGEIOLATCH_SH
  • Operations: log flushes, TempDB usage, autogrowth, and backup duration

Set alert thresholds from your baseline, then correlate Azure Monitor with Query Store and application telemetry. SQL Insights is retired; Microsoft points SQL VM users to VM Insights with data collection rules.

Maintain and revalidate

Schedule these on a regular cadence:

  • Patching and extension health reviews
  • Index and statistics maintenance
  • Integrity checks and secret rotation
  • Restore drills and failover tests

Re-baseline after any resize, disk change, SQL upgrade, schema change, or growth spurt.

Apply FinOps controls without risking performance

Review these regularly:

  • Idle or unattached disks (Azure Advisor's recommendation is preview, and deletion is irreversible)
  • Excess free capacity, snapshots, and backup storage
  • Reservations and savings plans (savings plans exclude software and storage)
  • Nonproduction schedules: deallocate the VM, since shutting down inside the OS still bills compute

Block storage is where overprovisioning hides. Lucidity is one option for teams that want real-time visibility into Azure block-storage volumes and automated identification of overprovisioned or idle capacity without infrastructure changes.

Its Lumen product provides tiering recommendations and idle-disk cleanup. Lucidity advertises savings of up to 70% on block storage, though your results depend on measured utilization.

Whatever tool you use, tie changes to measured utilization, approvals, performance guardrails, and a clear rollback path so savings never undercut SQL performance.

Frequently Asked Questions

Can I use SQL Server on Azure virtual machines?

Yes, on supported SQL and OS combinations. It suits teams that need engine-level control, legacy compatibility, or custom configuration, with the trade-off that you administer and optimize it.

What is the best Azure VM size for SQL Server?

There isn't a single best size. Base the choice on measured CPU, memory, IOPS, throughput, latency, disk limits, availability, and growth, then confirm with representative performance tests.

Should TempDB be on the local SSD of an Azure VM?

It can be, in supported configurations. The data is ephemeral and may be lost on deallocation, so test automatic folder recreation and SQL startup after reboots and deallocation.

How should I separate SQL Server data, log, and TempDB files in Azure?

Give each its own volume or storage path, since their I/O patterns differ. Validate disk type, caching, capacity, and VM limits for each.

Which Azure disk type is best for SQL Server?

It depends on latency, IOPS, throughput, capacity, caching support, regional availability, and cost. Check current Microsoft limits before choosing a tier.