Recommend a Solution for Storing Relational Data — Lesson

Azure gives you four fundamentally different ways to run a relational database in the cloud, and the wrong choice will cost you either money, time, or both. This lesson teaches you how to pick among Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure Virtual Machines, and the open-source Azure Database for PostgreSQL / MySQL Flexible Server. We focus on the deployment-model decision only — tier sizing, horizontal scaling, and backup/DR belong to sibling LOs in this topic.

This is one of the most frequently tested design areas on the AZ-305 exam because every real architecture needs a relational store somewhere. The examiner will give you requirements — compatibility, compliance, operational cost, upgrade cadence — and expect you to pick the product that best satisfies them without over-engineering. By the end of this lesson, you should be able to make that call in under two minutes.

Why This Matters

Every line-of-business application you will design for Azure sits on top of a relational database. Choosing badly has two common failure modes. The first is over-paying for capabilities you do not need — running SQL Server Enterprise Edition on a VM cluster when a serverless Azure SQL Database would carry the workload for a tenth of the cost. The second is under-buying — picking single-database PaaS for a workload that actually depends on SQL Agent jobs, cross-database queries, or CLR assemblies, then discovering two months into migration that the platform does not support them.

For the AZ-305 exam, "relational storage" questions are usually scenario-based: Contoso has an ISV vendor requirement, Fabrikam needs a FedRAMP-compliant store, a lift-and-shift candidate has 17 databases that share a SQL Agent schedule. Each clue maps to exactly one or two correct answers. The examiner wants to see that you can trace the clue to the constraint and the constraint to the product.

In your career, getting this decision right early means your database does not become a ceiling on everything else you build. Choose the lowest-friction managed platform that can host your workload today and still hold it three years from now.

Prerequisites

• Relational database fundamentals — tables, normalization, ACID transactions, indexes. Self-check: can you explain why a primary key is different from a unique index?
• Azure resource hierarchy — management groups, subscriptions, resource groups, resources. Self-check: what happens to a resource group's child resources when you delete the group?
• IaaS vs PaaS vs SaaS shared-responsibility model — who patches the OS, who patches the database engine, who patches the application. Self-check: on Azure SQL Database, does Microsoft patch the OS?
• Basic networking in Azure — VNets, subnets, private endpoints, service endpoints. Self-check: what is the practical difference between a service endpoint and a private endpoint from a data-exfiltration standpoint?
• Familiarity with the Well-Architected Framework pillars — Reliability, Security, Cost Optimization, Operational Excellence, Performance Efficiency. Self-check: which pillar usually pushes you toward PaaS over IaaS?

Learning Objectives

1. Classify a workload against the four Azure relational-database deployment models by tracing explicit and implicit requirements.
2. Recommend one of Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure VMs, Azure Database for PostgreSQL Flexible Server, or Azure Database for MySQL Flexible Server based on compatibility, operational, and cost constraints.
3. Evaluate trade-offs between feature coverage and operational responsibility using the shared-responsibility model.
4. Design the baseline resource topology (compute boundary, networking mode, authentication) for the chosen service.
5. Justify the recommendation by mapping each decision back to a Well-Architected Framework pillar.

Building Blocks

Database engine — Think of it as the cookbook: a fixed set of features, SQL dialect quirks, and system tables that your application learned to love. Formally, a database engine is the software that parses SQL, manages storage, and enforces ACID semantics. It matters because application code is bound to a specific engine (T-SQL for SQL Server, PL/pgSQL for PostgreSQL) and a mismatch means rewrites.

Deployment model — The restaurant business model behind the cookbook: do you own the kitchen (IaaS), rent it with a head chef included (PaaS), or order meal-kits (serverless)? Formally, the deployment model determines which layer of the stack you manage (hardware, OS, engine, database) and which Microsoft manages. It matters because everything from patching cadence to SLA to pricing follows from this choice.

Shared-responsibility model — The split of security and operations duties between you and Microsoft. Analogy: renting an apartment — the landlord fixes the plumbing, you clean the dishes. Formally, a matrix that lists every operational concern (patching, backup, HA, encryption, compliance attestation) and who owns it per service. It matters because any duty you keep becomes a cost centre: a night-on-call, a CVE to patch, an audit finding to close.

Feature surface area — The set of engine features your application actually uses: SQL Agent jobs, CLR, cross-database queries, Service Broker, DTC, linked servers, full-text indexing on specific language analysers. Analogy: the length of the tail on a checklist. Formally, the delta between vanilla SQL and the ISV or legacy features your code depends on. It matters because PaaS SKUs trade breadth of features for operational simplicity.

