Design Data Storage Solutions — Unit Lesson

This lesson provides a comprehensive survey of Azure's data storage and integration capabilities, covering relational databases, data integration platforms, and nonrelational storage solutions. You'll learn how to evaluate different data storage technologies, design scalable architectures, and select the right tool for each workload. The Azure data platform has evolved dramatically with services like Azure SQL Database, Azure Cosmos DB, Azure Synapse Analytics, and Azure Data Factory offering specialized solutions for diverse data scenarios—from traditional OLTP to modern cloud-native analytics.

Reference: Ch. 2, §2.1–§2.4, p. 51–93 of the AZ-305 exam book (Designing Microsoft Azure Infrastructure Solutions, by Ashish Agrawal et al., Pearson Education, 2023).

Why This Matters

Data storage decisions are among the most critical architectural choices you make. A poor storage selection can lead to performance bottlenecks, cost overruns, compliance violations, and operational headaches that are expensive to remediate. Azure offers over a dozen storage solutions, each optimized for specific scenarios:

• Relational databases like Azure SQL Database and Azure SQL Managed Instance handle transactional workloads with ACID guarantees and complex queries.
• Data integration services like Azure Data Factory and Azure Databricks orchestrate ETL/ELT pipelines and analytical computations.
• Data warehouse and analytics platforms like Azure Synapse Analytics enable massive parallel processing of big data.
• NoSQL stores like Azure Cosmos DB and Azure Table Storage provide globally distributed, schemaless storage with extreme scalability.
• Data lakes like Azure Data Lake Storage Gen 2 combine Blob Storage flexibility with hierarchical namespaces and fine-grained access control.

Understanding the strengths, limitations, and cost characteristics of each service ensures you design systems that are performant, secure, cost-effective, and aligned with business requirements.

Prerequisites

Before starting this lesson, you should be familiar with:

• Basic database concepts: RDBMS, tables, schemas, queries, transactions, ACID properties
• Azure subscription fundamentals and resource groups
• Networking basics: IP addresses, firewalls, virtual networks, public vs. private endpoints
• Encryption and security concepts: symmetric/asymmetric encryption, certificates, keys, TLS
• Data integration concepts: ETL, ELT, data pipelines, transforms
• Basic understanding of cloud compute models: IaaS, PaaS, SaaS
• Familiarity with Azure Portal, az CLI, or PowerShell (helpful for examples)

Learning Objectives

After completing this lesson, you will be able to:

• Design relational data solutions by selecting among Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs, and justifying your choice based on scalability, licensing, feature requirements, and cost.
• Choose appropriate scaling methods (serverless autoscaling, sharding, read scale-out, elastic pools) for relational workloads and explain trade-offs in complexity and management overhead.
• Design data integration architectures using Azure Data Factory, Azure Databricks, and Azure Synapse Analytics to build ETL/ELT pipelines for structured and unstructured data.
• Recommend nonrelational storage solutions (Azure Cosmos DB, Azure Blob Storage, Azure Table Storage, Azure File Share) based on data model, access patterns, and geographic distribution requirements.
• Apply data protection strategies including encryption (TDE, Always Encrypted, TLS), network isolation (service endpoints, private endpoints), access control (RBAC, managed identities), and data resilience (snapshots, soft delete, versioning).
• Evaluate cost and performance trade-offs when choosing between storage tiers, consistency models, and provisioning approaches.

Building Blocks

Azure SQL Family

The Azure SQL family provides managed relational database services ranging from lightweight single databases to fully managed instances:

• Azure SQL Database: Multi-tenant PaaS service, managed backups, automatic patching, per-database scaling. No SQL Agent, SSIS, SSRS, or cross-database transactions by default.
• Azure SQL Managed Instance: Single-tenant PaaS service with near-100% SQL Server compatibility. Supports SQL Agent, SSIS, SSAS, SSRS, database mail, linked servers, and cross-database transactions.
• SQL Server on Azure VMs: IaaS service offering complete control over OS, SQL Server version, and all SQL Server features (Always On Availability Groups, database mirroring, log shipping). Higher management burden but maximum flexibility.

Purchasing Models

• DTU-based (Database Transaction Unit): Blended measure of CPU, memory, and I/O. Tiers are Basic ($5–$15/month), Standard, and Premium. Simpler for predictable, moderate workloads. Up to 4 TB storage.
• vCore-based: Independently choose compute (1–80 vCores), memory, and storage. Tiers are General Purpose (for most workloads), Business Critical (premium performance, failover replicas), and Hyperscale (up to 100 TB storage, rapid scaling). More flexible and cost-efficient for variable workloads.

Scaling Strategies for Relational Data

1. Serverless autoscaling: Compute auto-pauses when idle; vCore count scales between min and max based on demand. Ideal for dev/test and bursty workloads.
2. Sharding: Horizontal scaling by splitting data across multiple databases using a shard key and shard map manager. Customer-managed but enables unlimited scale.
3. Read Scale-Out: Use ApplicationIntent=ReadOnly parameter to route read queries to secondary replicas. No autoscaling; suitable for read-heavy workloads.
4. Elastic Pools: Share resources (DTUs/vCores) across multiple databases within min-max limits. Reduces per-database peak costs when usage patterns are diverse.

