This chapter covers the Azure SQL family of relational database services: Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs. You will learn how each service differs in terms of management overhead, scalability, high availability, and pricing models. For the DP-900 exam, approximately 15–20% of questions relate to Azure relational data services, and understanding the trade-offs between these three offerings is critical for scenario-based questions. By the end, you will be able to match business requirements to the correct Azure SQL service.
Jump to a section
Imagine Azure SQL Database as a restaurant chain where you order a meal (database) and the chain handles all the cooking, cleaning, and maintenance. You don't own the kitchen or hire the chefs—you just specify your menu (schema) and how many customers you expect (DTU/vCore). The chain's central kitchen (Azure infrastructure) automatically scales stoves and ovens (compute/storage) based on demand. In contrast, SQL Managed Instance is like a franchise: you get your own dedicated kitchen with a fixed layout (always-on SQL Server instance) but the chain still handles repairs and upgrades. Azure SQL on VMs is like buying a restaurant building and hiring your own staff—you control everything but must manage updates, backups, and scaling yourself. The chain's pricing model: you pay per meal (serverless) or a monthly subscription (provisioned). If you overestimate demand, you waste money; underestimate, customers wait (performance throttling). The DP-900 exam tests which service fits different business needs—like choosing between a caterer (SQL Database), a franchise (Managed Instance), or a DIY kitchen (SQL on VMs).
What Are Azure SQL Services?
Azure SQL services are fully managed or partially managed relational database solutions based on Microsoft SQL Server engine, hosted in Microsoft Azure. They provide built-in high availability, automated backups, and security features. The three main offerings are: - Azure SQL Database: Platform as a Service (PaaS) – single database or elastic pool. - Azure SQL Managed Instance: PaaS with near 100% compatibility with on-premises SQL Server, including SQL Server Agent, linked servers, and cross-database queries. - SQL Server on Azure VMs: Infrastructure as a Service (IaaS) – you manage the SQL Server instance and OS; Azure manages the host hardware.
Why They Exist
Organizations migrating from on-premises SQL Server to the cloud need options that balance compatibility, control, and management overhead. Azure SQL Database minimizes administrative effort but has some feature limitations (e.g., no SQL Agent, no cross-database queries). SQL Managed Instance removes those limitations while still being PaaS. SQL on VMs provides full control but requires you to handle patching, backups, and high availability configuration.
How They Work Internally
Azure SQL Database: Each database is a fully isolated unit. Compute and storage are separated. The database engine runs on a cluster of nodes; if the primary node fails, the system automatically fails over to a secondary node without data loss (using Always On Availability Groups internally). Storage is backed by Azure Premium SSD or Ultra Disk, with built-in geo-replication options (Active Geo-Replication and Auto-Failover Groups). - Compute tiers: Provisioned (reserved resources) or Serverless (auto-scale compute based on workload, with a pause feature after 1 hour of inactivity). - Service tiers: General Purpose (balanced compute/storage), Business Critical (low-latency, high resiliency with multiple replicas), and Hyperscale (up to 100 TB storage, fast scaling).
SQL Managed Instance: Deployed within a dedicated subnet in your virtual network. It uses a similar architecture to SQL Database but includes a full SQL Server instance, enabling features like SQL Agent, CLR, Service Broker, and linked servers. Instances are automatically patched and backed up. The instance has a fixed size (e.g., 4 vCores, 16 GB memory) and supports up to 100 databases per instance. Failover is handled by a built-in cluster.
SQL Server on Azure VMs: You select a VM size (e.g., Standard_E8s_v3) and install SQL Server. You are responsible for SQL Server licensing, patching, backups, and high availability (e.g., configuring Always On Availability Groups). Azure provides managed disks and automatic backup to Azure Blob Storage if you enable it. This option is best for lift-and-shift migrations where you need full control.
Key Components, Values, and Defaults
DTU vs vCore: DTU (Database Transaction Unit) is a bundled measure of compute, storage, and I/O. vCore allows you to choose compute and storage separately. For DP-900, know that DTU is simpler but less flexible; vCore is used in General Purpose, Business Critical, and Hyperscale tiers.
Backup retention: Default 7 days for point-in-time restore, configurable up to 35 days. Long-term retention (LTR) up to 10 years.
Geo-replication: Active Geo-Replication creates up to 4 readable secondaries in different regions. Auto-Failover Groups allow automatic failover to a secondary region.
Elastic pools: Shared resources among multiple databases with a single price. Useful for databases with variable usage patterns.
Serverless: Auto-pause after 1 hour of inactivity (configurable). Compute is scaled automatically between min and max vCores.
Configuration and Verification Commands
Using Azure CLI:
# Create SQL Database server
az sql server create --name mydbserver --resource-group myrg --location eastus --admin-user admin --admin-password P@ssw0rd1234
# Create a database
az sql db create --resource-group myrg --server mydbserver --name mydb --service-objective S0
# List databases
az sql db list --resource-group myrg --server mydbserver --output tableUsing PowerShell:
New-AzSqlDatabase -ResourceGroupName myrg -ServerName mydbserver -DatabaseName mydb -Edition Standard -RequestedServiceObjectiveName S0In the Azure portal, you can monitor DTU/vCore usage, storage, and IOPS via Metrics blade.
How They Interact with Related Technologies
Azure Active Directory: Supports AAD authentication for SQL Database and Managed Instance.
Azure SQL Data Sync: Allows bi-directional sync between databases (for hybrid scenarios).
Azure Logic Apps: Can connect to SQL databases for ETL.
Azure Data Factory: For data movement and transformation.
Azure Purview: For data cataloging and governance.
Trap Patterns for the Exam
Trap: "SQL Database supports SQL Agent." Reality: SQL Database does not support SQL Agent; use Elastic Jobs or Azure Automation. Managed Instance does support SQL Agent.
Trap: "Managed Instance is always cheaper than SQL Database." Reality: Managed Instance is often more expensive due to dedicated resources; it's chosen for compatibility, not cost.
Trap: "SQL on VMs is fully managed." Reality: Only the VM host is managed; you manage SQL Server itself.
Trap: "Hyperscale tier is available for Managed Instance." Reality: Hyperscale is only for SQL Database, not Managed Instance.
Exam-Relevant Numbers
Maximum database size in General Purpose: 4 TB; Business Critical: 4 TB; Hyperscale: 100 TB.
Managed Instance: up to 100 databases per instance.
Backup retention: 7 days default, max 35 days.
Serverless auto-pause delay: 1 hour default (can be set from 10 minutes to 24 hours).
DTU tiers: Basic (5 DTU), Standard (S0-S12, 10-3000 DTU), Premium (P1-P15, 125-4000 DTU).
Choose the Right Azure SQL Service
Evaluate your application's compatibility requirements. If you need full SQL Server features (Agent, CLR, cross-database queries) and want PaaS, choose SQL Managed Instance. If you need the lowest management overhead and can accept some feature limitations, choose Azure SQL Database. If you need full control over SQL Server configuration and OS, choose SQL Server on Azure VMs. Also consider licensing: with SQL Database and Managed Instance, you pay per vCore with included SQL license; with SQL on VMs, you provide your own license or pay per minute.
Select Compute and Storage Tier
For SQL Database, choose between DTU and vCore purchasing model. DTU is simpler for small workloads; vCore offers flexibility. Then pick a service tier: General Purpose (balanced), Business Critical (low latency, high resilience), or Hyperscale (large databases). For Managed Instance, only vCore is available, with General Purpose and Business Critical tiers. For SQL on VMs, choose VM size (e.g., E series for memory-optimized) and configure storage (Premium SSD or Ultra Disk).
Configure High Availability and Disaster Recovery
For SQL Database, by default you get local redundancy with automatic failover within the same region (99.99% SLA). For cross-region DR, enable Active Geo-Replication or Auto-Failover Groups. Managed Instance uses a built-in Always On group; for DR, you can set up a failover group to another region. SQL on VMs requires you to manually configure Always On Availability Groups or use Azure Site Recovery. The exam tests that SQL Database and Managed Instance provide built-in HA without extra configuration.
Set Up Security and Networking
For SQL Database and Managed Instance, configure firewall rules at the server level (IP ranges) and enable Azure Services access. Use Azure Active Directory authentication for managed identities. For Managed Instance, since it's deployed in a VNet, you must configure NSG rules and route tables. SQL on VMs requires typical VM security (NSGs, network ACLs). All services support Transparent Data Encryption (TDE) and Always Encrypted at the column level.
Monitor and Scale
Use Azure Monitor and SQL Insights to track DTU/vCore usage, storage, and query performance. For SQL Database, scaling can be done manually or via autoscale (only in Serverless). Elastic pools allow scaling across multiple databases. Managed Instance scaling requires changing the instance tier (downtime). SQL on VMs scaling means resizing the VM (requires restart). The exam asks about scaling behavior: SQL Database can scale with minimal downtime; Managed Instance incurs downtime; SQL on VMs requires restart.
Enterprise Scenario 1: SaaS Application with Multi-Tenant Databases
A SaaS company provides a CRM application to hundreds of small businesses. Each customer gets their own database. The workload is unpredictable — some customers are active during business hours, others at night. The company chooses Azure SQL Database with elastic pools. They create a pool with a total of 1000 eDTUs and add 200 databases. As customers grow, they can move databases to a larger pool. They use Serverless for small customers to save costs when not in use. The auto-pause feature pauses a database after 1 hour of inactivity, which reduces compute costs by up to 80% for low-usage customers. They also enable geo-replication for disaster recovery. What goes wrong? If they misconfigure the elastic pool minimum eDTU per database, one noisy tenant can consume all resources, starving others. They need to set a per-database min and max eDTU to guarantee performance isolation.
Enterprise Scenario 2: Lift-and-Shift Migration of Legacy ERP
A manufacturing company runs an on-premises SQL Server 2016 ERP system that uses SQL Server Agent jobs, linked servers to other databases, and CLR stored procedures. They want to migrate to Azure with minimal code changes. They choose Azure SQL Managed Instance because it offers near 100% compatibility. They create a Managed Instance with 16 vCores in a Business Critical tier for low latency. They use Azure Database Migration Service to perform an online migration. What goes wrong? If they forget to configure the VNet peering correctly, the application cannot connect. Also, the instance size cannot be changed without downtime, so they must carefully size it initially. They also need to ensure the source database is compatible — for example, if they use FileStream, Managed Instance supports it, but with some limitations.
Enterprise Scenario 3: Regulatory Compliance with Full Control
A financial institution requires complete control over SQL Server configuration, including custom trace flags, startup parameters, and a specific version of SQL Server (e.g., SQL Server 2019 with specific CU). They also need to install third-party monitoring agents on the OS. They choose SQL Server on Azure VMs. They provision a VM with 32 vCores, 256 GB RAM, and Premium SSD storage. They install SQL Server using a pre-configured Azure Marketplace image. They configure Always On Availability Groups for high availability using two VMs in different availability zones. What goes wrong? If they do not set up automatic patching, they risk security vulnerabilities. Also, if they choose the wrong VM size (e.g., D-series instead of E-series), they may get insufficient memory-to-core ratio, causing performance issues. They must also manage backup files themselves — they automate backups to Azure Blob Storage using Ola Hallengren scripts.
Exactly What DP-900 Tests
Domain: Relational Data (Objective 2.1). The exam expects you to: - Describe the differences between Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs in terms of management, compatibility, and scalability. - Identify scenarios where each service is appropriate. - Understand purchasing models: DTU vs vCore. - Know service tiers: General Purpose, Business Critical, Hyperscale (for SQL Database only). - Recognize high availability and disaster recovery options: geo-replication, failover groups. - Understand elastic pools and serverless compute.
Top 3 Wrong Answers and Why Candidates Choose Them
"SQL Database supports SQL Server Agent." Wrong because candidates confuse SQL Database with Managed Instance. They see "SQL" and assume all features are present. Reality: SQL Database does not have SQL Agent; use Elastic Jobs or Logic Apps.
"Managed Instance is cheaper than SQL Database." Wrong because candidates think PaaS is always cheaper. Managed Instance has a higher base cost due to dedicated resources; it's for compatibility, not cost savings.
"SQL on VMs is fully managed." Wrong because candidates see "Azure" and think everything is managed. Reality: only the VM host is managed; you manage SQL Server.
Specific Numbers and Terms That Appear Verbatim
99.99% SLA for SQL Database and Managed Instance (with appropriate tiers).
35 days max backup retention.
1 hour default serverless auto-pause delay.
100 TB maximum database size for Hyperscale.
4 TB for General Purpose and Business Critical.
DTU tiers: Basic (5 DTU), Standard (S0-S12), Premium (P1-P15).
Edge Cases and Exceptions
Hyperscale is only for SQL Database, not Managed Instance.
SQL Database can only have one database per logical server? No, a logical server can host many databases. But each database is isolated.
Managed Instance supports multiple databases per instance (up to 100).
Serverless compute is only available in General Purpose tier of SQL Database.
Auto-failover groups require at least one secondary region.
How to Eliminate Wrong Answers Using Underlying Mechanism
When you see a question about migrating an on-premises SQL Server that uses SQL Agent, cross-database queries, and linked servers, eliminate SQL Database immediately because it lacks those features. If the question emphasizes minimal management and the application is compatible with SQL Database limitations, choose SQL Database. If the question mentions full control over SQL Server version or OS, choose SQL on VMs. For cost-related questions, remember that SQL Database elastic pools are best for variable multi-tenant workloads, while Managed Instance is for compatibility-heavy migrations.
Azure SQL Database is PaaS with limited features; SQL Managed Instance is PaaS with full SQL Server compatibility; SQL on VMs is IaaS with full control.
SQL Database supports DTU and vCore purchasing models; Managed Instance and SQL on VMs use vCore only.
Service tiers: General Purpose (balanced), Business Critical (low latency, high resilience), Hyperscale (up to 100 TB, SQL Database only).
Default backup retention is 7 days, configurable to 35 days; long-term retention up to 10 years.
Serverless compute auto-pauses after 1 hour of inactivity (configurable).
Elastic pools allow sharing resources among multiple databases in SQL Database.
Geo-replication and auto-failover groups are available for SQL Database and Managed Instance.
SQL on VMs requires you to manage high availability (e.g., Always On Availability Groups).
Managed Instance supports up to 100 databases per instance.
Hyperscale tier is NOT available for Managed Instance or SQL on VMs.
These come up on the exam all the time. Here's how to tell them apart.
Azure SQL Database
PaaS, fully managed by Azure.
Supports single database and elastic pools.
Limited compatibility: no SQL Agent, no cross-database queries, no linked servers.
Scaling is elastic with minimal downtime.
Pricing: DTU or vCore, pay per database or pool.
Azure SQL Managed Instance
PaaS with near-100% SQL Server compatibility.
Instance-scoped features: SQL Agent, CLR, linked servers, cross-database queries.
Deployed in a dedicated VNet subnet.
Scaling requires changing the instance tier (some downtime).
Pricing: vCore only, pay per instance (up to 100 databases included).
Azure SQL Database
No OS access; fully managed.
Automatic backups, patching, HA.
Limited to SQL Database features.
Scale compute and storage independently.
Ideal for new cloud applications.
SQL Server on Azure VMs
Full OS and SQL Server control.
You manage backups, patching, HA.
Full SQL Server feature set (any version).
Scale via VM size change (requires restart).
Ideal for lift-and-shift with minimal changes.
Azure SQL Managed Instance
PaaS – no OS management.
Automatic backups, patching, HA built-in.
Near-full SQL Server compatibility.
Fixed instance size, scaling with downtime.
Pricing includes SQL license.
SQL Server on Azure VMs
IaaS – full OS and SQL management.
You configure backups, patching, HA.
Full SQL Server compatibility (any version).
Flexible VM sizing, but restart required.
Bring your own SQL license or pay-as-you-go.
Mistake
Azure SQL Database is just SQL Server hosted in Azure.
Correct
Azure SQL Database is a PaaS service with a different engine (always the latest SQL Server version) but lacks some features like SQL Agent, CLR, and cross-database queries. It is not the same as installing SQL Server on a VM.
Mistake
SQL Managed Instance is the same as SQL Database but with a different name.
Correct
Managed Instance provides near-full SQL Server compatibility including instance-scoped features (SQL Agent, linked servers, Service Broker). It runs in a dedicated VNet and has a fixed instance size, unlike the elastic nature of SQL Database.
Mistake
SQL Server on Azure VMs is fully managed by Azure.
Correct
Only the virtual machine host is managed. You are responsible for SQL Server licensing, patching, backups, and high availability configuration. Azure provides managed disks and optional automated backup, but you must enable it.
Mistake
Hyperscale tier is available for all Azure SQL services.
Correct
Hyperscale is only available for Azure SQL Database (single database). It is not available for Managed Instance or SQL on VMs.
Mistake
Elastic pools are only for SQL Database, not Managed Instance.
Correct
Correct. Elastic pools are a feature of Azure SQL Database that allows sharing resources among multiple databases. Managed Instance does not support elastic pools; each instance has fixed resources.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Azure SQL Database is a PaaS offering that provides a single database or elastic pool. It lacks some instance-level features like SQL Server Agent, linked servers, and cross-database queries. SQL Managed Instance is also PaaS but provides near-100% compatibility with on-premises SQL Server, including SQL Agent, CLR, and linked servers. Managed Instance runs in a dedicated VNet and has a fixed instance size, while SQL Database can be scaled more elastically. For the exam, remember that Managed Instance is for when you need full SQL Server features but want PaaS management.
Choose SQL Server on Azure VMs when you need full control over the operating system and SQL Server configuration, such as custom trace flags, specific SQL Server versions (e.g., 2016, 2019), or third-party monitoring agents. Also choose VMs if you require features not available in Managed Instance, like FileStream with certain configurations or if you need to run multiple instances on the same VM. However, you must manage backups, patching, and high availability yourself. The exam tests that VMs are for lift-and-shift scenarios where you cannot change the application.
Hyperscale is a service tier for Azure SQL Database that supports up to 100 TB of storage and provides fast scaling of compute and storage. It is designed for very large databases (over 4 TB) and applications that need rapid scaling, such as large OLTP or data warehousing workloads. Hyperscale uses a layered architecture with multiple nodes for compute and storage. It is only available for single databases, not Managed Instance or elastic pools. On the exam, know that Hyperscale is for extremely large databases in SQL Database.
DTU (Database Transaction Unit) is a bundled measure of compute, storage, and I/O. It is simpler and used for Basic, Standard, and Premium tiers. vCore allows you to choose compute and storage separately, offering more flexibility. vCore is used in General Purpose, Business Critical, and Hyperscale tiers. DTU is best for small workloads where you want simplicity; vCore is for workloads where you need to control resources independently. The exam expects you to know that DTU is not available for Managed Instance or SQL on VMs.
Azure SQL Database provides built-in high availability with a 99.99% SLA (for appropriate tiers). It internally uses Always On Availability Groups across multiple nodes in the same region. If the primary node fails, failover occurs automatically with no data loss. For disaster recovery across regions, you can configure Active Geo-Replication (up to 4 readable secondaries) or Auto-Failover Groups. The exam tests that you do not need to configure anything for local HA; it's automatic.
No, Azure SQL Database does not support SQL Server Agent. For scheduling jobs, you can use Elastic Jobs (for SQL Database), Azure Automation, or Azure Logic Apps. SQL Managed Instance does support SQL Server Agent. This is a common exam trap: candidates assume SQL Database has all SQL Server features, but it doesn't.
An elastic pool is a set of shared resources (eDTUs or vCores) allocated to a group of databases in Azure SQL Database. It is ideal for multi-tenant applications where each tenant has a small database with variable usage. You set a minimum and maximum DTU/vCore per database, and the pool automatically distributes resources. This is more cost-effective than provisioning separate databases with dedicated resources. Elastic pools are not available for Managed Instance or SQL on VMs.
You've just covered Azure SQL Services — now see how well it sticks with free DP-900 practice questions. Full explanations included, no account needed.
Done with this chapter?