AZ-305 Relational Data Storage Design: Azure SQL Database, Managed Instance, Tiers, Scaling and Data Protection

Relational data storage design is the set of decisions that turns a workload's database requirements into one Azure service, one purchasing model, one service tier and compute tier, a scaling approach and a set of data-protection controls. The official task has four skills: recommend a solution for storing relational data, recommend a database service tier and compute tier, recommend a solution for database scalability, and recommend a solution for data protection.

Work through them in that order, because each choice narrows the next. The engine and the features the application depends on pick the service. The workload's size, latency and cost profile pick the tier. Read and write growth pick the scaling pattern. The threat you must defend against (lost media, a curious administrator, another tenant, an auditor asking for proof) picks the protection feature.

High availability, failover groups and geo-replication for disaster recovery belong to the business continuity task, and backup retention to the backup and recovery task; this lesson mentions them only where they affect a storage or scaling choice.

Choose Azure SQL Database for new cloud applications that use one database at a time, Azure SQL Managed Instance when a SQL Server workload needs instance-scoped features but you still want PaaS, and SQL Server on an Azure virtual machine when the workload needs something no PaaS option provides. All three run the SQL Server engine; the difference is how much of the instance you get and how much you must manage.

Azure SQL Database is a database-scoped service on a logical server. It has no SQL Server Agent, no Database Mail, no CLR, no Service Broker and no cross-database queries with three-part names. Its substitutes (elastic jobs instead of Agent jobs, elastic query instead of cross-database joins) work, but adopting them means rewriting code. Changing the tier or putting the database in an elastic pool never adds these features: Hyperscale, Business Critical and pools change performance, size and cost, not the engine's surface area.

Azure SQL Managed Instance gives you a near-complete SQL Server instance as a managed service: SQL Server Agent jobs, Database Mail with sp_send_dbmail, CLR assemblies created from binary, Service Broker, cross-database queries between databases on the same instance and linked servers. Microsoft still handles patching, automated backups and high availability. This makes it the usual answer for a lift-and-shift of an existing SQL Server instance that must become PaaS.

When a requirement says "PaaS" or "least administrative effort", start with Managed Instance for an existing SQL Server estate, then check every stated dependency against the table. One unsupported feature that must not change is enough to push the design to a VM.

SQL Server on an Azure virtual machine is the right recommendation when the workload depends on something that neither PaaS option supports and the requirements forbid changing it. It is IaaS: you get full control of the operating system and SQL Server, and you take on their management, so it loses whenever a requirement names PaaS and nothing else rules PaaS out.

• FILESTREAM or FileTable. Neither Azure SQL Database nor Managed Instance supports them. Moving the files to Blob storage is a schema change.
• SSRS on the same server. The PaaS services don't run Reporting Services. Microsoft's PaaS alternative is Power BI paginated reports, which changes the reporting platform.
• Linked servers to non-SQL Server sources, such as Oracle through its OLE DB provider. Managed Instance can't host third-party providers; replacing the linked server with Azure Data Factory changes the integration.
• A pinned engine version or vendor-approved update timing. Azure SQL Database and Managed Instance are evergreen: Microsoft updates the engine and you can't hold a major version or defer updates.
• OS-level access for agents, custom software or configuration that a managed service doesn't expose.

Version pinning has two tempting near-misses. Database compatibility level (for example 150 for SQL Server 2019) only changes query-processing behaviour; the engine underneath is still the current, Microsoft-patched build. The Managed Instance update policy chooses between a feature set aligned to SQL Server 2022, one aligned to SQL Server 2025, or the always-up-to-date feature set; it doesn't offer older versions and Microsoft still applies updates. The rule: any requirement to choose the engine build or decide when updates are applied rules out both PaaS options, and only SQL Server on a VM, where you install the version and updates yourself, meets it.

