Design Data Storage Solutions for Relational Data — Lesson

Relational databases are the backbone of enterprise systems, holding mission-critical data for billions of transactions daily. Azure provides multiple relational storage options—each optimized for different workloads, scales, and cost profiles. Designing the right relational solution requires balancing performance, security, scalability, and cost.

This lesson covers the Azure relational database platform, purchasing models, security controls, and scaling patterns. It prepares you for AZ-305 exam scenarios where you must recommend and design relational solutions for real-world enterprise needs.

Reference: Ch. 2, §§2.1–2.3, p. 51–76 of the AZ-305 exam book.

Why This Matters

Choosing the wrong relational platform can waste millions in compute costs or expose your organization to data breaches. Azure SQL Database is cost-efficient for variable workloads; Managed Instance suits lift-and-shift migrations; SQL Server on VMs offers maximum control; PostgreSQL and MySQL serve open-source ecosystems. Understanding when to use each, how to size correctly, and which security layers to apply are core AZ-305 competencies.

Architects must also know how to scale read workloads, protect data at rest and in transit, and ensure compliance. The exam tests both breadth (all five platforms) and depth (DTU vs. vCore models, auto-failover groups, Always Encrypted).

Prerequisites

• Azure fundamentals: Understand Azure regions, availability zones, and managed services.
  • Self-check: Can you name three Azure regions and explain latency trade-offs?
• SQL basics: Know SQL syntax, indexes, and query performance.
  • Self-check: What is a clustered index, and why does it affect write performance?
• Network topology: Familiar with virtual networks, subnets, and Azure firewalls.
  • Self-check: How do private endpoints differ from public endpoints for database connectivity?
• Data security principles: Understand encryption, access control, and compliance frameworks.
  • Self-check: What is the difference between encryption at rest and in transit?

Learning Objectives

1. LO12: Recommend a relational storage solution (Azure SQL Database, SQL Managed Instance, SQL Server on VMs, Azure Database for PostgreSQL/MySQL) based on workload, migration path, and cost constraints.
2. LO13: Design database tier and sizing strategy using DTU/vCore models, compute tiers, elastic pools, and serverless configurations.
3. LO14: Design scalability patterns for read-heavy workloads, including read scale-out, sharding, geo-replication, and auto-failover groups.
4. LO15: Design data protection strategies using Transparent Data Encryption (TDE), Always Encrypted, Dynamic Data Masking (DDM), Row-Level Security (RLS), and auditing.

Building Blocks

Azure SQL Database — Analogy: A managed coffee machine that auto-scales; you buy cups, not the machine. Definition: A fully managed PaaS relational database that auto-scales compute and storage, removes patching overhead, and includes built-in HA. Why it matters: Lowest operational burden; ideal for SaaS, variable workloads, and greenfield applications.

Azure SQL Managed Instance — Analogy: A rented data center rack with DBA on-call; you get near-native SQL Server features without owning hardware. Definition: A managed SQL Server with 99.99% SLA, native virtual network integration, and compatibility with most SQL Server features (logins, linked servers, T-SQL). Why it matters: Enables lift-and-shift; reduces migration friction; supports complex migrations with less refactoring.

SQL Server on VMs (IaaS) — Analogy: Owning and maintaining your own data center closet. Definition: SQL Server running on Azure VMs; you manage OS patches, SQL patches, HA/DR, and backups. Why it matters: Maximum control, complex licensing, custom configurations; ideal for on-premise parity or specialized SQL Server features.

Azure Database for PostgreSQL / MySQL — Analogy: Managed open-source databases, like PaaS coffee from a café chain. Definition: Fully managed PostgreSQL or MySQL with auto-backup, HA, and scaling; removes infrastructure management. Why it matters: Cost-effective for open-source workloads; eliminates licensing; attracts talent familiar with PostgreSQL/MySQL ecosystems.

DTU (Database Transaction Unit) — Analogy: A bundled "coffee quota" that includes beans, water, and energy. Definition: A composite metric mixing compute (CPU), memory, and I/O; measured in eDTU for elastic pools. Why it matters: Simple purchasing for unpredictable workloads; no need to forecast CPU and RAM separately; good for small-to-mid databases.

vCore (Virtual Core) — Analogy: Buying CPU cores individually, like choosing exact machine specs. Definition: One logical CPU core; paired with memory and storage tiers (General Purpose, Business Critical, Hyperscale). Why it matters: Transparent sizing; good for predictable workloads; enables reserved instances (RIs) for discounts; fine-grained control.