Encryption Strategies

• At Rest: Transparent Data Encryption (TDE) with Data Encryption Key (DEK) encrypted by Key Encryption Key (KEK) in Azure Key Vault.
• In Transit: TLS for all external connections; IPsec VPN or MACsec for network traffic.
• In Use: Always Encrypted with column encryption keys and column master keys, enabling queries on encrypted columns without decryption on the server.

Azure Data Integration Services

• Azure Data Factory: Cloud-based ETL/ELT service. Defines Linked Services (connectors), Datasets (data structures), Activities (copy, transform, control flow), Pipelines, and Triggers (schedule, event, tumbling window). Supports 90+ connectors and built-in transformations.
• Azure Databricks: Apache Spark-based analytics platform with notebooks, collaborative workspaces, job scheduling, and integration with Blob Storage, ADLS, and Cosmos DB.
• Azure Data Lake Storage Gen 2: Hierarchical namespace on Blob Storage, POSIX ACLs, optimized for analytics workloads.
• Azure Synapse Analytics: Evolution of SQL Data Warehouse, combining dedicated SQL pool (MPP with DWUs), serverless SQL pool (pay-per-query), and Spark pool in a unified studio.

NoSQL and Nonrelational Storage

• Azure Cosmos DB: Globally distributed, multimodel (SQL, MongoDB, Cassandra, Gremlin, Table APIs), 99.999% availability, sub-10 ms latency, automatic indexing.
• Azure Table Storage: Schemaless key-value store (partition key + row key), up to 1 MB entities, highly available and cost-effective.
• Azure Blob Storage: Object storage for unstructured data (videos, images, backups). Hot, Cool, and Archive tiers with different availability and cost profiles.
• Azure File Share: SMB/NFS network file system, mountable on Windows/Linux/macOS.

Deep Dive

Topic 1: Design Relational Data Storage Solutions

Relational databases remain the foundation of enterprise applications. Choosing between Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs requires careful analysis:

Azure SQL Database suits cloud-native applications, SaaS offerings, and workloads that don't need SQL Server-specific features. You get automatic backups (up to 35 days), point-in-time restore, geo-replication, and transparent patching. Cost scales with compute and storage. The DTU and vCore models offer different pricing philosophies: DTU for unpredictable workloads, vCore for those with known resource requirements.

Azure SQL Managed Instance is ideal when migrating legacy on-premises SQL Server databases with minimal refactoring. It supports SQL Agent for scheduled jobs, SSIS for ETL packages, SSAS for multidimensional analysis, and SSRS for reporting—features absent from Azure SQL Database. Cross-database transactions and linked servers enable multi-database queries. Always On Failover Groups provide automatic failover with zero RPO (Recovery Point Objective).

SQL Server on Azure VMs gives you full control: custom configurations, all SQL Server editions, Always On Availability Groups, database mirroring, and third-party extensions. You manage OS patching, SQL Server updates, and backups. This flexibility comes with operational overhead and higher licensing costs (unless using BYOL).

Scaling relational data depends on workload characteristics:

• Vertical scaling (DTU/vCore increase): Simple but has hardware limits. Causes brief downtime in Basic/Standard tiers; Premium and Business Critical use read replicas to minimize impact.
• Serverless autoscaling: vCore count adjusts automatically, and the database pauses when idle (no charges). Ideal for dev/test, bursty analytics, and variable workloads. Adds USD 3–5 per database per month.
• Sharding: Implement application logic to route requests to a shard (database) based on a shard key (e.g., customer ID). Use a shard map manager (custom or Azure SQL Elastic Database Tools) to track which key ranges belong to which shard. Enables unlimited scale but increases complexity.
• Read Scale-Out: Deploy read-only secondary replicas and set ApplicationIntent=ReadOnly in connection strings. Separates read and write traffic. Replicas are synchronous in Business Critical tier, asynchronous in General Purpose.
• Elastic Pools: For SaaS multi-tenant scenarios, pool databases and allocate shared DTUs/vCores. During peak hours, some tenants use more resources; during off-hours, others spike. Total peak demand is typically less than sum of individual peaks, reducing per-tenant cost.

Encryption and compliance are non-negotiable. Enable TDE by default (SQL Database does automatically). Use Always Encrypted for columns containing PII, credit cards, or regulated data—the server never sees plaintext values. Store column master keys in Azure Key Vault and restrict access using managed identities or Microsoft Entra ID. Configure firewall rules and Microsoft Entra ID authentication to prevent unauthorized access.

Topic 2: Design Data Integration and Analytics Pipelines

Modern data architecture separates ingestion, transformation, and analytics into specialized stages, each handled by purpose-built services:

Azure Data Factory orchestrates data movement and transformation. Define Linked Services to connect to sources (SQL Server, Salesforce, REST APIs) and sinks (Blob Storage, Cosmos DB, Data Warehouse). Use Copy Activity to move data, Data Flow for visual transformations (joining, aggregating, pivoting), and Lookup Activity for dynamic configuration. Pipelines chain activities; Triggers execute them on schedule (e.g., daily), via tumbling window (e.g., every hour with late-arrival handling), or on events (e.g., when a file lands in Blob Storage). Integration Runtimes abstract the compute: Azure IR for cloud sources, Self-Hosted IR for on-premises databases, Azure-SSIS IR for legacy SSIS packages.

Azure Databricks enables collaborative data science and engineering. Teams write Spark code in notebooks (Python, Scala, SQL, R), share results, and schedule jobs. Cluster management is automatic; attach multiple notebooks to a cluster for interactive development or job runs. Databricks SQL provides a SQL interface; Databricks ML supports model training and deployment. DBFS (Databricks File System) provides unified storage access; mount Azure Blob Storage or ADLS Gen 2 for data lakes.

Azure Synapse Analytics combines data warehouse and big data analytics. Dedicate a SQL pool for structured queries with MPP (Massive Parallel Processing); data is distributed across nodes by a distribution key (round-robin, hash, or replicated). Use DWUs (Data Warehouse Units) to provision compute and storage; 10 DWUs is entry-level. Serverless SQL pool lets you query data in place (Blob or ADLS) without provisioning. Apache Spark pools handle unstructured data processing and machine learning. The Synapse Studio integrates data integration (same as ADF), SQL editing, Spark notebooks, and Power BI visuals.

Data Lake design with ADLS Gen 2 follows Bronze-Silver-Gold (raw-processed-refined) zones. Bronze stores raw ingested data; Silver contains cleaned, validated, deduplicated data; Gold provides curated, business-ready datasets for analytics. Use hierarchical namespace (HNS) for folder structures and POSIX ACLs for fine-grained security.

Topic 3: Recommend Storage Solutions for All Data Types and Design Nonrelational Data Storage

Choosing the right storage involves understanding your data model, access patterns, scale, and consistency requirements:

For relational data, the decision tree is:

1. Do you need SQL Server-specific features (SQL Agent, SSIS, SSRS, Always On AG, cross-database transactions)?

   • Yes → Azure SQL Managed Instance or SQL Server on Azure VMs
   • No → Azure SQL Database

2. Do you need max

imum control and on-premises compatibility?

• Yes → SQL Server on Azure VMs
• No → Managed option (Database or MI)

1. How much data? DTU limits are 4 TB; vCore allows 100 TB in Hyperscale tier.

2. What's your read/write ratio? Heavy reads benefit from read replicas (Read Scale-Out) or read-only secondaries (Failover Groups).

For semi-structured data (XML, JSON, YAML), options include:

• Azure Cosmos DB with SQL API for flexible schemas and rich querying.
• Azure Table Storage for simple key-value pairs.
• Azure Blob Storage if you're archiving documents.

For nonrelational/NoSQL data, the decision depends on the data model:

• Key-value: Azure Table Storage (simple, cheap), Azure Cosmos DB (global distribution, consistency tuning), Azure Managed Redis (in-memory, high throughput).
• Document (JSON/BSON): Azure Cosmos DB SQL or MongoDB API; MongoDB on VMs if you need full control.
• Column-family: Azure Cosmos DB Cassandra API; Apache HBase on HDInsight if you need open-source.
• Graph: Azure Cosmos DB with Gremlin API for traversal queries.
• Time-series: Azure Time Series Insights for IoT data; OpenTSDB on HDInsight for open-source.
• Object/Blob: Azure Blob Storage (hot, cool, archive tiers) for files; Azure Data Lake Storage Gen 2 for analytics workloads with access control.

Azure Cosmos DB excels in global distribution, offering 99.999% availability across multiple regions, automatic failover, and multi-master replication. Consistency levels (Strong, Bounded Staleness, Session, Consistent Prefix, Eventual) balance latency and consistency. Autoscale can handle unpredictable traffic. Cost is based on RUs (Request Units)—each operation consumes RUs; scale from 400 RU/s to millions.

Azure Storage tiers optimize cost for different access patterns:

• Hot: 99.99% SLA, RA-GRS replication, immediate access. For frequently accessed data.
• Cool: 99.9% SLA, RA-GRS replication, 30-day minimum retention. For infrequently accessed data; early deletion incurs charges.
• Archive: Offline, retrieval takes hours, 180-day minimum retention. For compliance/long-term backup.

Data protection in Azure Storage includes:

• Resource Manager locks: CannotDelete or ReadOnly prevent accidental modifications.
• Blob versioning: Maintain version history; automatic or manual snapshots.
• Soft delete: Recover deleted blobs within 1–365 days.
• Immutable storage: Time-based retention or legal hold prevents modification or deletion.
• Point-in-time restore: Restore all blobs in a container to a prior state.