When the database engine must stay PostgreSQL or MySQL, the managed Azure choice is Azure Database for PostgreSQL flexible server or Azure Database for MySQL flexible server. Moving to Azure SQL means changing the engine, and running the engine on VMs means managing replication, backups and patching yourself.

Flexible server provides automatic backups, managed minor version patching, extensions (for PostgreSQL, extensions such as PostGIS), and zone-redundant high availability with a standby in another availability zone. Azure Database for MySQL Single Server is retired, so flexible server is the only current MySQL deployment option.

Two read-side behaviours are easy to confuse:

A design that needs both zone resilience and readable copies (for example for a reporting team in another region) therefore needs both features. Restoring geo-redundant backups produces point-in-time copies, not a continuously updated replica.

For PostgreSQL data that will outgrow a single node, see the scalability section: elastic clusters in flexible server are the current distributed option, and Azure Cosmos DB for PostgreSQL is on a retirement path and not recommended for new projects.

The DTU model sells preconfigured bundles of compute, storage and I/O at a fixed price per performance level; the vCore model lets you choose compute and storage independently and unlocks Azure Hybrid Benefit, reserved capacity, serverless compute and Hyperscale. Azure SQL Database offers both; Managed Instance is vCore only.

Within the DTU model, Standard suits light to moderate steady workloads and Premium is for I/O-intensive, latency-sensitive work at a higher price; Premium is also the DTU tier with In-Memory OLTP and a free read scale-out replica.

Cost levers for steady vCore workloads stack. Azure Hybrid Benefit removes the SQL Server licence charge when you bring SQL Server core licences with active Software Assurance, and reserved capacity discounts compute in exchange for a one- or three-year commitment. Neither changes performance. Hyperscale has had simplified pricing with no SQL Server licence component since December 2023, so Azure Hybrid Benefit isn't available for new Hyperscale databases; reservations still apply to its provisioned compute. Serverless is priced for intermittent use: a database busy around the clock costs more on serverless than on provisioned compute, and neither discount applies to it.

General Purpose is the budget tier with remote storage, Business Critical is the low-latency tier with local SSD, In-Memory OLTP and an included readable replica, and Hyperscale is the tier for very large or fast-growing databases with snapshot backups and fast scaling. Managed Instance offers General Purpose and Business Critical with the same storage and replica distinction.

Read the requirements for the deciding feature. Memory-optimized tables or the lowest storage latency point to Business Critical (or DTU Premium); no hardware generation or zone-redundancy setting adds In-Memory OLTP to General Purpose. Growth beyond 4 TB, size-independent backups or scaling compute without copying data point to Hyperscale. A balanced workload under 4 TB with no special feature points to General Purpose when budget leads. Note that Microsoft now positions Hyperscale as the recommended default tier for new and modernizing OLTP and HTAP workloads, so a requirement phrased around future growth or fast scaling, rather than lowest cost, leans that way.

The same logic holds inside Managed Instance: an instance that needs SQL Server Agent plus memory-optimized tables or an included reporting replica must be Business Critical, because General Purpose Managed Instance has neither In-Memory OLTP nor a readable secondary.

Hyperscale behaviours that change a design

Hyperscale is a one-way door unless you plan the exit: it can't host memory-optimized tables, it can be reverse migrated only to General Purpose and only within 45 days, and its backups can't be restored to another tier. These limits matter more than its size limit when you recommend a conversion.

• No memory-optimized tables. Hyperscale supports only a subset of In-Memory OLTP: memory-optimized table types, table variables and natively compiled modules work, but durable and non-durable memory-optimized tables don't. A conversion from Premium or Business Critical is blocked while any In-Memory OLTP object exists, so change memory-optimized tables to disk-based tables and drop the other In-Memory OLTP objects (and their dependencies) before converting; the supported ones can be recreated afterwards. This is a code and schema task: no tier hop, compute size or replica setting removes the restriction.
• Reverse migration. A database that was converted to Hyperscale can be moved back within 45 days, but only to General Purpose. To return to Business Critical, reverse migrate to General Purpose and then change the tier. A database created as Hyperscale can't be reverse migrated.
• Backups. Hyperscale backups restore only as Hyperscale databases. Pre-conversion backups of the original tier can still be restored, but they don't contain changes made after the conversion.
• Geo-replicas share the primary's tier, so a Business Critical geo-secondary of a Hyperscale database isn't a way out.