Elastic Pool — Analogy: Sharing a coffee machine budget across multiple departments; peak demand in one dept doesn't starve others. Definition: A shared pool of compute and storage for multiple databases, with min/max DTU/vCore per database. Why it matters: Multi-tenant SaaS architectures; reduces cost for databases with non-overlapping peaks; simplifies management.

Transparent Data Encryption (TDE) — Analogy: A vault that auto-locks whenever the database closes. Definition: Encryption of data pages on disk; transparent to applications (queries see plaintext). Key stored in Azure Key Vault or Service-Managed. Why it matters: Meets compliance (HIPAA, PCI, GDPR); protects stolen backups; minimal performance overhead.

Always Encrypted — Analogy: A lock-box inside the vault; only the application holds the key, not even DBAs. Definition: Column-level encryption; keys reside in the app tier or HSM, never in the database; parameterized queries enable server-side filtering on encrypted columns. Why it matters: Protects against insider threats; meets GDPR "right to be forgotten"; supports deterministic encryption for indexing.

Dynamic Data Masking (DDM) — Analogy: Pixelating a face in a photo so friends can still see you're in the picture. Definition: Query-time obfuscation of sensitive columns (e.g., XXX-XX-1234 for SSN); rules-based by role. Why it matters: Simple compliance tool; doesn't change underlying data; speeds GDPR subject-data requests; light-weight.

Row-Level Security (RLS) — Analogy: A bouncer checking your ID before letting you into a room; you can only see rows you're authorized for. Definition: Predicate-based filtering at the database engine level; users see only rows matching their security context. Why it matters: Multi-tenant enforcement; prevents accidental data leaks; works with applications and direct queries alike.

Understanding Relational Database Architecture

A relational database stores data in structured tables with rows and columns, enforcing referential integrity through primary and foreign keys. Azure's relational offerings—including Azure SQL Database, Azure SQL Managed Instance, PostgreSQL, and MySQL—all follow the relational model but differ in deployment pattern and management responsibility. When designing a relational solution, you must consider:

• Deployment model: Managed platform (SQL Database, Managed Instance) vs. self-managed (SQL Server on VMs)
• Purchasing model: Consumption-based (DTU) vs. compute-focused (vCore)
• Tier selection: Determines compute, memory, storage, and HA capabilities
• Scaling strategy: Vertical (larger instance) vs. horizontal (read replicas, sharding, geo-replication)

Understanding the nuances of each choice prevents costly mistakes in production.

DTU vs vCore Purchasing Models

Deep Dive

LO12 — Recommend a Relational Storage Solution

Decision Tree for Platform Selection

Start with the migration path:

• Greenfield (new app)? → Strongly consider Azure SQL Database (PaaS, lowest ops).
• Lift-and-shift SQL Server 2012+? → SQL Managed Instance (near-native feature parity, minimal refactoring).
• Exotic SQL Server features (e.g., SQL Agent, Service Broker, distributed transactions)? → SQL Server on VMs.
• Open-source (PostgreSQL/MySQL)? → Azure Database for PostgreSQL/MySQL (managed, cost-effective).

Worked Decision: A company migrating a legacy ERP system running SQL Server 2019 needs minimal downtime. They use logins, SQL Agent jobs, and some transactional replication. → Recommendation: SQL Managed Instance. It provides 99.99% SLA, allows logins and Agent jobs, and accepts database backups via RESTORE DATABASE.

See the LO-level lesson for more.

Why This Matters for Exam and Production

Choosing the wrong relational platform can lock your organization into technical debt. A lift-and-shift migration of a legacy SQL Server application designed for Managed Instance may cost 150K/month if you select vCore tiers without reservation pricing, whereas planned RI purchases could cut costs by 40K/month. Conversely, choosing SQL Database when you need SQL Agent for scheduled jobs (only in Managed Instance) forces mid-project rearchitecture.

Key exam patterns:

• Lift-and-shift scenarios: Default to SQL Managed Instance (supports legacy SQL Server features, SQL Agent, linked servers)
• Greenfield SaaS: Use SQL Database with elastic pools and geo-replication for multi-tenant isolation
• Open-source ecosystems: Use managed PostgreSQL or MySQL for ecosystem compatibility
• Maximum control required: SQL Server on VMs (but requires highest operational overhead)