Access control layers include:

• Account keys: Symmetric shared secrets; high privilege, should rotate every 90 days.
• SAS tokens: Time-limited, scoped permissions; better than keys for delegated access.
• Microsoft Entra ID with RBAC: Role-based access (Storage Blob Data Owner, Contributor, Reader); integrates with on-premises AD via Microsoft Entra Connect Sync.
• Private endpoints: Network-level access control; traffic stays within VNet, avoiding public internet.
• Service endpoints: Restrict storage to VNet subnets; still uses public IP but firewall-controlled.

Worked Examples

Example 1 (Easy): Migrate a Simple Web Application Database

Scenario: Your company runs a transactional web app (e-commerce, CRM) on SQL Server 2019 on-premises. Traffic is steady, $\sim 100$ concurrent users. Database is 50 GB, mostly read (reads:writes = 70:30). You want to migrate to Azure with minimal code changes and zero downtime.

Analysis:

• Data size (50 GB) easily fits in Azure SQL Database or Azure SQL Managed Instance.
• Steady traffic favors provisioned compute over serverless.
• Read-heavy (70%) suggests read replicas to offload reads.
• No mention of SQL Agent, SSIS, SSRS → Azure SQL Database is sufficient.
• Zero-downtime migration → use Azure Database Migration Service (DMS) with Always On in target to support reads during migration.

Design Decision: Provision Azure SQL Database with vCore model (General Purpose, 4 vCores), read scale-out enabled (2 secondary replicas). Set up Active Geo-Replication for disaster recovery to a secondary region.

Implementation:

Outcome: Read replicas absorb the 70% read traffic; writes go to primary. Cost is about USD 600–800/month; failover is automatic if primary fails.

Example 2 (Medium): Design a Multi-Tenant SaaS Data Layer

Scenario: You're building a SaaS platform serving 1000+ tenants. Each tenant has $\sim 100$ MB of relational data (users, orders, analytics). Some tenants are heavy users (100 concurrent connections), others are light (10 concurrent). You want to optimize cost and ensure tenant isolation.

Analysis:

• Total data: $$1000 \times 100$MB= 100$ GB (within Azure SQL Database limits).
• Variable load across tenants → elastic pool for cost optimization.
• Tenant isolation is critical → separate database per tenant or logical isolation within a shared database.
• Thousands of databases → sharding with a shard map manager.

Design Decision: Use Elastic Pools with database-per-tenant. Each tenant has a dedicated database; all databases share a pool of DTUs/vCores. Pool size: 20 vCores, max $$15$/vCore, $$\sim$$300$/month for 1000 databases.

Architecture:

Connection Logic:

Outcome: Cost per tenant is $$\sim$$0.30$/month (shared pool). Heavy tenants don't degrade light tenants (DTU caps). Auto-scaling adjusts pool size as load grows.

Example 3 (Hard): Design a Global Analytics Platform with Multi-Region Data

Scenario: Your company operates globally (EU, APAC, US). You need to ingest clickstream, IoT sensor, and transactional data from each region, clean and unify it in a data lake, and enable real-time dashboards and ad-hoc queries. Data volume: 50 GB/day, 99.99% uptime required, latency $< 5$ seconds for queries.

Analysis:

• High-velocity, high-volume data → Azure Databricks or Synapse Spark for ingestion and transformations.
• Multiple regions → data sovereignty (store EU data in EU) and latency (store data close to users).
• Real-time dashboards → Azure Cosmos DB for fast reads, or Synapse Serverless SQL for ad-hoc queries on data lake.
• 99.99% uptime → multi-region failover, automated DR.

Design: Implement a Lambda architecture (batch + real-time):

Multi-Region Deployment:

Data Flow: Regional IoT sensors → Event Hubs → Kafka → Spark cluster (scales auto) → Cosmos DB (real-time reads, 99.999% availability) and Blob Storage (cold archive). Nightly batch processes raw → processed → curated layers. Synapse Serverless SQL provides SQL interface over Parquet files in Gold zone.

Cost Optimization: Cosmos DB uses autoscale RUs (adjust RU/s every minute based on demand); archive cold data to Archive tier ($$0.00099$/GB/month vs.$$0.02$/GB for Hot). Synapse Spark uses spot instances for non-critical batch jobs (70% savings).

Outcome: Sub-second dashboard latency via Cosmos DB; ad-hoc queries via Serverless SQL (pay per TB scanned, $$5/TB); 99.99% uptime with multi-region failover; monthly cost \sim $15,000$ (Cosmos RUs, Spark, storage, Synapse).

Visual Explanations

Azure SQL Family Selection Flowchart

Data Integration Pipeline Architecture

NoSQL Data Model Selection Matrix

Storage Tier Comparison Table

Encryption In-Depth Comparison

Azure Synapse Analytics MPP Distribution

Data Protection Mechanisms Table

Common Mistakes