Vendor-supported configuration — A product catalogue entry from the ISV saying "this version of our software is tested and warrantied on X, not Y". Formally, a constraint placed by a third party (SAP, Dynamics AX, EBS) on the engine version, patch level, and OS. It matters because running outside a supported configuration means the ISV will not help you when things break.

Compute boundary — Where CPU and RAM get billed and isolated. Analogy: the walls around your kitchen. For Azure SQL Database it is the logical server + database; for Managed Instance it is the virtual cluster; for VMs it is the VM itself. It matters because noisy-neighbour, maintenance windows, and scaling granularity all live at this boundary.

Management overhead — The ongoing human cost of keeping the database healthy: patch windows, index maintenance, backup verification, failover drills. Analogy: the difference between leasing a car with a maintenance plan and buying an old project car. Formally, person-hours per month across ops, DBA, and security functions. It matters because cloud cost is only part of TCO — engineer time is the rest.

Deep Dive

Azure's relational-database options are not a linear spectrum from "easy" to "powerful". They are a decision tree shaped by compatibility demands, operational appetite, and which SQL dialect your application speaks. Walk through the four branches in order.

Azure SQL Database — the default PaaS choice

Azure SQL Database is a fully managed PaaS offering of the SQL Server engine. Microsoft runs the OS, the engine, patching, backups, and HA. You manage the logical server, the databases, the users, and the firewall. It is the correct answer whenever the workload is cloud-native, built in-house, and does not require instance-scoped features.

Key capabilities you should recognise on the exam:

• Three purchasing models: DTU-based (Basic, Standard, Premium) for simple workloads, vCore-based (General Purpose, Business Critical, Hyperscale) for flexibility, and Serverless for intermittent load.
• Automatic backups with 7–35 day point-in-time restore (PITR) and optional long-term retention (LTR) up to 10 years.
• Built-in HA via zone-redundant deployment in many regions.
• Elastic pools for multi-tenant workloads that have bursty, uncorrelated load.

[!TIP] Azure SQL Database in Hyperscale tier decouples compute from storage and can grow to 100 TB. If the scenario mentions "rapidly growing database, need to scale storage independently of compute", the answer is almost always Hyperscale.

Instance-scoped features that Azure SQL Database does not support include SQL Agent, cross-database queries in the same server (you can use elastic query), Service Broker, CLR assemblies, DTC transactions, and FILESTREAM. If your scenario surfaces any of these, Azure SQL Database is the wrong choice.

Azure SQL Managed Instance — SQL Server without the server

Azure SQL Managed Instance is still PaaS, but it provides instance-level compatibility with SQL Server. It is the correct answer when the workload depends on instance features PaaS-native Azure SQL Database does not support, or when you are lifting-and-shifting an existing on-prem SQL Server estate and do not want to refactor.

Capabilities you should recognise:

• Near-complete SQL Server surface area: SQL Agent, CLR, cross-database queries, Service Broker, linked servers, DTC.
• Two service tiers: General Purpose and Business Critical. Business Critical adds read replicas and in-memory OLTP.
• Deployed inside your own VNet subnet — giving you true network isolation.
• Supports auto-failover groups for cross-region DR.

[!IMPORTANT] Managed Instance provisioning takes 4–6 hours for the first instance in a subnet and about $2.5 hours for subsequent instances. On the exam, if the scenario emphasises rapid provisioning of many small databases, Managed Instance is the wrong answer even if the features technically match.

Managed Instance's sweet spot is legacy migration. If the scenario says "lift-and-shift", "SQL Agent jobs", "cross-database queries", or "existing on-prem SQL Server", start your evaluation with MI and only step up to a VM if something breaks the MI feature list.

SQL Server on Azure Virtual Machines — IaaS escape hatch

Running SQL Server on an Azure VM is the IaaS option. You get a full Windows or Linux VM with SQL Server installed; you manage everything from the OS up. It is the correct answer when the workload needs features even Managed Instance does not cover, or when a vendor (SAP, Dynamics AX, Microsoft Dynamics GP, custom ISVs) certifies only "SQL Server on a VM" as a supported configuration.

Reasons the exam will push you to this option:

• Need a specific build of SQL Server (an older SP, a specific CU, or SQL Server 2019 when MI has already moved on).
• Need file-system access: FILESTREAM, FileTable, or PolyBase features that read local files.
• Need cluster-level features like Distributed Availability Groups that span Azure and on-prem.
• ISV or compliance requirement explicitly names "SQL Server on Windows Server" as the supported platform.
• Need full control over patching cadence — for example, a trading system that cannot accept the MI monthly window.

[!WARNING] Choosing IaaS means you own OS patching, SQL Server patching, backup management, and HA configuration. If the scenario emphasises "minimise operational overhead", the answer is never a VM even if the features match.

Use the SQL Server IaaS Agent extension in Full management mode to get automated backup and patching on top of IaaS. It closes the operational gap with PaaS without giving up feature breadth.

Azure Database for PostgreSQL / MySQL Flexible Server — open-source engines

Azure Database for PostgreSQL Flexible Server and Azure Database for MySQL Flexible Server are the managed options when the application is built on an open-source engine. They are PaaS, with most of the same operational benefits as Azure SQL Database but against a different SQL dialect.

Pick these when:

• The application is explicitly written against PostgreSQL (Rails, Django, Spring Boot with JPA dialect set, node-postgres) or MySQL (WordPress, Magento, Drupal).
• The codebase uses engine-specific features: PostGIS, pg_vector, jsonb operators, MySQL fulltext indexes.
• The team's operational skills are Linux and open-source oriented.

[!NOTE] Flexible Server replaced the older Single Server SKU for both PostgreSQL and MySQL. On the exam, prefer Flexible Server — Single Server is being retired, and choosing it signals outdated knowledge. Flexible Server offers zone-redundant HA, burstable pricing tiers, and stop/start for dev environments.

The common beginner error on the exam is picking Azure SQL Database for a PostgreSQL workload because "Azure SQL" sounds generic. The engine matters — Azure SQL Database speaks T-SQL and cannot host a PostgreSQL schema as-is.

The decision framework

A repeatable heuristic that works for the exam and production both:

1. What SQL dialect is the application written for? If PostgreSQL or MySQL, stop here — Flexible Server for that engine is the answer.
2. Does it require instance-level SQL Server features? (SQL Agent, CLR, cross-DB queries, Service Broker, DTC, FILESTREAM). If yes, Managed Instance is the floor, SQL Server on VM is the ceiling.
3. Is the workload vendor-locked to a specific SQL Server build or OS configuration? If yes, SQL Server on VM.
4. Otherwise — Azure SQL Database is the default. Pick the right tier (LO13) separately.

The more locked you are to specific features or builds, the further right on the IaaS axis you end up. The less locked, the further left toward serverless PaaS — and the cheaper and easier the operation.

Worked Examples

Example 1 — Easy: new greenfield SaaS

Problem. Contoso is building a new multi-tenant SaaS application. The code is written in .NET with Entity Framework against SQL Server. Tenants will share a schema; load is expected to be bursty with long idle periods overnight. The team wants minimum operational overhead. Recommend a database service.

Solution. Trace the clues: "new greenfield" rules out legacy compatibility concerns; "Entity Framework against SQL Server" tells us T-SQL dialect; "bursty with long idle periods" is a serverless tell; "minimum operational overhead" rules out IaaS. Because tenants share a schema and the workload is bursty, recommend Azure SQL Database in the General Purpose Serverless tier. If tenants grow uncorrelated, migrate to an elastic pool later.

[!NOTE] Serverless auto-pauses after a configurable delay (default 60 minutes of inactivity), so idle cost approaches storage-only. That is the single strongest reason to use it for dev/test and bursty production tenants.

Example 2 — Medium: lift-and-shift of legacy ERP

Problem. Fabrikam runs an on-prem ERP on SQL Server 2019 Enterprise with 43 databases. The ERP relies on SQL Agent jobs that coordinate cross-database stored procedures and a handful of CLR assemblies. The vendor supports the software on "SQL Server 2017 or later", and Fabrikam wants to stop managing Windows servers. Recommend a database service.

Solution. "Cross-database stored procedures" and "SQL Agent jobs" eliminate Azure SQL Database. "Stop managing Windows servers" eliminates SQL Server on VM. The vendor support statement is broad — it does not require a specific SQL Server build or OS. That leaves Azure SQL Managed Instance. Deploy in General Purpose tier first, reassess to Business Critical if any of the 43 databases need in-memory OLTP or read replicas (those are LO13 concerns). Put the instance in a dedicated subnet and use auto-failover groups for DR (LO15).

