Study guide
3.3 Configuring SQL Server on Azure VMs for Scale and Performance
DP-300: Administering Microsoft Azure SQL Solutions Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources
2 Planning and Deploying Azure SQL Solutions
2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching
3 Configuring Resources for Scale and Performance
3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression
4 Planning and Implementing a Migration Strategy
4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting
5 Configuring Database Authentication and Authorization
5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management
6 Implementing Security for Data at Rest and in Transit
6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints
7 Implementing Compliance Controls for Sensitive Data
7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL
8 Monitoring Resource Activity and Performance
8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events
9 Monitoring and Optimizing Query Performance
9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights
10 Configuring Database Solutions for Optimal Performance
10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing
11 Creating and Managing SQL Server Agent Jobs
11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates
12 Automating Deployments and Database Tasks in Azure
12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks
13 Planning an HA/DR Strategy and Backup and Restore
13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations
14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups
14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups
15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting
15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions
3.3 Configuring SQL Server on Azure VMs for Scale and Performance
Key Takeaways Explain This Topic AI
Memory-optimized E-series VMs (the Ebdsv5/Ebsv5 families) are the recommended starting point for production SQL Server because of their high memory-to-vCore ratio and premium storage support
Premium SSD v2 and Ultra Disk let you scale IOPS and throughput independently of disk size; Ultra Disk performance can even be changed dynamically without deallocating the VM
Stripe multiple data disks with Storage Spaces to aggregate IOPS and throughput into one volume, and format data/log volumes with a 64 KB allocation unit size
Place tempdb on the VM's local ephemeral SSD (the D: drive) - it is fast and free, but its contents are lost on stop/deallocate, which is acceptable only for tempdb
The SQL IaaS Agent extension unlocks automated backup, automated patching, best-practice configuration, and switching between pay-as-you-go and Azure Hybrid Benefit licensing with no downtime
Last updated: August 2026
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).
Disk type Performance scaling Host caching Best for
Premium SSD Capacity, IOPS, and throughput bundled per disk size Yes (ReadOnly for data, None for log) Mature baseline for most production workloads
Premium SSD v2 IOPS and throughput tuned independently of disk size No Smaller disks that need high performance at lower cost
Ultra Disk Highest IOPS/throughput, dynamically tunable at runtime without deallocating No Sub-millisecond latency, very high I/O, latency-sensitive OLTP
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.