Myth 1: Bigger Database = More Performance

Reality: Performance depends on vCore count, not storage size. Increasing storage without vCores doesn't improve query speed. Identify bottlenecks (CPU, memory, I/O) and scale the appropriate dimension. Use Query Store to find slow queries; index properly. Storage scales independently in vCore model.

Myth 2: DTU-Based Pricing Is Always Cheaper

Reality: vCore pricing is often cheaper for predictable, high-volume workloads. DTU bundles CPU, memory, and I/O; you pay for all three even if you only need CPU. vCore lets you right-size: pick 4 vCores and 128 GB storage if that's your need. For small databases ($< 5`GB,`< 10$ concurrent users), DTU Basic is cheaper. Do the math for your workload.

Myth 3: Serverless Auto-Pause Saves Money for Always-On Apps

Reality: Serverless is only cost-effective for idle or bursty workloads. If your database runs $24/7$, auto-pause never triggers; you're paying compute + $3–$5 premium with no benefit. Serverless adds cold-start latency (10–30 seconds after wake) unsuitable for low-latency apps. Use provisioned compute for always-on workloads.

Myth 4: Cosmos DB Is Always the Best Choice for Distributed Data

Reality: Cosmos DB's global distribution, high availability, and flexible APIs come at a cost ($\sim $1$ per 100 RUs/second at autoscale). For workloads that don't need global distribution or high availability, ADLS Gen 2 ($$0.02$/GB) or Azure SQL Database with read replicas are cheaper. Evaluate RU/second required; if $< 1000$, Cosmos DB makes sense. If $> 50000$, consider Synapse.

Myth 5: Encryption Adds Negligible Overhead

Reality: Transparent Data Encryption (TDE) is negligible. Always Encrypted requires CPU for encrypt/decrypt on the client; queries on encrypted columns are slower. For heavily encrypted tables, throughput may drop $10{-}20\%$. Test with production-like data volumes before assuming encryption is free.

Myth 6: Multi-Region Failover Is Automatic in Azure SQL

Reality: Manual failover is fast ($< 1$ minute for Premium tier) but not automatic. Auto-failover requires Failover Groups (additional cost, $\sim $0.74$/failover group/month). Single-region databases fail silently without Failover Groups; you must manually reconnect clients.

Practice Exercises

Exercise 1: Evaluate DTU vs. vCore Pricing

Scenario: Your database averages 100 DTUs but spikes to 300 DTUs during peak hours. Current cost on Standard DTU: $$296$/month. How much would vCore cost?

Task:

1. Estimate vCore equivalent: 300 DTU (peak) ≈ $\sim 12$ vCores. Use the Azure Pricing Calculator.
2. Calculate vCore cost (General Purpose, 12 vCores, 100 GB storage) for eastus region.
3. Compare to DTU. Which is cheaper?

[!TIP] Use https://azure.microsoft.com/pricing/calculator/ and filter for "SQL Database." Ensure you account for RA-GRS geo-replication and read replicas in both models.

▶Details & Hint

DTU to vCore rough mapping: 3 DTU ≈ 1 vCore. So 300 DTU peak ≈ 100 vCores, but actual cost depends on region, commitment discounts, and workload profile. Use the Azure calculator for real-world pricing.

▶Details & Solution

• Standard DTU (300 DTUs): $$296$/month (given).
• General Purpose vCore (12 vCores, 100 GB, eastus): about USD 600–800/month without reserved instances. With 1-year reserved instances: about USD 400–500/month.
• Recommendation: vCore is cost-effective if you commit to 1-year or 3-year reserved instances. If unpredictable, DTU is cheaper month-to-month.

Exercise 2: Design Sharding Strategy

Scenario: Your SaaS app has $10,000 tenants, 10 GB each (100 TB total). Single Azure SQL Database can't scale beyond 100 TB (Hyperscale limit). You need to shard.

Task:

1. Choose a shard key (tenant ID, customer region, or hash(tenant ID)).
2. Design shard mapping (how many shards? how many tenants per shard?).
3. Outline the shard map manager architecture.
4. Describe failover strategy for a shard.

[!IMPORTANT] Shard key selection is critical. Avoid keys that lead to hot shards (one shard much busier than others).

▶Details & Hint

Consider tenant_id as shard key (distributed, uniform cardinality). With $10,000 tenants and max $\sim 1000$ tenants per shard, you'd need 10 shards. Alternatively, hash(tenant_id) % 10. Maintain a global shard map in a master database; each shard database contains local shard maps.

▶Details & Solution

• Shard key: tenant_id (directly maps to customer; easy to reason about).
• Shard count: 10 shards ($1,000 tenants/shard, 10 GB/shard).
• Shard map architecture:
  • Master database (single instance, non-sharded):
    • Global shard map table: tenant_id → shard_id
    • Shard list table: shard_id → connection string
  • Shard databases (10 instances):
    • Tenant tables (normalized schema) with WHERE tenant_id = @currentTenant filter in app.
    • Local shard map (for within-shard operations).