Hyperscale replicas come in two kinds, covered with scalability below: high-availability replicas and named replicas.

Provisioned compute is a fixed size billed every hour it exists; serverless compute autoscales within a vCore range, bills per second and can pause when idle; an elastic pool shares one set of provisioned resources across many databases. The usage pattern and whether users can tolerate a resume delay decide between them.

Serverless is available for single databases in General Purpose and Hyperscale (not Business Critical) on standard-series hardware. You set a minimum and maximum vCore count, and compute is billed per second for what is used. Auto-pause is a separate setting: after a configurable idle delay (up to seven days) the database pauses and only storage is billed, and the next connection resumes it after a short delay. Auto-pause is generally available in General Purpose and in preview for Hyperscale. You can turn it off, in which case the database never pauses, always bills at least its minimum compute, and has no resume delay, while keeping autoscaling and per-second billing.

Elastic pools use provisioned compute. They save money when many databases have low average utilization and peaks that rarely overlap, because the pool is sized for the combined load rather than every database's peak. Nothing pauses, so there is no warm-up delay. Per-database minimum and maximum settings stop one database from taking the whole pool, but capping a database also throttles it.

The general rule: auto-pause trades idle cost for a resume delay, so it fits only where the first request after a quiet period may wait. Turning auto-pause off removes the delay but leaves a minimum compute charge running all the time on every database, so across a large fleet of mostly idle databases it is usually dearer than sharing one pool. For example, a set of departmental reporting databases opened once a morning can live with auto-pause; a fleet of customer-facing databases whose users notice any wait is better served by a pool.

To offload read-only work without extra cost, connect it with ApplicationIntent=ReadOnly to the read scale-out replica that Premium and Business Critical include; to give read workloads their own compute size and security, use Hyperscale named replicas. Scaling the primary up adds compute charges and leaves the reports competing with the writes.

Premium and Business Critical databases (and Business Critical Managed Instances) keep several synchronized replicas for high availability, and read scale-out routes read-intent connections to one of them at no extra charge. Data there can lag the primary slightly. Standard and General Purpose have a single compute replica, so there is nothing to route to.

Hyperscale has two kinds of same-region secondaries:

Both share the primary's storage, so they stay current, unlike a database copy, which is a point-in-time snapshot. Named replicas are the answer when several teams each need an independently sized, isolated read-only copy and failover behaviour must not change. Active geo-replication also creates a readable secondary, but it is a separately billed database whose main purpose is disaster recovery (covered in the business continuity task).

Scaling writes: sharding and distributed PostgreSQL

When write throughput or data size outgrows a single database, scale out horizontally by sharding the data across databases; read replicas, named replicas and bigger sizes don't spread writes. Every replica holds a full copy and accepts no writes, and scaling up stays on one node.

For Azure SQL Database, the Elastic Database client library maintains a shard map that records which database holds each sharding key (for example a tenant ID) and provides data-dependent routing, opening the connection to the right shard. A multitenant design can place large tenants in their own databases and group small tenants, often with the small-tenant shards in an elastic pool. Related tools have different jobs: elastic query runs read queries across databases, and elastic jobs run T-SQL across many databases on a schedule; neither partitions data or routes tenants.

For PostgreSQL, elastic clusters in Azure Database for PostgreSQL flexible server distribute tables across nodes with the Citus extension while applications keep standard PostgreSQL drivers and tooling. Azure Cosmos DB for PostgreSQL offers similar distributed tables but is on a retirement path, and Microsoft directs new distributed PostgreSQL projects to elastic clusters. Flexible server read replicas scale reads only.

