Design data storage solutions
Relational Data Platform and Tier Design
CoreChoose relational services, compute models, scaling patterns, and protection mechanisms from compatibility, operational, performance, and recovery requirements.
Aligned to the current AZ-305 study guide, skills measured as of April 17, 2026, verified August 25, 2026.
Why this matters
The best database design is rarely the service with the most features; it is the managed boundary that satisfies engine compatibility, workload shape, isolation, recovery, and cost constraints.
Must Know
- Select SQL Database, SQL Managed Instance, or SQL Server on Azure VMs by required engine compatibility and desired operational control.
- Fit the compute model to demand: steady workloads favor provisioned capacity, intermittent eligible databases may favor serverless, and variable databases can share an elastic pool.
- Address read scaling, data distribution, and compute sizing as separate architecture decisions.
- Choose backups, zone resilience, geo-replication, and failover features according to the specific failure and recovery objective.
Compare and Distinguish
- Azure SQL Database favors cloud-native managed databases; Azure SQL Managed Instance offers broader instance compatibility; SQL Server on Azure VMs preserves operating-system and instance control.
- Provisioned compute fits steady demand; serverless compute can fit intermittent eligible databases; elastic pools share provisioned resources across variable databases.
- Read scale addresses read workload; partitioning or sharding changes data distribution; scaling compute does not replace a data-architecture decision.
Scenario examples
- Scenario: Many small tenant databases peak at different times. Think: evaluate an elastic pool.
- Scenario: A legacy application requires SQL Agent and cross-database instance behavior with minimal change. Think: evaluate Managed Instance before a VM.
- Scenario: A business needs controlled regional failover for several databases. Think: design failover groups and application connection behavior.
Exam traps
- A read replica does not make writes horizontally scalable.
- Backups alone do not meet a near-zero regional RTO.
- The lowest-cost tier may fail latency or availability requirements.
Key takeaways
- Start with engine and instance compatibility, then operational responsibility.
- Treat compute, data distribution, availability, and recovery as separate levers.
- Design the client connection and failover behavior, not only the database resource.
How it works
- Azure SQL Database and Managed Instance place databases on service-managed infrastructure with built-in replica and failover behavior determined by the selected service and tier; SQL Server on Azure VMs leaves more of that orchestration to the customer.
- Serverless compute adjusts capacity within configured limits and can pause eligible databases after inactivity, while an elastic pool lets member databases draw from a shared resource allocation as their demand changes.
- Geo-replication sends committed changes asynchronously to readable secondary databases, and a failover group adds coordinated failover plus listener endpoints that can redirect application connections.
Objects and administrative surfaces
- Logical server and managed-instance boundaries affect networking, administration, and feature compatibility.
- Service tier and compute tier determine performance architecture, availability characteristics, and cost.
- Backups, zone redundancy, failover groups, and geo-replication address different failure and recovery scopes.
When to use it
- Use SQL Database for database-scoped cloud applications, Managed Instance for broad instance compatibility, and SQL Server on VMs for hard guest requirements.
- Use serverless for an eligible intermittent database, an elastic pool for noncoincident database peaks, and provisioned capacity for steady latency-sensitive demand.
- Combine service-managed backups with database-native regional continuity when both point-in-time recovery and rapid failover are required.
Security and governance implications
- Keep database authentication, Azure resource authorization, SQL object grants, network access, and key custody as separate boundaries.
- If customer-managed keys are required, design key access, rotation, availability, and recovery alongside the database service.
How to validate and revise the design
- For poor performance, compare compute saturation, query shape, waits, locks, read/write mix, pool contention, and distribution before changing tiers.
- For failed recovery, verify backup retention, replica health, failover endpoint behavior, client retries, DNS, identity, and dependent services.
More detail
- Feature compatibility is evaluated at the database or instance boundary, not just by SQL syntax; cross-database behavior, agent jobs, network topology, and administrative control can determine the service choice.
- Scaling compute can interrupt existing connections and does not repartition data, so applications need transient-fault handling and a separate plan when the bottleneck is data distribution.
- Because geo-replication is asynchronous, regional failover can expose an RPO greater than zero; the answer must account for data-loss tolerance as well as endpoint recovery time.
Ready for the quiz?
- Which engine and instance features are mandatory, and how much guest or platform control can the operating model accept?
- Does the demand pattern call for provisioned capacity, eligible serverless behavior, an elastic pool, read scale, or data distribution?
- Which failure and recovery objective requires backups, zone resilience, geo-replication, or a failover group?
Related objectives
- D2.1.S1 — Recommend a solution for storing relational data
- D2.1.S2 — Recommend a database service tier and compute tier
- D2.1.S3 — Recommend a solution for database scalability
- D2.1.S4 — Recommend a solution for data protection