• Failover: Each shard has a read replica in a secondary region. Failover Group automatically switches on failure (RTO $< 1$ minute, RPO = 0 in synchronous mode).
• Cost: 10 databases (about USD 50–80/month each) = USD 500–800/month + 1 master database (USD 50/month) about USD 600/month total.

Exercise 3: Select a Data Lake Tier

Scenario: You have 5 TB of raw sensor data, mostly accessed within 30 days, then archived. Storage cost is a concern.

Task:

1. Estimate storage costs for Hot, Cool, and Archive tiers over 1 year.
2. Recommend the optimal strategy (single tier vs. lifecycle policy).
3. Calculate retrieval cost if you need to recover 100 GB from Archive.

▶Details & Hint

Hot: $$0.0184$/GB/month; Cool: $$0.0092$/GB/month; Archive: $$0.00099$/GB/month. Early deletion penalty for Cool (30 days) and Archive (180 days) applies if you delete before the minimum retention.

▶Details & Solution

• Hot tier (year 1): $5,000 GB $\times $0.0184 \times 12 = $1,104$/year.
• Cool tier (year 1): $5,000 GB $\times $0.0092 \times 12 = $552$/year. Early deletion cost if deleted < 30 days: negligible for archival data.
• Archive tier (years 2+): $5,000 GB $\times $0.00099 \times 12 = $60$/year.
• Optimal strategy: Lifecycle policy: Hot for 30 days, Cool for 150 days, Archive thereafter.
  • Year 1 cost: ($5,000 \times $0.0184 \times 1$) + ($5,000 \times $0.0092 \times 5$) + ($5,000 \times $0.00099 \times 6$)$\approx $92 + $230 + $30 = $352$ (blended).
• Retrieval cost from Archive (100 GB): Rehydration (standard priority) + storage tier change. Cost: 100 GB $\times $0.01$ (retrieval) + 1 hour rehydration (negligible) $= $1$.
• Annual savings: $$1,104 - $352 = $752$ vs. keeping all data Hot.

Exercise 4: Cosmos DB Consistency and RU Estimation

Scenario: You're designing a shopping cart service using Cosmos DB. Users add/remove items; the cart must reflect changes immediately. Estimate RUs.

Task:

1. Which consistency level? (Strong, Bounded Staleness, Session, Consistent Prefix, Eventual)
2. Estimate RU consumption: $10,000 reads/sec (cart views), $1,000 writes/sec (add to cart).
3. Calculate monthly cost with autoscale.

[!NOTE] A simple point read (get by ID) costs $\sim 1$ RU. A write (insert/update) costs about 5–10 RU depending on item size and indexing.

▶Details & Hint

