Study guide

3.1 Configuring Azure SQL Database 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.1 Configuring Azure SQL Database for Scale and Performance

Key Takeaways Explain This Topic AI

The DTU model (Basic, Standard, Premium) bundles compute, memory, and I/O into one blended measure; the vCore model exposes compute independently and is required for Hyperscale, serverless, zone redundancy, and Azure Hybrid Benefit

Serverless auto-pause has a minimum delay of 1 hour (configurable up to 7 days); while paused you pay only for storage, and the first connection after a pause triggers a resume that can take about a minute

Elastic pools let multiple databases on the same logical server share a pool of eDTUs or vCores, ideal for many databases with spiky, non-overlapping usage patterns

Vertical scaling is an online operation - existing connections are dropped briefly at the final switchover, so applications need retry logic

Business Critical and Premium tiers include a free read-scale replica reachable with ApplicationIntent=ReadOnly in the connection string

Last updated: August 2026

Purchasing Models: DTU vs vCore

Azure SQL Database offers two purchasing models . The DTU (Database Transaction Unit) model is the older, simpler option: a DTU is a blended measure of CPU, memory, and I/O, so you pick a pre-bundled size without thinking about hardware. It has three service tiers - Basic (5 DTUs, tiny workloads), Standard (S0 at 10 DTUs up to S12 at 3,000 DTUs, general workloads), and Premium (P1 at 125 DTUs up to P15 at 4,000 DTUs, low-latency I/O and in-memory OLTP). The DTU model is easy to reason about but gives you no control over hardware generation, no serverless option, no Hyperscale, and no Azure Hybrid Benefit licensing savings.

The vCore (virtual core) model exposes compute independently from storage and lets you choose hardware generations. It is the model the exam emphasizes because it unlocks the advanced features. Within the vCore model there are three service tiers :

Tier Storage architecture Typical latency Scale limits Best for

General Purpose Remote Azure Storage 5-10 ms Up to 4 TB, 2-128 vCores Most business workloads, budget-sensitive

Business Critical Local SSD 1-2 ms Up to 4 TB High-transaction OLTP, lowest I/O latency

Hyperscale Decoupled, multi-layer cache Varies Up to 10 GB - 128 TB Very large databases, fast scale and backup

General Purpose separates compute from storage and relies on Azure Storage durability - architecturally similar to SQL Server failover cluster instances. Business Critical places data and log files on local SSD attached to the compute node and maintains a four-replica Always On availability group behind the scenes (three replicas for high availability plus one readable secondary you can use for free). Hyperscale re-architects the engine with a multi-tiered caching layer: page servers, a log service, and remote snapshot storage. Because backups are snapshot-based, backup and restore of a multi-terabyte database can finish in minutes rather than hours, and scaling compute is a constant-time operation regardless of data size.

A classic exam trap: moving a database into or out of Hyperscale is not a simple tier change - it is a migration (a size-of-data copy operation). Reverse migration is possible but tightly bounded: if the database was originally migrated to Hyperscale from another Azure SQL Database service tier, you can reverse migrate it to General Purpose within 45 days of that original migration, and reach any other tier by reverse migrating to General Purpose first. A database created directly as Hyperscale cannot be reverse migrated at all. Changing between General Purpose and Business Critical, by contrast, is a supported in-place scaling operation.

Serverless vs Provisioned Compute

Within the vCore model you choose a compute tier : provisioned (compute is reserved and billed hourly whether or not it is used) or serverless (compute auto-scales between a minimum and maximum vCore range you define and is billed per second based on vCores actually used). Serverless is the right answer for intermittent, unpredictable workloads that can tolerate a warm-up delay.

Serverless adds auto-pause and auto-resume . If the database is idle for the configured auto-pause delay - minimum 1 hour , maximum 7 days, default 1 hour - the database pauses and you are billed only for storage . The next login or activity triggers auto-resume, which typically takes on the order of a minute, and the very first connection attempt may fail, so applications must implement connection retry logic. While online, you are billed for at least the configured minimum vCores; while paused, features that require the database to stay online (such as geo-replication) prevent auto-pause. Exam traps here: auto-pause is only available in serverless, the minimum pause delay is one hour (not minutes), and serverless trades a resume delay for cost savings - it is wrong for latency-sensitive always-on workloads.