Choosing Among Azure SQL Family Products
Why Is This Important?
One of the most frequently tested areas on the DP-900 (Microsoft Azure Data Fundamentals) exam is the ability to distinguish between the various Azure SQL family products and recommend the right one for a given scenario. Microsoft's Azure platform offers multiple relational database services built on SQL Server technology, and understanding the differences — in terms of deployment model, management overhead, compatibility, pricing, and use cases — is critical not only for the exam but also for real-world cloud architecture decisions.
Choosing the wrong product can lead to unnecessary costs, management complexity, or missing features. The exam tests whether you can match business and technical requirements to the correct Azure SQL offering.
What Are the Azure SQL Family Products?
The Azure SQL family consists of three primary products:
1. Azure SQL Database
Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database engine. It is based on the latest stable version of SQL Server and is managed by Microsoft, meaning you do not need to worry about patching, backups, or high availability — these are handled automatically.
Key characteristics:
- PaaS offering — minimal administrative overhead
- Available in Single Database and Elastic Pool deployment options
- Single Database: one isolated database with its own set of resources
- Elastic Pool: multiple databases sharing a pool of resources, ideal for multi-tenant SaaS applications with variable usage patterns
- Supports serverless compute tier that auto-scales and auto-pauses for cost savings
- Built-in intelligent performance features (automatic tuning, threat detection)
- Best for modern cloud-born applications that need a single, managed database
- Does not support all SQL Server features (e.g., SQL Server Agent, cross-database queries without Elastic Query, certain CLR features)
2. Azure SQL Managed Instance
Azure SQL Managed Instance is also a PaaS offering but provides near 100% compatibility with the on-premises SQL Server engine. It is designed for customers who want to migrate existing on-premises SQL Server workloads to Azure with minimal changes.
Key characteristics:
- PaaS offering with high compatibility with on-premises SQL Server
- Supports features not available in Azure SQL Database such as SQL Server Agent, cross-database queries, Service Broker, CLR, Database Mail, and linked servers
- Deployed within a virtual network (VNet), providing network isolation
- Supports instance-scoped features (multiple databases per instance)
- Ideal for lift-and-shift migrations from on-premises SQL Server
- Automated backups, patching, and high availability
- More expensive than Azure SQL Database for equivalent workloads but offers broader feature compatibility
3. SQL Server on Azure Virtual Machines (IaaS)
SQL Server on Azure VMs is an Infrastructure-as-a-Service (IaaS) offering. You get full control over the SQL Server instance and the underlying operating system. Microsoft provides the VM infrastructure, but you are responsible for managing SQL Server, OS patching, backups, and high availability.
Key characteristics:
- IaaS offering — full control, full responsibility
- 100% feature compatibility with on-premises SQL Server (it is SQL Server, just running on an Azure VM)
- You choose the SQL Server version and edition
- Best for workloads that require OS-level access, specific SQL Server versions, or features not supported in PaaS options (e.g., SSIS installed locally, third-party software on the same server, failover cluster instances)
- Supports BYOL (Bring Your Own License) through Azure Hybrid Benefit
- You manage patching, backups, high availability, and disaster recovery (though Azure provides tools to help)
- Ideal when you need maximum control or have strict compliance/regulatory requirements dictating OS-level access
How to Choose: Decision Framework
When deciding among these three products, consider the following factors:
A. Compatibility Requirements
- If the application uses SQL Server-specific features like SQL Agent, Service Broker, CLR, or cross-database queries → Azure SQL Managed Instance
- If the application requires a specific SQL Server version, OS-level access, or third-party software on the same machine → SQL Server on Azure VM
- If the application is newly developed or uses only standard T-SQL and does not depend on instance-level features → Azure SQL Database
B. Management Overhead Preference
- Least management: Azure SQL Database (fully managed, automatic everything)
- Moderate management: Azure SQL Managed Instance (mostly managed, some configuration needed)
- Most management: SQL Server on Azure VM (you manage the OS and SQL Server)
C. Migration Scenario
- Lift-and-shift of existing SQL Server with minimal code changes → Azure SQL Managed Instance or SQL Server on Azure VM
- Building a new cloud-native application → Azure SQL Database
- Need to replicate the exact on-premises environment → SQL Server on Azure VM
D. Cost Considerations
- Variable or unpredictable workloads → Azure SQL Database Serverless
- Multiple databases with varying usage → Azure SQL Database Elastic Pool
- Existing SQL Server licenses → Azure Hybrid Benefit on any of the three products
E. Network Isolation
- Requires VNet integration natively → Azure SQL Managed Instance (deployed inside a VNet by default)
- SQL Server on Azure VM is also inside a VNet
- Azure SQL Database supports Private Link and VNet service endpoints but is not deployed inside a VNet
How It Works in Practice
Imagine a company currently running SQL Server 2016 on-premises with 15 databases that use cross-database queries and SQL Server Agent jobs. They want to move to Azure with minimal refactoring:
→ The best choice is Azure SQL Managed Instance because it supports cross-database queries, SQL Agent, and multiple databases per instance, closely matching their on-premises setup.
Now imagine a startup building a brand-new SaaS application that needs a single relational database with automatic scaling:
→ The best choice is Azure SQL Database (Single Database or Serverless) because it offers the lowest management overhead and supports modern cloud application patterns.
Finally, imagine a company that needs to run a legacy application that requires SQL Server 2012 and a specific third-party backup tool installed on the same server:
→ The best choice is SQL Server on Azure VM because it provides OS-level access and supports older SQL Server versions.
Summary Comparison Table
Azure SQL Database: PaaS | Lowest admin overhead | Best for new cloud apps | Limited SQL Server feature set
Azure SQL Managed Instance: PaaS | Moderate admin overhead | Best for lift-and-shift | Near 100% SQL Server compatibility
SQL Server on Azure VM: IaaS | Highest admin overhead | Best for full control/legacy apps | 100% SQL Server compatibility
Exam Tips: Answering Questions on Choosing Among Azure SQL Family Products
1. Look for keywords in scenarios: If the question mentions "lift-and-shift," "minimal code changes," "SQL Agent jobs," "cross-database queries," or "Service Broker," the answer is almost always Azure SQL Managed Instance.
2. "Fully managed" and "new application" = Azure SQL Database: If the scenario describes building a new application with the least administrative effort, choose Azure SQL Database.
3. "Full control," "OS-level access," or "specific SQL Server version" = SQL Server on Azure VM: When the question mentions needing to install custom software, access the operating system, or use a specific older version of SQL Server, the answer is SQL Server on Azure VM.
4. Understand PaaS vs. IaaS: The exam frequently tests whether you know that Azure SQL Database and Azure SQL Managed Instance are PaaS, while SQL Server on Azure VM is IaaS. PaaS means Microsoft handles patching, backups, and HA. IaaS means you handle it.
5. Elastic Pool questions: If the scenario involves multiple databases with unpredictable or variable workloads and cost optimization is a concern, think Elastic Pool (a deployment option within Azure SQL Database).
6. Serverless questions: If the scenario describes a database that is used intermittently or has long idle periods, think Azure SQL Database Serverless tier, which auto-pauses and auto-scales.
7. Don't confuse Managed Instance with SQL on VM: Both support many SQL Server features, but Managed Instance is PaaS (Microsoft manages infrastructure), while SQL on VM is IaaS (you manage everything). If the question emphasizes reduced management with broad compatibility, choose Managed Instance.
8. Azure Hybrid Benefit: Remember that existing SQL Server licenses can be reused across all three products through Azure Hybrid Benefit, but the exam may test whether you know this applies to reduce costs.
9. Network isolation: If a question specifically mentions deploying within a virtual network, both Managed Instance and SQL on VM natively reside in a VNet. Azure SQL Database uses Private Link or service endpoints for VNet connectivity.
10. Elimination strategy: When in doubt, eliminate options based on the management model first (PaaS vs. IaaS), then narrow down based on feature requirements. This two-step approach helps you arrive at the correct answer quickly.
By understanding the unique characteristics, use cases, and limitations of each Azure SQL family product, you will be well-prepared to answer scenario-based questions on the DP-900 exam confidently and accurately.
