Design relational data storage
Choosing between Azure SQL Database, Managed Instance, SQL on VMs and open-source databases, service and compute tiers, scaling, and data protection.
- 18 min
- 4 concepts
- Try it yourself
- 4-question check
Learn
Azure offers relational databases from fully managed to full control. The right choice depends on compatibility with existing SQL Server features, how much management you want, and performance and scale needs.
- 1
Recommend a solution for storing relational data
Azure SQL Database: fully managed PaaS for new cloud apps (single databases or elastic pools). Azure SQL Managed Instance: near 100% SQL Server compatibility (SQL Agent, cross-database queries, CLR, linked servers) for lift-and-shift. SQL Server on Azure VMs: full OS and instance control, unsupported features, or specific versions. Azure Database for PostgreSQL and MySQL (flexible server) for open-source engines.
Remember: Needs SQL Agent and cross-database queries? Managed Instance.
- 2
Recommend a database service tier and compute tier
Purchasing models: vCore (recommended: choose cores, memory and storage) or DTU (bundled). vCore tiers: General Purpose (balanced), Business Critical (local SSD, highest resilience, a free readable replica, in-memory OLTP) and Hyperscale (up to 128 TB, fast scaling, named replicas). Compute: provisioned or serverless (auto-scales and auto-pauses, for intermittent use). Elastic pools share resources between many databases with varying load.
Remember: Lots of small databases with spiky use: elastic pool.
- 3
Recommend a solution for database scalability
Scale up by changing tier or vCores. Read scale-out uses replicas for read-only queries (Business Critical, Hyperscale). Hyperscale adds named replicas and grows storage automatically. Sharding splits data across databases with the elastic database tools. Serverless scales compute automatically within limits.
Remember: Read-heavy reporting: read scale-out replicas.
- 4
Recommend a solution for data protection
Transparent data encryption (on by default, optionally with customer-managed keys) protects data at rest. Always Encrypted protects sensitive columns even from DBAs. Dynamic data masking hides data in query results. Microsoft Entra authentication, private endpoints, auditing and Defender for SQL complete the picture. Ledger tables give tamper-evidence.
Remember: Hide data from DBAs: Always Encrypted.
Which SQL option?
| Option | Management | Compatibility | Best for |
|---|---|---|---|
| Azure SQL Database | Fully managed | Most features, per database | New cloud apps |
| SQL Managed Instance | Fully managed | Near 100% SQL Server | Lift and shift with instance features |
| SQL Server on VMs | You manage OS and SQL | Full | Unsupported features or OS access |
Try it
Pick the relational database.
This interactive part needs JavaScript.
In the portal
- Azure SQLAzure portal: Azure SQL > Create
- Compute and storageAzure portal: SQL databases > your database > Settings > Compute + storage
Key terms
Click a card to see the definition.
Check your understanding
4 questions. Score 80% or more and these skills are marked as confident in your AZ-305 study plan.
An app needs SQL Agent jobs and cross-database queries, fully managed. Which service?
Managed Instance has instance-level features.
Which compute tier auto-pauses when idle?
Serverless auto-pauses and resumes.
Which protects data even from database administrators?
Keys stay with the client.
Which tier includes a readable secondary replica at no extra cost?
Business Critical supports read scale-out.
Sources
- Microsoft Learn: What is Azure SQL?
- Microsoft Learn: vCore purchasing model
- Microsoft Learn: Hyperscale service tier
- Microsoft Learn: Serverless compute tier
- Microsoft Learn: Always Encrypted
Facts in this lesson are from Microsoft Learn. This site isn't affiliated with Microsoft.