3.1 Configuring Azure SQL Database for Scale and Performance

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:

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.

Elastic Pools for Multi-Database Workloads

An elastic pool is a cost-sharing construct: you purchase a pool of eDTUs (DTU model) or vCores (vCore model) on a single logical server, and multiple databases draw from that shared pool instead of each having dedicated compute. The canonical use case is a software-as-a-service provider with hundreds of tenant databases whose activity peaks occur at different times. Instead of over-provisioning every database for its individual peak, you size the pool for the aggregate load and let databases borrow capacity as needed.

Key behaviors to remember: every database in a pool can be assigned a per-database minimum (guaranteed resources) and a per-database maximum (cap that prevents one noisy tenant from consuming the whole pool). All databases in a pool must live on the same logical server. Pools also simplify administration because you scale one pool rather than dozens of individual databases. The exam likes to test the inverse scenario too: a single database with a steady, high, predictable load does not belong in a pool - dedicated sizing is cheaper and more predictable.

Vertical Scaling Operations and Downtime Behavior

Scaling a database up or down is an online operation: the service provisions a new instance at the target size, copies or attaches the data, and switches over. The database remains available throughout almost the entire operation; only at the final switchover are active connections dropped - typically for a few seconds - so connection retry logic is essential. You can scale through the portal, CLI, PowerShell, REST, or T-SQL, for example:

ALTER DATABASE [SalesDB] MODIFY (SERVICE_OBJECTIVE = 'GP_Gen5_4');

Service objective names encode the model and tier: S3 or P2 for DTU, GP_Gen5_4 (General Purpose, 4 vCores), BC_Gen5_8 (Business Critical), or HS_Gen5_2 (Hyperscale). Changing between General Purpose and Business Critical involves data movement to or from local SSD, so it is a size-of-data operation, though it stays online. A rough conversion rule of thumb when migrating from DTU to vCore is that 100 DTUs of Standard maps to about 1 vCore of General Purpose, and 125 DTUs of Premium maps to about 1 vCore of Business Critical.

Read Scale-Out

Read scale-out gives you a free read-only replica on the Business Critical (vCore) or Premium (DTU) tiers - the same replica that provides high availability. To use it, add ApplicationIntent=ReadOnly to the connection string; the gateway then redirects the session to the readable secondary instead of the primary. Because replication is asynchronous, the replica can lag slightly, so it suits reporting and analytics queries that tolerate near-real-time data. General Purpose does not include a free read-scale replica (its replicas hold storage, not readable compute), and Hyperscale instead offers configurable high-availability replicas and named replicas for read scale and hybrid transactional/analytical processing. A frequent exam pattern: when a scenario describes reporting workloads slowing down OLTP on Business Critical, the answer is to point the reporting connection string at the read-only replica - not to scale up vCores and not to pay for geo-replication.