[!TIP] On the AZ-305 exam, always ask: "Does this application need SQL Server–specific features (Agent, CLR, linked servers, specific T-SQL extensions)?" If yes → Managed Instance. If no → SQL Database (cheaper, simpler).

[!WARNING] Never assume a legacy application is "already tuned for vCore"—older T-SQL code often exploits DTU limits implicitly. Always benchmark after migration.

LO13 — Design for Database Tier and Sizing

Purchasing Models

DTU Model: Bundles CPU, memory, and I/O into a single metric (Basic, Standard, Premium tiers). Suitable for databases < 100 GB, unpredictable workloads, or dev/test. eDTU for elastic pools (e.g., 100 eDTU pool shared across 10 databases).

vCore Model: Separates compute (logical CPU cores) from storage. Offers Serverless (auto-pauses after idle threshold), Provisioned (always-on), and Business Critical tiers. Better for databases > 100 GB, predictable workloads, or cost-sensitive production.

Compute Tiers (vCore model)

• General Purpose: Suitable for most workloads; compute/memory ratio ~1:5.1. Includes remote NVMe cache (Accelerated Database Recovery). Cost-effective.
• Business Critical: High performance; compute/memory ratio ~1:7. In-memory OLTP, always-on HA (4 replicas). Premium for mission-critical.
• Hyperscale: Up to 100 TB storage (partitioned), fast compute scaling (~ 5 min). Read scale-out replicas at no charge. For massive transactional or analytical workloads.

Serverless Configuration