Transparent data encryption protects data files and backups at rest with no application change; Always Encrypted protects chosen columns from everyone who can't get the keys, including DBAs and cloud operators, but needs driver and query support. Choose by who must be kept out.

TDE is on by default for new Azure SQL databases with a service-managed protector. To create, rotate and revoke the key yourself, switch to a customer-managed key (bring your own key) in Azure Key Vault or Azure Key Vault Managed HSM. You can't supply a certificate in the master database as the protector, as you can on SQL Server on-premises. Requirements and behaviours:

• The vault or HSM must have soft delete and purge protection enabled, so an accidentally deleted key or vault can be recovered before databases become permanently inaccessible; TDE validates both settings and refuses the key without them.
• The logical server's (or a user-assigned) managed identity needs get, wrap key and unwrap key on the key: with the Azure RBAC permission model, the Key Vault Crypto Service Encryption User role. Data-plane key access comes from a data-plane role; roles that manage the vault resource itself don't grant it.
• The protector is an asymmetric key (RSA or RSA-HSM) stored as a Key Vault key object.
• Revoking access makes the affected databases inaccessible. A server-level key protects every database on the server; a database-level customer-managed key gives each database its own protector, so one customer's revocation affects only that database.

Always Encrypted encrypts columns in the client driver; the engine never holds the column master key, so privileged users see ciphertext. Deterministic encryption produces the same ciphertext for the same value and supports equality lookups, joins and grouping; randomized encryption is stronger but supports no computation without an enclave. Secure enclaves let the engine evaluate range comparisons, LIKE and in-place encryption on enclave-enabled columns using randomized encryption. Azure SQL Database offers VBS enclaves on standard hardware and Intel SGX enclaves on DC-series hardware; Always Encrypted with Intel SGX enclaves reaches end of support on 31 October 2027, so new designs should use VBS.

Dynamic data masking hides part or all of a column's values from non-privileged users at query time, row-level security controls which rows each user or session gets back, and neither stops a database administrator. Only Always Encrypted keeps data from administrators.

Dynamic data masking applies masking rules (default, email, random, datetime, or a partial custom-string mask such as showing only the last four digits) in the engine, so the application needs no change. Users or roles granted UNMASK see real values, and administrators are always exempt. The data isn't encrypted and every row is still returned.

Row-level security binds an inline table-valued predicate function to a table through a security policy. A filter predicate silently removes rows the caller may not read from every query, even one that forgets its WHERE clause; a block predicate stops writes that would violate the rule. With a shared application login, the app sets the tenant or user in SESSION_CONTEXT and the predicate compares each row against it. Per-tenant views are weaker because queries against the base table bypass them.

Requirements often combine two of these, because each answers a different threat: one feature for the administrator, another for ordinary users. For example, a clinic database might use Always Encrypted on a diagnosis column that IT staff must never read, and row-level security so each doctor's queries return only that doctor's patients.

Tamper evidence and monitoring: ledger, auditing and Defender for SQL

Ledger tables prove that data wasn't altered, SQL auditing records who did what, and Microsoft Defender for SQL alerts on threats such as SQL injection; each answers a different question.

The ledger feature hashes every transaction into a blockchain-like chain. Append-only ledger tables allow inserts only, which suits insert-only events; updatable ledger tables allow updates and deletes but keep their history. Database digests summarize the ledger state; with automatic digest storage they are written to immutable Azure Blob storage, outside the reach of the DBAs being audited, and the database can be verified against them. Temporal tables keep row history but give no cryptographic proof, and administrators can change the history.

SQL auditing writes database events to an Azure Storage account (cheap, long retention), a Log Analytics workspace (queryable with KQL alongside other Azure logs) or Event Hubs (streaming to other tools). Platform metrics show resource usage, not data access.

Microsoft Defender for SQL adds vulnerability assessment and threat protection, alerting on potential SQL injection, anomalous logins (for example from unusual locations) and suspicious access patterns.