1. Choose the VM
- Memory-optimised sizes with high memory-to-vCore ratio (E-series, Ebdsv5 for high IOPS) suit most OLTP.
- Check the VM's uncached disk throughput and IOPS limits. A VM size caps the total from all disks.
- Licence: pay-as-you-go image or Azure Hybrid Benefit with your own licence.
2. Register with the SQL IaaS Agent extension
Gives automated backup and patching options, storage configuration in the portal and licence management.
az sql vm create -g rg-sql -n vm-sql01 --license-type AHUB --sql-mgmt-type Full
3. Storage layout
| Purpose | Disk | Host caching |
|---|---|---|
| Data files | Premium SSD / Premium SSD v2, striped in a Storage Space if needed | ReadOnly |
| Transaction log | Separate Premium SSD (v2) | None |
| tempdb | Local ephemeral D: drive (fast, free) | n/a |
| Backups | Azure Blob via backup to URL, not a local disk | n/a |
Format data and log volumes with 64 KB allocation unit size. If you put tempdb on D:, make sure the folder is recreated at startup (the SQL IaaS extension can manage this).
4. Instance configuration
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 112640; -- leave ~10-15% for the OS
EXEC sp_configure 'cost threshold for parallelism', 50;
EXEC sp_configure 'max degree of parallelism', 8;
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;
Also: grant Lock Pages in Memory and Perform Volume Maintenance Tasks (instant file initialization) to the service account; enable Query Store on user databases.
5. Backups
CREATE CREDENTIAL [https://stsqlbackupcin.blob.core.windows.net/sqlbackups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<sas-token-without-?>';
BACKUP DATABASE Sales TO URL = 'https://stsqlbackupcin.blob.core.windows.net/sqlbackups/Sales_full.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;
Options: SQL IaaS automated backup, Azure Backup for SQL Server in VM (central, policy-based, with log backups every 15 minutes), or your own Agent jobs to URL. Pick one, not several competing jobs that break the log chain.
6. Protect the single instance
- Daily VM-level backup plus SQL-native backups (VM snapshots alone don't give point-in-time recovery).
- Backup storage account with GRS/RA-GRS if backups are part of the DR plan.
- Alerts on backup failure and disk latency (
Disk Read/Write Latency> 10 ms on data disks).
When standalone isn't enough
If the customer needs minutes of RTO across zones, move to an Always On availability group across availability zones (see the HA post). Design the disk layout and service accounts now so that move is easy.