Compute auto-pauses after `$X$ hours of idle time (e.g., 1 hour default). Resumes on next connection (~ 1 min cold-start). Billing pauses during idle. Ideal for dev, test, and bursty workloads.

Elastic Pools

For multi-tenant SaaS: Define a pool (e.g., 400 eDTU, 5 GB storage per database). Databases within the pool share compute; one database's peak doesn't starve others. Cost 20–40% less than standalone databases if workloads are non-overlapping.

See the LO-level lesson for more.

Deep Dive into Sizing and Cost Optimization

DTU Sizing Fundamentals: DTU is an abstraction bundling compute, memory, and I/O. A S0 tier (10 DTUs) is suitable for light test workloads (~5 concurrent connections), while S12 (~1000 DTUs) handles moderate transactional loads. In production, you must monitor DTU percentage—if sustained above $80\%$, consider upscaling.

vCore Sizing for Variable Workloads: If your application has highly variable traffic (e.g., 10 vCores at midnight, 50 at noon), vCore + reserved instances (1- or 3-year discounts) can deliver $40\%$ cost savings vs. always-provisioned DTU. Serverless vCore (auto-pause during idle periods) further reduces waste.

Tier Selection Trade-offs:

• General Purpose: Best for mixed OLTP/reporting; acceptable $2\%$ latency variance; scales to 128 vCores
• Business Critical: Mandated for SLA $> 99.99\%$; includes in-memory OLTP for sub-millisecond response; $2x$ cost premium
• Hyperscale: For databases $> 1$TB or concurrent read-heavy workloads; distributed architecture (compute separated from storage leaf pages); incremental cost as data grows

[!TIP] Use the Azure Pricing Calculator with your actual workload metrics (DTU % history, vCore CPU %) to simulate tier costs. A misconfigured tier can waste $50K+/year.

HA and DR Considerations

Every relational database tier in Azure includes a configurable SLA. General Purpose offers multi-region failover via read replicas (RTO ~5 minutes, RPO ~5 seconds). Business Critical includes always-on secondary replicas in the same region (RTO <1 second, near-zero RPO). For mission-critical applications (e.g., banking ledgers), always select Business Critical or enable auto-failover groups.

Service Tiers and Performance Characteristics

LO14 — Design for Database Scalability

Read Scale-Out

Create up to 4 read-only secondary replicas (within same region or geo-replicated). Application routes read workload to secondaries; write workload to primary. Built-in redirection with connection strings or custom app logic.

Sharding Patterns

For multi-tenant SaaS or massive single databases:

1. Range sharding: Partition by customer ID ranges (e.g., Shard-A: 1–10k, Shard-B: 10k–20k).
2. Hash sharding: Hash on tenant ID % number of shards.
3. Directory-based: Maintain a shard map (e.g., Tenant 123 → Shard-C).

See the LO-level lesson for more.

Geo-Replication & Auto-Failover Groups

Asynchronously replicate databases to secondary region(s) for HA/DR. Auto-failover group automates regional failover (RTO ~ 30 sec, RPO ~ 5 sec).

See the LO-level lesson for more.

Scaling Patterns and Trade-offs

Read Replicas: Distribute read-heavy analytics queries to separate replicas, reducing load on the primary. Replicas are eventually consistent ($5\%$–$30\%$ lag depending on workload). Applications must route reads explicitly (via connection strings or middleware). Ideal for reporting workloads.

Elastic Pools: Pool up to $500$ databases with shared compute. Each pool has min/max eDTU limits. A tenant experiencing peak load can burst up to the pool max, as long as other tenants are quiet. Pooling is most cost-effective when tenant workloads are uncorrelated (peak times differ).

[!WARNING] Do NOT pool databases with predictable, synchronized peaks (e.g., all tenants run nightly batch jobs at midnight). This causes pool exhaustion and SLA violations.

Sharding: Manually split data by tenant, geography, or date range across multiple databases. Each shard is independent. Requires application-side routing logic and data consistency discipline. Unlocks unlimited horizontal scale but increases operational complexity (consistent backups, cross-shard joins difficult).

Geo-Replication: Asynchronously replicate the entire database to a secondary region. Enables disaster recovery (RTO ~1 minute, RPO ~5 seconds) and local read scaling in geographically distributed apps. Cost is $70\%$ of the primary database.

Scaling Strategies Matrix

LO15 — Design for Data Protection

Transparent Data Encryption (TDE)

Encrypt all data pages on disk using a Database Encryption Key (DEK) protected by a Service-Managed Key or Azure Key Vault Transparent Key Encryption Key (TKEY).

Always Encrypted

Column-level encryption; encryption keys reside in client-side HSM or app config, never on server. Use deterministic or randomized encryption.

Dynamic Data Masking (DDM)

Rule-based obfuscation at query time. Roles can have unmasked access; others see masked values.

Row-Level Security (RLS)

Predicates prevent users from seeing rows outside their security context.

Auditing & Microsoft Defender for SQL

Enable auditing to log all DDL, DML, SELECT, and authentication events to Azure Storage or Log Analytics. Microsoft Defender for SQL flags suspicious activity (e.g., brute-force logins, SQL injection, unusual data access).

See the LO-level lesson for more.

Data Protection Strategies in Depth

Transparent Data Encryption (TDE): Encrypts the entire database file at rest on disk. Applied at the storage layer; the application sees plaintext. Key is stored in Azure Key Vault (BYOK) or Microsoft-managed. TDE protects against disk theft and unauthorized file access. Enabled by default on all SQL Database tiers. Gotcha: TDE does NOT protect against DBA privilege escalation (a DBA can still query unencrypted data in memory).

Always Encrypted: Client-side encryption—the application encrypts data before sending to the database. The database stores ciphertext; even DBAs cannot read it without the client's decryption key (stored in app config or Azure Key Vault). Requires application code changes and careful key rotation. Cost: Adds latency (~10–20%$ for encrypted column queries) and complexity (WHERE clauses on encrypted columns are not supported without enclave–based queries).

[!TIP] Combine TDE + Always Encrypted for defense-in-depth: TDE protects disk/backup; Always Encrypted protects against DBA snooping.

Dynamic Data Masking (DDM): Masks sensitive columns in query results without altering stored data. A DBA querying the salary column sees *****, while authorized users see real values. Zero application impact. Limitation: Applies only to SELECT queries; masked data is readable in export/dump operations.

Row-Level Security (RLS): Filters query results by predicate (e.g., WHERE tenant_id = @user_tenant_id). Applied transparently; users cannot bypass it via direct SQL. Prevents cross-tenant data leakage in shared tables. Cost: Minimal; predicates are evaluated server-side.

Data Protection Features Matrix

Worked Examples

Example 1: SaaS Multi-Tenant Startup (Easy → Medium, LO12 + LO13)

Problem: A B2B SaaS company (CRM) is launching with 50 customers. Customers are volatile (some grow to 1000s of users, others churn). They need cost efficiency, predictable monthly spend, and per-customer isolation.

Step-by-Step Solution:

1. Choose platform: Azure SQL Database (PaaS, simplest ops for SaaS).
2. Purchasing model: Elastic pool with vCore, General Purpose, Provisioned. Each tenant gets a separate database.
3. Sizing: Start with 50-database pool, 4 vCore total (shared), min 1 vCore per database. ~50 eDTU per database average.
4. Cost: ~$200/month (US East, ~2000 vCore-hours). As customer count grows, add more vCores to the pool (no database migration).
5. Backup: Default 7-day geo-redundant backups; meets GDPR.

[!NOTE] Elastic pools are cheaper than 50 standalone databases because peak load of one tenant doesn't starve others. Monitor CPU/memory per database to detect runaway queries or noisy tenants.

Why Elastic Pools? Each SaaS tenant has unpredictable traffic. By pooling $50$ tenants in a single pool (e.g., max $200$ eDTU, min $10$ eDTU per tenant), you pay for shared capacity rather than $50$ separate databases. If Tenant A peaks at 9 AM and Tenant B peaks at 3 PM, the pool absorbs both. Cost savings: $60\%$ vs. individual databases.

Failover Logic: Set up an auto-failover group pointing to a secondary region (US-East). If the primary region fails, clients automatically reconnect to the secondary (RTO ~1 minute). Replication is asynchronous, so $5$–$10$ seconds of recent transactions may be lost.

Backup Strategy: SQL Database retains backups for $35$ days by default. Store weekly full backups in Azure Blob Storage for 7 years (audit requirement). Cost: $10/month for long-term retention.

Example 2: Migrating Legacy SQL Server ERP (Medium, LO12 + LO14 + LO15)

Problem: A financial services firm runs a 500 GB SQL Server 2019 ERP on-premise. They need <1 hour downtime, full-text search, replication, and column-level encryption for regulatory compliance.

Step-by-Step Solution:

1. Choose platform: SQL Managed Instance (supports replication, full-text, T-SQL, logins).
2. Sizing: Premium tier, 8 vCore (Managed Instance only supports vCore). 1 TB storage.
3. Backup: Native SQL backups to Azure Blob Storage; LRS + GRS (geo-redundant).
4. Security: Enable Always Encrypted for SSN, account numbers. Enable TDE (auto-on for Managed Instance).
5. Migration: Use Azure Data Migration Service to restore backup on Managed Instance; minimal downtime (backup + restore ~30 min for 500 GB).
6. Scaling: Add read replicas in secondary region via auto-failover group.

[!IMPORTANT] Always Encrypted requires re-running Column Encryption Key setup and parameterized queries in ERP app. Test on staging Managed Instance first.

Assessment Phase: Interview the current SQL Server DBA. Identify used features:

• SQL Agent for ETL jobs? → Managed Instance required
• Linked servers? → Managed Instance required
• Custom CLR assemblies? → Managed Instance or VMs
• Replication via SQL replication? → Managed Instance or VMs

If yes to any, Managed Instance is mandatory (SQL Database lacks these).

Sizing Phase: Export 3 months of DMV data (Query Store, sys.dm_db_resource_stats). Identify:

• Peak vCore usage (CPU + memory + I/O bottlenecks)
• Data size (including indexes, LOBs)
• Concurrent connections

Recommend vCore tier with $20\%$ headroom. Example: Peak $40$ vCores → provision $48$ vCores.

Cost Optimization: Model both Managed Instance (per-vCore pricing) and SQL Servers on VMs (Software Assurance discount). For large instances ($>72$ vCores), VMs may be cheaper due to bulk licensing.

Example 3: Real-Time Analytics on petabyte-scale data (Hard, LO13 + LO14)

Problem: A retailer processes $100M/year in transactions (1 billion rows/year). They need sub-second OLTP for checkout and hourly analytics on sales trends, inventory levels.

Step-by-Step Solution:

1. Split workload: OLTP → Azure SQL Hyperscale (transactional primary); Analytics → Read-only Hyperscale replica or Azure Synapse (separate analytics warehouse).
2. Sizing OLTP: Hyperscale, Business Critical, 16 vCore, auto-scale up to 32 vCore, 100 TB storage. RTO/RPO: 30 sec / 5 sec (auto-failover group).
3. Scaling analytics: 4 read-only Hyperscale replicas (no charge for replicas). Queries on replicas; no contention with OLTP.
4. Geo-redundancy: Replicate to secondary region (e.g., US West). Auto-failover if primary region fails.
5. Cost: ~$5k/month OLTP + ~$2k/month read replicas (shared compute).

[!TIP] Hyperscale read scale-out is free (replicas share compute). Use this for BI/reporting instead of Synapse if you don't need MPP (Massively Parallel Processing).

Sharding Strategy: Data is too large for a single instance ($>4$TB max). Shard by customer geography or tenant ID. Each shard resides in a separate database in a different region. Example: US shard (10TB), EU shard (8TB), APAC shard (6TB).

Read-Only Replicas: Each shard has read replicas in the same region for local analytics queries. Primary in shard database processes writes; replicas serve reporting.

Consistent Querying: All queries hitting the primary shard for transactions; reporting queries hit replicas. Cross-shard joins are difficult; denormalize reporting data via event hub → Azure Synapse.

Cost: Sharding adds infrastructure cost (3 shards × $48$ vCores per shard = $144$ vCores) and operational complexity (consistent backups, cross-shard transactions hard). But enables unlimited horizontal scale for multi-petabyte workloads.

Visual Explanations

Platform Selection Decision Tree

DTU vs vCore: Purchasing Model Comparison

Data Protection Layers

HA/DR Architecture with Auto-Failover Group

Multi-Tenant Elastic Pool Scaling

Always Encrypted vs. TDE Comparison

Common Mistakes

Mistake 1: Confusing DTU Limits with Storage Limits

Myth: "A Standard S2 database with 50 DTU can hold 100 GB of data."

Reality: DTU is compute (CPU/memory/I/O); storage is separate. A Standard S2 database has ~50 DTU and 250 GB storage (independently). You can have high DTU utilization with low storage, or vice versa.

Why it's tricky: Students often read "Standard 50 DTU" and assume the 50 also limits size. Pricing tables list both DTU and storage; they're orthogonal dimensions.

---

Mistake 2: Assuming Always Encrypted is Transparent to the App

Myth: "Once Always Encrypted is enabled on a column, all existing queries work unchanged."

Reality: Queries using string literals or non-parameterized queries fail. The driver must parameterize queries and handle encryption client-side. Stored procedures need rewrite; predicates on encrypted columns must be deterministic.

Why it's tricky: Transparent Data Encryption (TDE) is actually transparent; Always Encrypted is not. The names are confusing, and app refactoring is unavoidable for Always Encrypted adoption.

---

Mistake 3: Misunderstanding Elastic Pool Min/Max DTU

Myth: "An elastic pool with min DTU = 50, max DTU = 100 guarantees each database gets 50 DTU."

Reality: Min/max are per database, not aggregate. A 10-database pool with min 50 per DB reserves 500 eDTU total for the pool. If only 3 databases are active, they can share more. Max per DB (100) prevents runaway tenants from consuming the entire pool.

Why it's tricky: Students conflate "min" with "guaranteed allocation" rather than "reservation floor."

---

Mistake 4: Not Planning for Geo-Failover Complexity

Myth: "Auto-failover groups handle all HA/DR; I don't need to plan anything."

Reality: Auto-failover groups failover the database, but application connection strings, firewall rules, DNS (CNAME), and login replication must also be configured. Asynchronous replication has RPO (Recovery Point Objective), meaning last few seconds of writes may be lost. Test failover regularly.

Why it's tricky: HA/DR is not just database failover; the entire system (app, network, auth) must failover correctly.

---

Practice Exercises

Exercise 1: Platform Selection (Easy, LO12)

Scenario: A consulting firm has a 50 GB SQL Server 2008 R2 database with heavy use of SQL Agent jobs and custom Extended Stored Procedures. They want to migrate to Azure with minimal code changes.

Question: Which Azure platform is best?

A) Azure SQL Database
B) Azure SQL Managed Instance
C) Azure Database for PostgreSQL
D) SQL Server on VMs

▶💡 Hint

SQL Agent jobs and Extended Stored Procedures are not supported by Azure SQL Database. Consider which platforms offer near-native SQL Server feature parity.

▶✓ Solution

Answer: B) Azure SQL Managed Instance

SQL Managed Instance supports SQL Agent jobs, Extended Stored Procedures (via CLR), and most T-SQL. It's a managed service, so no VM patching. Option D (SQL Server on VMs) also works but requires more operations and SQL licensing overhead. Managed Instance is the lift-and-shift winner.

---

Exercise 2: DTU vs vCore Sizing (Medium, LO13)

Scenario: A reporting database with 200 GB, queries running every hour, CPU ~30%, memory ~40%. Currently on-premise SQL Server (4 CPU cores, 32 GB RAM), costs $300/month to operate (including staffing).

Question: Would you recommend DTU or vCore model? Why?

▶💡 Hint

Think about database size (200 GB is large), workload predictability (hourly queries are predictable), and cost sensitivity. DTU has size/performance limits; vCore offers more flexibility.

▶✓ Solution

Answer: vCore (General Purpose, 4 vCore, Provisioned)

200 GB is above DTU's practical sweet spot (~100 GB). Hourly queries are predictable, so Provisioned (not Serverless) is appropriate. 4 vCore matches current CPU cores. Monthly cost: ~$250–350 (cheaper than current on-premise ops when including labor). vCore allows reservation instances (RIs) for additional discounts if you commit 1–3 years.

---

Exercise 3: Elastic Pool Design (Medium, LO13 + LO14)

Scenario: A SaaS company has 100 customers. Customer A (largest) uses 8 vCore peak (10 AM–2 PM). Customer B uses 2 vCore peak (11 PM–1 AM). 98 other customers use <0.5 vCore each. Individual database costs are $300/month each. Elastic pool: 16 vCore shared,$800/month.

Question: Should they use an elastic pool? Calculate savings.

▶💡 Hint

Compute total costs: 100 databases * $300 = ? Compare to one elastic pool. Factor in non-overlapping peak times.

▶✓ Solution

Answer: Yes, use elastic pool. Savings: ~$29,200/year.

Current cost: 100 DBs * $300/month =$30,000/month.
Elastic pool cost: $800/month. Monthly savings:$30,000 - $800 =$29,200.
Yearly: ~$350,400 saved.

Non-overlapping peaks (A: 10 AM–2 PM, B: 11 PM–1 AM) allow both to fit in 16 vCore pool. The 98 small databases provide steady utilization. This is a textbook elastic pool win.

---

Exercise 4: Data Protection Layer Selection (Hard, LO15)

Scenario: A healthcare provider stores patient SSN, DOB, diagnoses, and medications in SQL Database. Compliance requires:

• Encryption at rest (HIPAA).
• Encryption in transit (TLS 1.2+).
• DBAs must NOT see PHI (Personally Identifiable Health Information).
• Reporting team needs access to de-identified data (masked SSN, real diagnosis).

Question: Design a data protection strategy using TDE, Always Encrypted, and DDM.

▶💡 Hint

Think about which columns are sensitive (SSN, DOB = keys), which can be masked (SSN for reports), and which stay plaintext (diagnosis for clinical use). Combine encryption layers.

▶✓ Solution

Answer: Three-layer approach:

1. TDE: Enable on all databases (HIPAA requirement, protects backups, transparent to app).
2. Always Encrypted (Deterministic): SSN, DOB columns. Keys in Azure Key Vault (app tier only). DBAs cannot query in plaintext.
3. DDM (Random Mask): SSN column → 'XXX-XX-' + SUBSTRING(SSN, 8, 4). Reporting team mapped to view with DDM applied.

Result: Backups encrypted (TDE), keys remote (Always Encrypted), DBAs blindfolded (Always Encrypted), reporting team sees de-identified data (DDM). HIPAA-compliant.

---

Exercise 5: Auto-Failover Configuration (Hard, LO14)

Scenario: A critical order-processing database (1 TB, 500k writes/sec) runs in US East. RTO requirement: 30 sec, RPO: 5 sec. You've set up an auto-failover group to US West.

Question: List the configuration steps and potential gotchas.

▶💡 Hint

Auto-failover groups include primary and secondary, but you must also configure firewalls, DNS, and app connection strings. What happens during failover?

▶✓ Solution

Answer: Configuration + Gotchas:

1. Primary DB (US East): Azure SQL Database, Business Critical, 16 vCore.
2. Secondary DB (US West): Auto-created by failover group, same tier.
3. Firewall: Allow app's client IP on both primary AND secondary servers.
4. Connection string: Use failover group listener (e.g., myserver.database.windows.net), which auto-routes to active primary.
5. Failover trigger: Auto or manual (you choose timeout).

Gotchas:

• Logins replicated? No—must sync logins manually (or use Microsoft Entra ID).
• Last ~5 seconds of writes lost if primary region fails (inherent async replication).
• Failover ~30 sec, but app reconnection may take longer if hardcoded to primary endpoint.
• Cost: Replicate to secondary = +50% (shared compute reduces cost).
• Test failover quarterly; don't assume it works.

---

Exercise 6: Serverless Configuration Trade-offs (Medium, LO13)

Scenario: You're designing a dev/test database that's used 8 AM–6 PM (8 hours/day), with 20 days/month active. Auto-pause after 1 hour idle. Choices: (A) Provisioned 2 vCore, (B) Serverless 2 vCore with auto-pause.

Question: Which costs less monthly?

▶💡 Hint

Provisioned: 730 hours/month * rate. Serverless: Only active hours billed, plus resumption delays. Calculate expected active hours.

▶✓ Solution

Answer: Serverless is cheaper (~40% savings).

Provisioned 2 vCore: 730 hours * $0.25/hour ≈$182.50/month.
Serverless 2 vCore (General Purpose): 8 hours * 20 days * $0.25/hour + ~1 hour/day pause/resume overhead ≈ 180 hours ≈$45/month.

Serverless wins because dev/test is low-utilization. Resume time (cold-start ~1 min) is acceptable. Gotcha: Always-on production workloads lose Serverless advantage because database rarely pauses.

---

Solution for Exercise 2 (DTU vs vCore Sizing):

Given: Workload peaks at $4000$ DTU, baseline $1000$ DTU. Current cost \3,500/month$ (S12 tier × 3 instances for HA).

Option A (DTU): Keep S12 tier; already at peak. Cost remains \3,500/month$.

Option B (vCore): $4000$ DTU ≈ $80$ vCores (rough conversion). Provision $96$ vCores (with headroom), $4$TB storage. Cost ≈ \4,200/month$(on-demand). Apply 1-year reserved instance: discount$40%$→$$2,500/month`. Winner: vCore with RI.

