Relational Data Storage Design

AZ-305 AZ-305 · Design data storage solutions

By Abu Anns · Last updated 20 November 2026Exam version: AZ-305 (skills measured as of 17 April 2026)

Relational data remains the backbone of most enterprise applications, and Azure offers several ways to host it. AZ-305 tests your ability to choose between them: when Azure SQL Database fits, when SQL Managed Instance is the right lift-and-shift target, and how to size, scale, and protect the database you choose. The decision always starts with the workload's requirements — compatibility, scale, availability, and cost — not with the service catalogue.

Azure SQL Database vs SQL Managed Instance vs SQL Server on VMs

Azure SQL Database is the fully managed PaaS option: a single database (or elastic pool) with built-in high availability, automated backups, and patching handled by Microsoft. Choose it for new cloud-native applications and for migrations where near-full SQL Server compatibility is enough. Azure SQL Managed Instance is also PaaS but offers near-100 percent compatibility with on-premises SQL Server, including SQL Server Agent, cross-database queries, CLR, and database mail — choose it when lifting and shifting applications that depend on instance-level features, or when you need to migrate many databases with minimal changes. SQL Server on Azure VMs (IaaS) gives full control over the OS and SQL Server version, including specific versions or features the PaaS options lack — choose it only when a hard requirement (an unsupported SQL Server version, a third-party component needing OS access, or a specific high-availability topology) rules out PaaS, because you then own patching, backups, and availability yourself. The exam's recurring trap is recommending IaaS when a PaaS option meets the requirement: PaaS is the default answer unless a stated constraint forces IaaS.

Service tiers and purchasing models

Azure SQL Database offers the DTU-based model (Basic, Standard, Premium) and the vCore-based model (General Purpose, Business Critical, Hyperscale). The vCore model is the modern choice: compute and storage scale independently, and you can choose provisioned compute (pay for allocated capacity) or serverless (auto-scales and auto-pauses, billed per second — ideal for intermittent or unpredictable workloads). General Purpose suits most workloads, with storage in remote blob storage and availability via a locally redundant replica. Business Critical uses locally attached SSD storage with always-on replicas for low-latency I/O and fast failover — choose it for OLTP workloads needing the lowest latency and highest availability. Hyperscale separates compute from a rapidly scaling storage layer (up to 100 TB), with near-instant backups and fast scaling — choose it for very large databases or workloads with rapidly growing storage. Elastic pools share compute across many databases with variable usage, so each database can burst while the pool's total cost stays predictable — the right answer when a SaaS application has many databases with peaks at different times.

Scaling reads and writes

For read-heavy workloads, use read scale-out: Business Critical and Premium tiers provide a free read-only replica, and Hyperscale supports named replicas for read scaling and workload isolation. For geo-distributed reads, active geo-replication creates up to four readable secondary databases in any region, each with its own failover. For write scaling, the options are narrower: relational databases scale vertically (more vCores) and through sharding — elastic database tools support horizontal sharding across databases for multi-tenant SaaS. When a scenario's write volume exceeds what a single database can handle, consider whether the workload is truly relational: very high-volume, globally distributed writes often belong in Cosmos DB instead.

Availability and data protection

Built-in availability differs by tier: General Purpose relies on remote storage with zone redundancy available; Business Critical uses a quorum of replicas with zone redundancy for fast failover. Zone redundancy replicates data across availability zones within a region, protecting against datacentre failure; it is available in General Purpose, Business Critical, and Hyperscale. For regional failure, use failover groups: a group of databases with a primary and a geo-secondary, automatic failover on a configurable policy, and a single listener endpoint so applications do not need connection-string changes. Backups are automatic: full backups weekly, differential every 12–24 hours, transaction logs every 5–10 minutes, with point-in-time restore up to 35 days (7 days by default on Basic). Long-term retention extends backups to 10 years for compliance. For protection against accidental or malicious deletion, enable backup storage redundancy (geo-redundant backup storage) and consider immutable backup vaults for critical databases.

