Design data storage solutions · Topic 1 of 5
Relational Data Storage and Service Tiers
AZ-305 relational design: choose Azure SQL Database, SQL Managed Instance, SQL on VMs or PostgreSQL and MySQL, and pick vCore tiers, serverless and Hyperscale.
- 5 min
- Includes a diagram
- SQL Database is the least managed overhead. Managed Instance gives near full SQL Server compatibility. A VM gives full control.
- Tiers: General Purpose (remote storage), Business Critical (local SSD, readable secondary included) and Hyperscale (up to 128 TB, named replicas).
- Serverless exists in General Purpose and Hyperscale, and auto pauses when idle.
What it is
Azure offers several ways to run a relational database, from fully managed platform services to a VM you control. Designing means choosing the service (how much compatibility and control you need) and the tier and compute model (how much performance, availability and cost you need). The decision is driven by compatibility, scale and operational effort.
How it works
Choosing the service
| Option | Best for | Notes |
|---|---|---|
| Azure SQL Database | New cloud apps, SaaS, modern PaaS | Single database or elastic pool. Most managed, no instance level features |
| Azure SQL Managed Instance | Lift and shift of SQL Server with minimal change | Near 100 percent SQL Server compatibility: SQL Agent, cross database queries, Service Broker, linked servers |
| SQL Server on Azure VMs | Full OS and SQL control, unsupported features, specific versions | You manage patching and backups (with the SQL IaaS extension to help) |
| Azure Database for PostgreSQL flexible server | PostgreSQL apps | Zone redundant high availability, read replicas |
| Azure Database for MySQL flexible server | MySQL apps | The older single server deployment option is retired |
| Oracle Database@Azure | Oracle workloads needing Oracle features | Runs Oracle infrastructure inside Azure datacenters |
Decision shortcut: need instance level SQL Server features (SQL Agent, CLR, cross database transactions, linked servers) with little rework, choose Managed Instance. New app, want least admin, choose SQL Database. Need OS access or an unsupported feature, choose a VM.
Purchasing models and tiers for SQL Database
There are two models. vCore is the recommended one, with independent choices for compute and storage. DTU is a bundled older model with Basic, Standard and Premium tiers, and its elastic pools share eDTUs.
| vCore tier | Characteristics | Use |
|---|---|---|
| General Purpose | Remote storage (about 5 to 10 ms latency), the default and most economical, zone redundancy available | Most business apps |
| Business Critical | Local SSD storage and a cluster of replicas (about 1 to 2 ms latency), a readable secondary included at no extra charge, roughly 2.7 times the General Purpose price | Low latency, high IOPS, fast failover, OLTP |
| Hyperscale | Storage separated from compute, up to 128 TB, very fast backups and restores, up to 30 named read replicas | Very large databases and rapid scale |
Serverless compute is available in General Purpose and Hyperscale. It scales between a minimum and maximum vCore and auto pauses when idle, billing per second, which suits intermittent workloads. Provisioned compute bills a fixed hourly rate and suits steady load. A native Hyperscale database cannot move back to another tier, but a database converted to Hyperscale can reverse migrate to General Purpose within 45 days.
Elastic pools
An elastic pool shares compute among many databases with varying, unpredictable usage, which is cheaper than provisioning each database for its peak. Each database has a per database minimum and maximum. Good for SaaS with many tenant databases. Not suited when all databases peak at the same time. Pools exist in the vCore model and the DTU model.
Sizing and cost
- Start by identifying IOPS, latency, size and availability requirements.
- Reserved capacity and Azure Hybrid Benefit (use existing SQL Server licenses) lower cost for steady workloads.
- Use auto pause on serverless for dev and test.
- Hyperscale suits databases that outgrow General Purpose's size, or that need fast restore regardless of size.
- For analytics on operational data, use read replicas instead of scaling the primary.
Choosing quickly
| Requirement | Answer |
|---|---|
| Migrate an on premises SQL Server using SQL Agent jobs and cross database queries | SQL Managed Instance |
| 400 small tenant databases with uneven load | An elastic pool |
| Database that is idle at night and must cost little | Serverless General Purpose |
| 40 TB database with fast restore | Hyperscale |
| Sub 2 ms latency and a free readable replica | Business Critical |
| OS level control for a third party package | SQL Server on an Azure VM |
A worked scenario
A SaaS vendor has 300 tenant databases whose load varies by timezone, plus one 20 TB reporting database. The architect puts the tenants in a General Purpose elastic pool sized for the aggregate, and the reporting database on Hyperscale with named replicas for analysts. The vendor's older on premises accounting system needs SQL Agent and linked servers, so it moves to Managed Instance.
What to remember for the exam
- SQL Database is the least managed overhead. Managed Instance gives near full SQL Server compatibility. A VM gives full control.
- Tiers: General Purpose (remote storage), Business Critical (local SSD, readable secondary included) and Hyperscale (up to 128 TB, named replicas).
- Serverless exists in General Purpose and Hyperscale, and auto pauses when idle.
- Elastic pools suit many databases with unpredictable, non overlapping peaks.
- Azure Hybrid Benefit and reserved capacity reduce cost.
"Minimal code and configuration change from SQL Server" points to Managed Instance.
How the exam asks about this
- "Lift and shift a SQL Server with SQL Agent jobs." SQL Managed Instance.
- "Intermittent workload, pay per second, pause when idle." Serverless compute.
- "Database will grow beyond 4 TB to tens of terabytes, fast scale." Hyperscale.
- "Many databases with different peak times." An elastic pool.
Common mistakes
Choosing Managed Instance for a new simple app. SQL Database is cheaper and simpler.
Using Business Critical just for size. Hyperscale handles size.
Serverless for a constantly busy database. Provisioned costs less.
Pools when every database peaks together.
Check yourself
Which service gives near full SQL Server compatibility as PaaS?
Azure SQL Managed Instance.
Which tier has a readable secondary at no extra cost?
Business Critical.
What is the maximum Hyperscale size?
128 TB.
Topic 1 of 5 in Design data storage solutions
Test yourself on Design data storage solutions
The full Microsoft AZ-305 question bank has 755 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.