Solution for Exercise 4 (Data Protection):

Requirement: Encrypt sensitive columns (SSN, credit card) and prevent DBA snooping.

Best approach:

1. Enable TDE (default) for disk encryption
2. Implement Always Encrypted on SSN and credit card columns
3. Store encryption keys in Azure Key Vault; restrict DBA access
4. Implement DDM for SSN in non-production environments

Cost: Minimal; Always Encrypted adds 15% query latency on encrypted columns. Acceptable for security-first requirements.

Summary & Concept Map

Key Takeaways:

1. Platform choice drives architecture: Greenfield → Azure SQL Database; lift-and-shift → Managed Instance; exotic SQL features → SQL on VMs; open-source → PostgreSQL/MySQL.
2. DTU bundles compute; vCore unbundles: Choose DTU for small, unpredictable workloads; vCore for large, predictable, cost-sensitive prod.
3. Elastic pools reduce cost 30–40% for multi-tenant SaaS with non-overlapping peaks.
4. Scaling reads: Use read-only replicas (free with Hyperscale), auto-failover groups (HA/DR in seconds), or sharding (single-tenant partitioning).
5. Data protection is layered: TDE (disk), Always Encrypted (columns, zero-knowledge), DDM (query-time obfuscation), RLS (row-level access control).
6. Serverless is dev-friendly: Auto-pauses after 1 hour idle; ~40% cheaper for <100 hours/month usage.
7. Test failover regularly: Auto-failover group handles DB, but app, firewall, DNS must also failover. RPO/RTO vary by tier.