Security and migration considerations

Security features the exam expects you to know: transparent data encryption (TDE) encrypts data at rest (enabled by default, with customer-managed keys in Key Vault for BYOK); Always Encrypted encrypts sensitive columns so even database administrators cannot read the values; dynamic data masking hides sensitive data from non-privileged users; and Microsoft Defender for SQL adds vulnerability assessment and advanced threat protection. Private connectivity comes from private endpoints, and public network access should be disabled for sensitive databases. For migration, Azure Database Migration Service (DMS) and the Azure SQL Migration extension handle online migrations with minimal downtime; assess compatibility first with Azure Migrate or the Data Migration Assistant.

Performance design and monitoring

Choosing the right tier is only the start; the database must stay fast as it grows. Query Performance Insight and the Query Store identify slow queries and regressions, while automatic tuning can create and drop indexes and fix query plans automatically — enable it for most production databases, with automatic index management verified in a test environment first. For read-heavy workloads beyond read scale-out, consider Hyperscale named replicas for workload isolation (reporting queries on one replica, the application on another) or a caching layer (Azure Cache for Redis) in front of the database. Index design remains the highest-leverage performance work: missing indexes cause scans, and the Database Engine Tuning Advisor or automatic tuning recommendations identify them. For connection management, use connection pooling in the application and consider Azure SQL's connection policy settings for proxy vs redirect (redirect reduces latency by connecting directly to the database node). When a scenario describes slow queries after a deployment, the answer is usually Query Store and plan regression, not more vCores — scaling compute treats the symptom, while the Query Store finds the cause.

Quick decision summary

When a scenario asks for a relational database, work through three questions. First, does the workload need SQL Server instance features (Agent, cross-database queries, CLR)? If yes, the answer is SQL Managed Instance. Second, is it a new cloud-native application or a simple migration? If yes, Azure SQL Database is the default. Third, is there a hard requirement that neither PaaS option meets — an unsupported SQL Server version, OS-level access, or a third-party component? Only then is SQL Server on VMs the answer. After choosing the service, size it: General Purpose for most workloads, Business Critical for low latency, Hyperscale for very large databases, serverless for intermittent use, elastic pools for many databases. Then protect it: zone redundancy for datacentre failure, failover groups for regional failure, and long-term retention where regulation demands it.

Advertisement

Exam traps to watch for

Practice questions

Test yourself on this guide. Answers are checked instantly, with a full explanation for each question.

Q1.A company is migrating an on-premises application that uses SQL Server Agent jobs, cross-database queries, and database mail. The application must move to Azure with minimal code changes. Which database service should the architect recommend?

Report an error

Q2.A SaaS company has 200 customer databases with unpredictable, spiky usage — each database peaks at different times of day. The company wants predictable costs and the ability for any database to burst. What should the architect recommend?

Report an error

Q3.A trading application needs the lowest possible read latency and must survive a datacentre failure within its region without data loss. Which Azure SQL Database tier and configuration should the architect recommend?

Report an error

Q4.A company runs an Azure SQL Database in West Europe and needs automatic failover to North Europe if the region fails, without changing the application connection string. What should the architect recommend?

Report an error

Q5.A database holds credit card numbers. The security team requires that even database administrators cannot read the card values, while the application must still be able to query them. Which feature should the architect recommend?

Report an error

Q6.A reporting workload runs heavy read queries against an Azure SQL Database and is slowing down the transactional application that shares it. The database is on the Business Critical tier. What is the simplest fix?

Report an error

Q7.A database grows by 500 GB per month and is approaching 4 TB. Backups take too long and scaling storage requires downtime. Which service tier should the architect recommend?

Report an error

Q8.A company must keep database backups for 10 years to meet a regulatory requirement. Which Azure SQL Database feature should the architect recommend?

Report an error

Q9.A development database is used for a few hours each day by a small team and sits idle the rest of the time. The company wants to minimise cost without losing data. What should the architect recommend?

Report an error