Shopping cart requires immediate consistency (user adds item, immediately visible). Session consistency is typical for web apps (consistency within the user's session). Strong consistency guarantees linearizability but adds latency and cost.

▶Details & Solution

• Consistency level: Session. Reads and writes from the same user are always consistent; different users may see slight delay. Best balance of latency and cost.
• RU estimation:
  • Reads: $10,000/s \times 1$RU$= 10,000$ RU/s
  • Writes: $1,000/s \times 8 RU (average document size \sim 1KB)= 8,000$ RU/s
  • Total: $18,000 RU/s (peak).
• Provisioning: Autoscale, max $18,000 RU/s.
• Cost: $$0.012$ per RU-hour. Assuming $18,000 RU/s for 8 hours/day (peak), $\sim 9,000`RU/s average:`(9,000 \times 730 \text{ hours/month} \times $0.012) = $78,840$/month (very high!). Use Burst capacity or Dedicated throughput ($$0.11$ per RU-second) if traffic is predictable: $18,000 \times 0.11 = $1,980$/month.
• Recommendation: Investigate query patterns; cache frequently accessed items; use materialized views in Cosmos DB to reduce RU consumption.

Exercise 5: Data Factory Pipeline Design

Scenario: Ingest customer data from Salesforce (daily extract), validate, and load into Azure SQL Database. Errors must trigger an alert.

Task:

1. Design the ADF pipeline (activities, linked services, error handling).
2. Write the pipeline JSON (simplified).
3. Describe the trigger (schedule, event-based, manual).

▶Details & Hint

Pipeline structure: Lookup (Salesforce, fetch last run timestamp) → Copy (extract customers) → Data Flow (validate, deduplicate) → Copy (load to SQL) → If error, send notification (Webhook, email).

▶Details & Solution

Pipeline JSON:

jsonCopy

{ "name": "SalesforceToSQL", "properties": { "activities": [ { "name": "CopySalesforceToSQL", "type": "Copy", "inputs": [{"referenceName": "SalesforceDataset"}], "outputs": [{"referenceName": "SQLDataset"}], "linkedServiceName": {"referenceName": "SalesforceLS"}, "source": {"type": "SalesforceSource"}, "sink": {"type": "AzureSqlSink", "writeBehavior": "upsert"} }, { "name": "OnFailure", "type": "WebActivity", "dependsOn": [{"activity": "CopySalesforceToSQL", "dependencyConditions": ["Failed"]}], "inputs": [], "policy": {"retry": 0, "timeout": "1.00:00:00"}, "typeProperties": { "url": "https://your-webhook.azurewebsites.net/alert", "method": "POST", "body": {"error": "Pipeline failed", "timestamp": "@utcnow()"} } } ] }, "triggers": [ { "name": "DailyTrigger", "type": "ScheduleTrigger", "recurrence": {"frequency": "Day", "interval": 1, "startTime": "2026-01-01T02:00:00"} } ] }

Trigger: ScheduleTrigger fires daily at 2 AM, executing the pipeline. On failure, WebActivity sends a POST to a webhook (e.g., Azure Logic Apps for email notification).

Exercise 6: Hybrid Scenario - Relational + Lake

Scenario: Your company has a transactional ERP database (on-premises SQL Server) and wants to enable analytics without impacting production. Design a solution.

Task:

1. Extract nightly from ERP to ADLS Gen 2.
2. Transform in Synapse Spark.
3. Load curated data back to Synapse SQL pool for dashboards.
4. Estimate infrastructure cost.

▶Details & Hint

Use Azure Data Factory Self-Hosted IR to extract from on-premises SQL Server. Schedule nightly pipeline (e.g., 11 PM). Transform with Spark; load to Synapse SQL pool (dedicated, always-on). Use serverless SQL for exploratory queries.

▶Details & Solution

Architecture:

codeCopy

On-Premises SQL Server (ERP) ↓ (Self-Hosted IR) Azure Data Factory (Copy activity) ↓ ADLS Gen 2 Bronze (raw nightly export, $\sim 100$ GB) ↓ (Spark transformation) Synapse Apache Spark Pool (2–4 nodes, $\sim 30$ min/night) ↓ ADLS Gen 2 Silver (processed, deduplicated, $\sim 50$ GB) ↓ (Synapse SQL) Synapse Dedicated SQL Pool (10 DWUs, always-on) ↓ Power BI (dashboards, refresh hourly from SQL pool)

Cost estimate (monthly):

• Data Factory: 1 pipeline run/day, $\sim 5$ min execution, $$1$ per $1,000 DIU-hours → $\sim $0.15$/month.
• Self-Hosted IR: VM (Standard_D2s_v3) $\sim $100$/month.
• ADLS Gen 2: 150 GB storage, $$0.02$/GB/month $\sim $3$/month.
• Synapse Spark: 2 nodes, $0.5 hours/night, $$0.3$/node-hour → $$9$/month.
• Synapse SQL Pool: 10 DWUs, $24/7$,$$0.88$/DWU-hour → $10 \times 0.88 \times 730 = $6,424$/month.
• Power BI Premium: $\sim $2,200$/month for 10 users.
• Total: $\sim $8,750$/month.

Optimization: Use Synapse Serverless SQL instead of Dedicated Pool for exploratory queries (pay per TB scanned, $$5$/TB). Only use Dedicated Pool for high-frequency dashboards.

Summary & Concept Map

Key takeaways:

• Azure SQL Database is ideal for cloud-native apps; Managed Instance for legacy migration; SQL Server on VMs for full control.
• Choose DTU for unpredictable workloads; vCore for predictable, large-scale workloads. Serverless is only cost-effective for idle workloads.
• Data Factory orchestrates ETL; Databricks and Synapse enable analytics at scale. Data Lake Storage Gen 2 is the foundation for modern data platforms.
• Cosmos DB excels in global distribution and low latency; Azure Storage tiers optimize cost for access patterns.
• Always encrypt sensitive data (TDE, Always Encrypted); use managed identities and Microsoft Entra ID for access control; implement resource locks and soft delete for protection.
• Evaluate every storage recommendation against requirements: data model, volume, velocity, latency, consistency, compliance, and cost.

Concept map:

Connections & Next Steps

What comes next:

• Unit 3: Design messaging and event solutions. Learn about Azure Service Bus, Event Grid, and Event Hubs for decoupled, scalable architectures.
• Unit 4: Design compute solutions. Explore Azure App Service, AKS, and serverless functions to host your applications.
• Unit 5: Design network solutions. Understand virtual networks, load balancers, and DDoS protection to secure and scale your infrastructure.
• Deeper dives:
  • Advanced scaling: implement CQRS (Command Query Responsibility Segregation) with Cosmos DB and Synapse for event-driven architectures.
  • Security hardening: use Azure Key Vault for key management; configure Virtual Network Service Endpoints and Private Link for network isolation.
  • Cost optimization: use Azure Cost Management, reserved instances, and spot pricing to reduce your cloud bill.
  • Real-time analytics: combine Event Hubs, Spark, and Cosmos DB for sub-second dashboards.

Real-World Applications

Case Study 1: E-Commerce Platform Scaling

Context: An online retailer grew from startup ($$100K`ARR) to unicorn (`$100M$ ARR) over 5 years. Initial architecture used a single on-premises SQL Server; database became a bottleneck during Black Friday (traffic spike 10x).

Challenge: Existing SQL Server couldn't scale horizontally; vertical scaling hit CPU and memory limits. Downtime during peak sales cost $$50K$/hour in lost revenue.

Solution:

1. Migration to Managed Instance (Phase 1): Lift-and-shift existing SQL Server using Azure Database Migration Service. Replicated schema, indexes, stored procedures with zero refactoring. Failover Groups provided automatic DR.
2. Sharding (Phase 2): As data crossed 1 TB, implemented customer ID-based sharding across 10 Azure SQL Databases. Read replicas handled analytics queries (customer segments, product trends).
3. Cosmos DB for cart/sessions (Phase 3): Moved volatile session and shopping cart data to Cosmos DB (Session consistency, $50K$ RU/s autoscale). Reduced SQL contention and improved checkout speed.
4. Azure Synapse for reporting (Phase 4): Nightly ETL loaded transactional data to Synapse SQL Pool. Business intelligence team could run ad-hoc queries without impacting production.

Outcome: Black Friday 2025 handled 100x traffic with 99.99% uptime; cost per transaction decreased 30% due to tiered storage and auto-scaling; time-to-insight for new marketing campaigns dropped from days to hours.

Case Study 2: Healthcare Data Platform

Context: A hospital network (15 facilities, 5 million patients) needed to unify patient records from multiple legacy systems (HL7, FHIR) into a single analytics platform to improve patient outcomes.

Challenge: PHI (Protected Health Information) is highly sensitive (HIPAA compliance). Data volume ($\sim 1$ TB/month) and velocity (real-time patient monitoring) required robust encryption and audit trails. Compliance auditors demanded immutable logs and role-based access.

Solution:

1. Azure Data Lake with immutable storage: Raw HL7 messages ingested into ADLS Gen 2 Bronze zone with blob-level immutability (legal hold, time-based retention). Enabled regulatory audit trails.
2. Synapse for ETL/transformation: De-identified patient records (remove names, SSNs) in Silver zone using Synapse Spark. Applied HIPAA-safe pseudonymization (hash + salt).
3. Cosmos DB for patient analytics: Gold zone loaded curated datasets into Cosmos DB (Gremlin API for patient-provider relationships). Queries for clinical decision support ($< 100$ ms latency critical).
4. Always Encrypted for sensitive columns: Stored procedure in SQL Database used column encryption keys to query encrypted patient medications. Doctors' applications had access to master keys via Azure Key Vault; analysts had read-only access to encrypted data.
5. Azure Purview for data governance: Catalogued all datasets, tracked data lineage (raw → processed → analytical), and enforced PII redaction rules.

Outcome: Time to identify high-risk patients dropped from 48 hours to $< 1$ hour via real-time dashboards; HIPAA audit passed with zero findings; cost per patient record decreased 50% via Cosmos DB autoscale and Archive tier for cold data.

Case Study 3: IoT Sensor Analytics at Scale

Context: A manufacturing company deployed $100,000 IoT sensors across 50 factories, streaming 10 million events/minute to predict machine failures and optimize production.

Challenge: High throughput ($10M$ events/min) and global distribution (US, EU, APAC regions) required sub-millisecond ingestion latency and sub-second analytics. Regulatory requirements (GDPR, localization) mandated data residency by region.

Solution:

1. Azure Event Hubs for ingestion: Per-region Event Hub (Kafka-compatible, 100 partitions). Sensors publish events; Event Hubs auto-scales and buffers 24 hours of data.
2. Azure Databricks for real-time processing: Streaming job reads from Event Hubs, aggregates by machine/factory (minute-level summaries), detects anomalies via isolation forest ML model, writes alerts to Cosmos DB.
3. Multi-region Cosmos DB: Replicated across US, EU, APAC. Write to nearest region ($< 5$ ms latency); alerts propagate globally via multi-master replication. Anomaly data indexed by machine ID for fast retrieval.
4. Synapse for historical analytics: Nightly batch job (Spark) reads $\sim 14$ billion events from Event Hubs archive (7-day retention), aggregates to hourly summaries (Parquet, Gold zone ADLS). Synapse Serverless SQL enables ad-hoc queries (e.g., "Which machine types failed most in Q4?").
5. Power BI for dashboards: Real-time tiles pull from Cosmos DB (last 24 hours); trend charts pull from Synapse (historical 2 years).

Outcome: Predictive maintenance model reduced unplanned downtime by 60% (USD 10M/year savings); global analysts could drill into root causes in $< 1$ minute via Synapse; infrastructure cost: USD 500K/month (Event Hubs USD 50K, Databricks USD 100K, Cosmos DB USD 150K, Synapse USD 150K, storage/networking USD 50K), offset by USD 10M operational savings.