2.1 Choosing and Deploying Azure SQL Database Offerings

The Azure SQL Family at a Glance

Quick Answer: Choose Azure SQL Database for cloud-native workloads: single databases for isolated apps, elastic pools for many databases with uncorrelated spiky usage, Hyperscale for databases growing past 4 TB, and serverless for intermittent workloads. Choose Azure SQL Managed Instance when a lift-and-shift migration needs instance-scoped features like SQL Server Agent, cross-database queries, or linked servers. Choose SQL Server on Azure Virtual Machines (VMs) only when you need operating-system or instance-level control that platform as a service (PaaS) cannot provide.

Microsoft treats Azure SQL as one family with three deployment choices, ordered from most managed to least managed:

• Azure SQL Database — fully managed PaaS scoped at the individual database. Microsoft owns the OS, the instance, patching, and built-in high availability.
• Azure SQL Managed Instance — PaaS scoped at the SQL Server instance, deployed inside your own Azure Virtual Network (VNet) with a private IP address.
• SQL Server on Azure VMs — infrastructure as a service (IaaS). You control the OS, the SQL Server version and edition, and every instance setting; you also own patching and availability.

The exam repeatedly tests the boundary between these three, so anchor every scenario question to one question: what is the least managed option that still meets every stated requirement?

Purchasing Models: DTU Versus vCore

Azure SQL Database offers two purchasing models.

The Database Transaction Unit (DTU) model bundles compute, memory, and I/O into a single blended measure. Tiers are Basic, Standard (S0 through S12), and Premium (P1 through P15). It is simple to understand and fine for small, predictable workloads, but you cannot scale compute and storage independently, and it does not support Azure Hybrid Benefit for licensing savings.

The vCore model exposes virtual cores directly so you can choose compute, memory, and storage independently. Its service tiers are:

• General Purpose — balanced, budget-oriented, uses remote Azure premium storage; most production workloads land here by default.
• Business Critical — local solid-state drive (SSD) storage, the highest I/O performance, and a free readable secondary replica for read scale-out.
• Hyperscale — a multi-layered architecture (compute nodes, page servers, log service, Azure storage snapshots) that scales to 128 TB, adds read replicas rapidly regardless of data size, and delivers near-instant backups and restores that are size-of-data independent.

Within vCore you also pick the compute tier: provisioned (always on, billed per hour) or serverless (auto-scaling and auto-pausing, billed per second of use). A classic exam trap: serverless auto-pause exists only for Azure SQL Database single databases, not for Managed Instance, and the first connection after a pause incurs resume latency, so it is wrong for latency-sensitive applications.

Deployment Options Inside Azure SQL Database

A single database gets its own guaranteed resources on a logical server. It is the default for one application, one database.

An elastic pool is a shared resource allocation (eDTUs or vCores) that many databases on the same logical server draw from. You set per-database minimum and maximum limits so one database cannot starve the others. Pools win when aggregate cost would be lower than provisioning each database for its individual peak — the classic case is a software-as-a-service (SaaS) vendor with dozens of tenant databases whose busy periods do not overlap. Remember: the pool is the billed object, and all databases in a pool share its service tier.

Hyperscale is both a service tier and an architecture. Beyond the 128 TB ceiling (elastic pools top out at 100 TB), it supports rapid scale-out of named read replicas, constant-time backup via file snapshots in Azure storage, and restore times measured in minutes even for multi-terabyte databases. It supports serverless compute as well.

When Managed Instance Is Forced

Managed Instance targets near-100% surface-area compatibility with boxed SQL Server. The instance-scoped capabilities that force you off a single database and onto Managed Instance are high-frequency exam material:

• SQL Server Agent jobs (a single database has no Agent; the PaaS workaround is elastic jobs)
• Cross-database queries using three-part names (single databases reach other databases only through elastic query, which is read-only and limited)
• Linked servers to other data sources
• Service Broker, common language runtime (CLR) assemblies, Database Mail, and distributed transactions
• Native VNet integration with a private IP, enabling private connectivity from on-premises networks
• The Managed Instance link, which replicates a SQL Server 2022 (or later) database to Managed Instance for disaster recovery or migration with minimal downtime

If a scenario mentions migrating an existing application with minimal code changes and any item above appears, the answer is Managed Instance.

When IaaS Is Forced

SQL Server on Azure VMs remains necessary when you need full control: unsupported SQL Server versions or editions, OS-level access for third-party backup agents or monitoring software, instance-level collation requirements, features like FILESTREAM with OS file access, or performance tuning at the storage and OS layer. The tradeoff is ownership — you manage patching, backups, and high availability (typically an Always On availability group across availability zones). The SQL Server IaaS Agent extension recovers some PaaS-like conveniences (automated backup, automated patching, license management) and is covered in Section 2.4.

Decision Matrix