[!NOTE] MI supports CLR assemblies in SAFE permission set but not UNSAFE or EXTERNAL_ACCESS. Always ask the ISV or check the codebase for the permission set before you commit.

Example 3 — Hard: ISV constraint meets compliance constraint

Problem. Northwind is rolling out Dynamics AX 2012 R3, an ERP that Microsoft supports only on "SQL Server 2014 or 2016 on Windows Server 2012 R2 or 2016". The workload processes US federal contracts and must run on a FedRAMP High-authorised Azure region with all data residency in-region. The operations team is 3 people and wants the thinnest possible OS management burden. Recommend a database service and justify.

Solution. The ISV constraint locks the SQL Server build to 2014/2016 on Windows Server 2012 R2/2016. Neither Azure SQL Database nor Managed Instance offers those builds — they always run the latest engine. Open-source engines are the wrong dialect. That forces SQL Server on Azure VM as the only viable answer. FedRAMP High is met by Azure Government (US Gov regions) or Azure Commercial with proper controls — choose Azure Government for federal workloads to simplify attestation. To thin the ops burden, enable the SQL Server IaaS Agent extension in Full management mode for automated patching, backups, and licence compliance.

[!TIP] When the exam pits a "vendor-supported configuration" clue against a "minimise ops" clue, the vendor clue always wins on feasibility — if the config is not supported, the rest of the architecture does not matter. Pick the VM and then minimise ops with the IaaS Agent extension.

Visual Explanations

Decision tree.

Caption: The four-branch decision tree for Azure relational storage. Walk it top-to-bottom; never reverse a branch to save money, because you will hit a feature wall later.

Responsibility spectrum.

Caption: The four services plotted on a control-vs-management plane. Moving up reduces your operational burden; moving right gives you more feature knobs to turn.

Feature parity comparison.

Caption: Feature parity is the decisive filter — match your workload's required features to the first service that supports them all.

Purchasing models at a glance.

Caption: The purchasing model is orthogonal to the deployment-model decision — after you pick the service, you still need to pick how you pay for it.

Network placement.

Caption: Each service integrates with a VNet differently — SQL DB and PG use private endpoints, MI lives inside its own delegated subnet, and VMs are full first-class NIC residents.

TCO driver table.

Caption: Operational cost is the silent TCO driver — PaaS reclaims DBA hours that would otherwise fund HA and patching on IaaS.

Authentication and identity integration.

Caption: If the workload requires Kerberos or on-prem AD integration, Managed Instance and SQL Server on VM are your only options — PaaS cannot host a traditional Windows-auth login path.

Compliance and region coverage.

Caption: Compliance rarely forces an engine choice — it forces a region and deployment-model choice.

Common Mistakes

❌ Myth: "Azure SQL Database supports everything SQL Server does — it's just SQL Server in Azure." ✅ Reality: Azure SQL Database targets the database-scoped feature set only. SQL Agent, CLR, cross-database queries, Service Broker, and linked servers live at the instance level and are not available. Use Managed Instance if you need them. Why it's tricky: The name "Azure SQL" makes the product sound like a direct port of SQL Server. Marketing and exam distractors both exploit that.

❌ Myth: "Pick SQL Server on VM — it's the most flexible and always works." ✅ Reality: IaaS means you own OS patching, engine patching, HA, and backup verification. Engineering and security time is the hidden cost. Pick PaaS whenever the feature surface allows, and fall back to VMs only when a feature or vendor constraint forces you. Why it's tricky: Junior architects equate "more control" with "better". On cost-optimised designs, control you do not need is waste.

❌ Myth: "If the workload says SQL, pick Azure SQL Database. If it says Postgres or MySQL, pick Flexible Server. Simple." ✅ Reality: The dialect check is only step 1. A SQL Server workload with SQL Agent jobs is not an Azure SQL Database candidate. Always confirm the feature surface before committing. Why it's tricky: Dialect-matching is a useful first filter but examiners add an ISV or instance-feature clue to test whether you stop at step 1 or continue to step 2.

❌ Myth: "Managed Instance can be deployed anywhere Azure SQL Database can." ✅ Reality: Managed Instance requires a dedicated, delegated subnet and has regional rollout gaps. Always check regional availability and plan the subnet (minimum /27) before you commit. Why it's tricky: The subnet-delegation requirement is easy to miss until deployment fails. The exam tests this with networking-heavy scenarios.

Practice Exercises

