azureblog.co.uk
← AZ-305 study plan
AZ-305 · Design data storage solutions

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. 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. 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. 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. 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?

OptionManagementCompatibilityBest for
Azure SQL DatabaseFully managedMost features, per databaseNew cloud apps
SQL Managed InstanceFully managedNear 100% SQL ServerLift and shift with instance features
SQL Server on VMsYou manage OS and SQLFullUnsupported 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.

  1. An app needs SQL Agent jobs and cross-database queries, fully managed. Which service?

  2. Which compute tier auto-pauses when idle?

  3. Which protects data even from database administrators?

  4. Which tier includes a readable secondary replica at no extra cost?

Sources

Facts in this lesson are from Microsoft Learn. This site isn't affiliated with Microsoft.