5.1 Relational Data Storage Selection
Key Takeaways
- Azure SQL Managed Instance is the correct answer whenever a scenario mentions SQL Agent jobs, Linked Server, cross-database queries/transactions, or Database Mail — these are instance-scoped features Azure SQL Database (single database) does not support.
- Azure SQL Database (single database or elastic pool) fits new, cloud-native, single-database applications that need minimal administrative overhead and elastic scale.
- SQL Server on an Azure VM is the only IaaS option and is correct only when the workload needs OS-level access, unsupported SQL Server versions, or third-party agents that require the host OS.
- Azure Database for PostgreSQL and MySQL Flexible Server are the correct picks when the source or target engine is open-source and the team wants to preserve existing tooling and drivers.
- Azure Database for MariaDB is retired for new deployments — do not recommend it on current AZ-305 scenarios.
Why This Topic Matters
The "Design data storage solutions for relational data" functional group opens Domain 2 (20-25% of AZ-305), and its very first bullet — "Recommend a solution for storing relational data" — is the gateway decision for nearly every data-tier question you will see. AZ-305 will not ask you to write SQL; it will hand you a set of business constraints (compatibility with existing SQL Server features, migration timeline, operations budget, open-source licensing already in use, cross-database or instance-level requirements) and ask which relational engine and deployment model fits. Getting the engine choice wrong at this step cascades into every later data question — compute tier, scalability, and protection design all assume you already picked the right platform.
The Relational Options on Azure
Azure offers relational data storage across a spectrum from "you manage everything" to "Microsoft manages everything":
| Service | Management model | SQL Server compatibility | Typical use case |
|---|---|---|---|
| SQL Server on Azure VMs | Infrastructure as a Service (IaaS) — you patch the OS, manage the SQL Server instance, and control every setting | 100% — it is SQL Server | Legacy apps needing OS-level access, third-party agents, or unsupported SQL Server versions/features |
| Azure SQL Managed Instance (MI) | Platform as a Service (PaaS), but at the instance level | Near-100% — supports SQL Agent, Database Mail, Service Broker, cross-database queries/transactions, Linked Server, CLR, and native VNet integration | Lift-and-shift of on-premises SQL Server estates that rely on instance-scoped features |
| Azure SQL Database (single database / elastic pool) | PaaS at the database level | High, but no instance-scoped features (no SQL Agent, no cross-database queries, no Linked Server) | Cloud-native applications built around one database per workload; SaaS multi-tenant patterns |
| Azure Database for PostgreSQL – Flexible Server | PaaS, open-source engine | PostgreSQL wire-compatible | Existing PostgreSQL applications, open-source-first architectures |
| Azure Database for MySQL – Flexible Server | PaaS, open-source engine | MySQL wire-compatible | Existing MySQL/LAMP-stack applications |
Key term: Managed Instance is the disambiguator most candidates miss. Because SQL MI runs inside a dedicated subnet with native virtual network (VNet) integration and exposes nearly the full SQL Server surface area — Agent jobs, sp_send_dbmail, cross-database joins, distributed transactions, Transparent Data Encryption inherited from source — it is the correct answer whenever a scenario says "the application relies on SQL Agent jobs" or "queries join across multiple databases," even though it costs more than an equivalent single database.
Azure Database for MariaDB has been retired for new deployments (Azure has been steering existing customers to migrate to MySQL or PostgreSQL Flexible Server); do not recommend it for new architecture on the current exam version.
A Decision Framework
Walk through requirements in this order when a scenario asks you to pick a relational store:
- Does the workload require OS-level access, unsupported SQL Server features, or a specific patch/version pinned by a vendor? → SQL Server on an Azure VM.
- Does the application depend on instance-scoped SQL Server features (SQL Agent, Linked Server, cross-database queries/transactions, CLR, Service Broker) or need near-100% compatibility for a large migration with minimal code changes? → Azure SQL Managed Instance.
- Is this a new or refactored application, single-database in nature, needing elastic scale with minimal administrative overhead? → Azure SQL Database (single database or elastic pool — see 5.3).
- Is the existing (or target) engine open-source — PostgreSQL or MySQL — and does the team want to keep that ecosystem/tooling? → the matching Flexible Server offering.
Exam Scenarios
Scenario A: A retailer is migrating an on-premises SQL Server 2017 estate. The application uses SQL Server Agent to run nightly ETL jobs, executes cross-database queries between an orders database and an inventory database, and calls Database Mail to send alerts. Leadership wants minimal code rewrite. Answer: Azure SQL Managed Instance — it is the only PaaS option that supports Agent jobs, cross-database queries, and Database Mail without re-architecting the application.
Scenario B: A startup is building a new SaaS product from scratch. Each customer gets an isolated database, usage is unpredictable, and the team has no dedicated database administrator. Answer: Azure SQL Database, most likely pooled into an elastic pool once tenant count grows (see 5.3) — no instance-level dependency exists, so paying for Managed Instance's extra surface area and cost is unjustified.
Scenario C: An engineering team already runs a Django application against PostgreSQL on-premises and wants a lift-and-shift with minimal driver/connection-string changes. Answer: Azure Database for PostgreSQL – Flexible Server.
Common Traps
- Choosing Azure SQL Database for a scenario that mentions "SQL Agent jobs" or "linked servers" — those phrases are the exam's signal for Managed Instance, not Database.
- Recommending SQL Server on a VM just because a migration is "lift-and-shift" — PaaS Managed Instance is also a lift-and-shift target and is preferred whenever the workload doesn't need OS-level control, because it removes patching and backup administration.
- Forgetting that Managed Instance requires a dedicated subnet and cannot be dropped into an existing subnet with other resource types — this is a network design constraint the exam likes to test in combination with Domain 4 networking questions.
A company is migrating an on-premises SQL Server database that relies heavily on SQL Server Agent jobs and cross-database transactions between two databases. Which Azure relational data service should you recommend?
Which statement correctly distinguishes Azure SQL Managed Instance from Azure SQL Database (single database)?
A team is standing up a brand-new cloud-native application with a single database per tenant, unpredictable usage, and no dedicated DBA staff. Which service best fits with the lowest operational overhead?