Concept Map

Connections & Next Steps

Reading Order (by Learning Objective):

1. LO12 — Recommend a Relational Storage Solution: Start here. Understand the five platforms, decision criteria (migration path, feature parity, cost), and when each excels. See the dedicated LO12 lesson.
2. LO13 — Design Database Tier and Sizing: Next, master purchasing models (DTU vs vCore), compute tiers (General Purpose, Business Critical, Hyperscale, Serverless), and elastic pools. See the LO13 lesson.
3. LO14 — Design Scalability: Explore read-only replicas, sharding patterns, geo-replication, and auto-failover groups. See the LO14 lesson.
4. LO15 — Design Data Protection: Finalize your design with encryption (TDE, Always Encrypted), masking (DDM, RLS), and auditing. See the LO15 lesson.

Next Topic: "Manage relational databases in Azure" (Unit 2, Topic 2) covers monitoring, backups, patching, and disaster recovery automation.

Related Topics:

• Unit 1: "Azure infrastructure fundamentals" (networking, identity, compliance).
• Unit 3: "Design NoSQL and unstructured data solutions" (Cosmos DB, Blob Storage).

Exam Alignment: AZ-305 tests all five LOs equally. Expect scenario-based questions: "Choose the cheapest platform for [workload]." "Design HA/DR for [requirement]." "Explain why Always Encrypted requires app changes." Master this lesson to ace data storage on the exam.