1. 🟢 A startup is building a new mobile app backend in Node.js using PostgreSQL with PostGIS for geospatial queries. They want minimal operations work. Which service?

▶💡 Hint

Dialect check is decisive; confirm the extension is supported.

▶✅ Solution

Azure Database for PostgreSQL Flexible Server. PostGIS is one of the supported extensions. Flexible Server handles patching, backup, and zone-redundant HA out of the box.

1. 🟡 A financial services firm is migrating a trading system from on-prem SQL Server 2019 Enterprise. The system uses distributed transactions via MSDTC across three databases on the same instance. Which service?

▶💡 Hint

DTC availability is the pivot.

▶✅ Solution

Azure SQL Managed Instance. MI supports DTC for transactions across databases hosted on the same instance. Azure SQL Database does not. A VM works but is unnecessary if no OS-level feature is required.

1. 🟡 A SaaS vendor hosts $10{,}000$ customer databases, one per tenant. Individual tenants are idle most of the time, a few are very active. Cost is the top constraint. Which service?

▶💡 Hint

Think about pooling uncorrelated bursty load.

▶✅ Solution

Azure SQL Database with elastic pools. Pool-level eDTUs or vCores are shared across databases — you pay once for the aggregate capacity rather than provisioning for each tenant's peak.

1. 🔴 A healthcare ISV supports its EHR application only on SQL Server 2017 on Windows Server 2016. The hospital runs in a country with strict data-residency rules and wants the software supported configuration preserved. Which service?

▶💡 Hint

Which service lets you pin to a specific SQL build and OS version?

▶✅ Solution

SQL Server on Azure Virtual Machine. Windows Server 2016 with SQL Server 2017 is the exact config the ISV supports. PaaS services always run the latest engine and do not let you pin versions. Enable the SQL IaaS Agent in Full mode to automate patching inside the supported envelope.

1. 🟡 A new internal app was written against Entity Framework Core using MySQL as the provider. Expected load is low and predictable. The team wants to minimise cost. Which service?

▶💡 Hint

Dialect decides the family; tier decides the cost.

▶✅ Solution

Azure Database for MySQL Flexible Server in the Burstable (B-series) tier. Burstable tiers accrue credits during idle periods and burst when needed, which fits low-predictable-load profiles at minimum cost.

1. 🔴 Which single change would force a workload currently running on Azure SQL Database to migrate to Managed Instance? Pick from: (a) adding a new stored procedure; (b) introducing SQL Agent jobs; (c) growing past 1 TB; (d) enabling TDE.

▶💡 Hint

Database-scoped vs instance-scoped features.

▶✅ Solution

(b) SQL Agent jobs are an instance-scoped feature not available in Azure SQL Database. The other options are all supported on SQL DB.

1. 🟢 True or false: Azure SQL Managed Instance can be deployed without a VNet.

▶💡 Hint

Remember the networking prerequisite of MI.

▶✅ Solution

False. Managed Instance requires a dedicated, delegated subnet in a VNet. This is a core design point — plan the subnet before you deploy.

1. 🔴 A workload requires SQL Server Reporting Services (SSRS) alongside the database engine. Which Azure service can host both?

▶💡 Hint

SSRS is a separate server role; where can that live?

▶✅ Solution

SQL Server on Azure Virtual Machine. Neither Azure SQL Database nor Managed Instance hosts SSRS; it is a server-side workload that needs IIS and SSRS installed on a Windows Server. Alternatively, Power BI paginated reports can replace SSRS in a cloud-native redesign, but if SSRS itself is required, the answer is a VM.

Summary & Concept Map

• Azure SQL Database is the default PaaS choice for new cloud-native T-SQL workloads; pick Hyperscale for very large databases and Serverless for bursty ones.
• Azure SQL Managed Instance is the right answer when instance-level SQL Server features (SQL Agent, CLR, cross-database queries, DTC) are required but you still want a managed platform.
• SQL Server on Azure VM is the escape hatch for ISV-locked configurations, OS-version pinning, FILESTREAM, and clustered features — pair with the SQL IaaS Agent to thin the operational cost.
• Azure Database for PostgreSQL and Azure Database for MySQL Flexible Server are the managed options for open-source dialects; always prefer Flexible Server over Single Server.
• Your first filter is always dialect; your second is instance-feature requirements; your third is vendor-support constraints; only then do you look at tiers and HA.
• Every decision should map back to a Well-Architected Framework pillar — usually Cost Optimization or Operational Excellence for the deployment-model choice.