Azure Fundamentals – Databases

Module 1 – Azure SQL Database

Definition

• PaaS (Platform as a Service): Microsoft manages the infrastructure, patches, and backups.
• Fully managed cloud SQL Server.
• Compatible with T-SQL, SSMS, and Azure Data Studio.

Pricing Models

Serverless (Auto-pause)

Azure SQL Database Serverless:
  - Active: billed by CPU seconds used
  - Auto-pause after X minutes of inactivity
  - First access after pause: ~30 seconds "warm-up"
  - Ideal for dev/test or non-critical applications

High Availability and Disaster Recovery

Point-in-Time Restore (PITR)

• Restore the database to any point within the retention window.
• Retention: 7 to 35 days (configurable).
• Useful for recovering from accidental deletion.

Azure Portal → SQL Database → Backups → Restore
  → Restore point: 2024-01-15 14:30:00
  → New database name: mydb-restored

Active Geo-Replication

Primary Database (East US)
  ↓ Continuous asynchronous replication
Secondary 1 (West US) — Readable replica
Secondary 2 (North Europe) — Readable replica
Secondary 3 (Southeast Asia) — Readable replica
Secondary 4 (UK South) — Readable replica

• Up to 4 readable secondary replicas.
• Manual failover possible (or automatic with Failover Groups).
• Useful for distributing reads geographically.

Failover Groups

Failover Group (mygroup.database.windows.net)
  ├── Primary: mydb.eastus.database.windows.net
  └── Secondary: mydb.westus.database.windows.net

Automatic DNS → on failover, the group URL points to the new primary.

• Single endpoint that automatically switches target on failover.
• Apps do not need to update their connection string.

Module 2 – Azure Cosmos DB

Definition

• Fully managed multi-model NoSQL database.
• Globally distributed: data replicated across multiple Azure regions.
• Latency: < 10ms for reads and writes (at the 99th percentile).
• Near-infinite horizontal scalability.

Available APIs

Request Units (RUs)

• Cosmos DB currency: measures the cost of each operation.
• 1 RU = cost of reading a 1 KB document.

Sample costs:
  Read 1 KB: 1 RU
  Write 1 KB: 5 RUs
  Complex query: multiple RUs depending on data scanned

Throughput models:

Global Distribution

Azure Portal → Cosmos DB Account → Replicate data globally
  → Add regions: East US, West Europe, Southeast Asia
  → Automatic failover: ON (auto-failover if primary region goes down)
  → Multi-region writes: ON (write in all regions)

Consistency Levels

Point-in-Time Restore

• Restore to any point within the last 30 days.
• To a new Cosmos DB account.

Comparison

Summary

Advanced Sections – Deep Dive

Section A – Azure SQL Database: Deep Dive

A.1 Purchase Models: DTU vs vCore

The DTU (Database Transaction Unit) model bundles CPU, memory, and I/O into a predefined opaque unit. It simplifies pricing but limits control.

The vCore model separates each dimension (number of cores, memory, storage), offering full transparency and the ability to benefit from Azure Hybrid Benefit (reuse of on-premises SQL Server licenses).

DTU vs vCore comparison:

DTU Basic     → up to 5 DTUs,   2 GB storage
DTU Standard  → up to 3,000 DTUs, 1 TB storage
DTU Premium   → up to 4,000 DTUs, 4 TB storage, In-Memory OLTP

vCore General Purpose   → up to 128 vCores, 624 GB RAM, GP v2 storage
vCore Business Critical → up to 128 vCores, local SSD storage, 4 replicas included
vCore Hyperscale        → up to 128 vCores, storage up to 100 TB, page servers

A.2 vCore Service Tiers

Hyperscale Architecture

flowchart TD
    APP[Application] --> PRI[Primary Compute]
    PRI --> LS[Log Service]
    LS --> PS1[Page Server 1]
    LS --> PS2[Page Server 2]
    LS --> PS3[Page Server 3]
    PRI --> HS_SEC[Secondary Compute\nRead-only replica]
    HS_SEC --> PS1
    HS_SEC --> PS2
    PS1 --> BLOB[Azure Blob Storage\nLong-term]
    PS2 --> BLOB
    PS3 --> BLOB

• Page Servers distribute storage → scalability up to 100 TB without re-architecture.
• Read-only secondary replicas connect directly to Page Servers → zero overhead on the primary.

A.3 Elastic Pools

Elastic Pools allow sharing a resource budget (DTUs or vCores) across multiple databases on the same logical server.

Without Elastic Pool:
  DB1: 100 DTUs (max used: 20 DTUs)
  DB2: 100 DTUs (max used: 30 DTUs)
  DB3: 100 DTUs (max used: 10 DTUs)
  Total billed: 300 DTUs

With Elastic Pool (200 shared DTUs):
  Pool: 200 DTUs
  DB1, DB2, DB3 share these 200 DTUs as needed
  Total billed: 200 DTUs → 33% savings

Ideal for: multi-tenant applications (SaaS) with staggered peak usage per client.

A.4 Serverless Tier

Serverless Configuration (Azure Portal):
  Compute tier    : Serverless
  Min vCores      : 0.5
  Max vCores      : 8
  Auto-pause      : 60 minutes of inactivity
  Billing         : Billed only during execution (seconds)

Lifecycle:
  Active → (inactivity > threshold) → Paused → (incoming connection) → Warm-up (~30s) → Active

Note: The ~30-second cold start can impact latency-sensitive applications.

A.5 Active Geo-Replication and Auto-Failover Groups

Active Geo-Replication

flowchart LR
    subgraph EastUS["East US (Primary)"]
        P["(Primary DB)"]
    end
    subgraph WestUS["West US"]
        S1["(Secondary 1\nReadable)"]
    end
    subgraph NorthEU["North Europe"]
        S2["(Secondary 2\nReadable)"]
    end
    subgraph SEAsia["Southeast Asia"]
        S3["(Secondary 3\nReadable)"]
    end
    P -- "Async replication" --> S1
    P -- "Async replication" --> S2
    P -- "Async replication" --> S3

• Up to 4 readable secondary replicas in different regions.
• Manual failover via PowerShell, CLI, or Azure portal.
• The secondary replica can absorb read requests (read scale-out).

Auto-Failover Groups

-- Create a failover group with Azure CLI
az sql failover-group create \
  --name myFailoverGroup \
  --partner-server mySecondaryServer \
  --resource-group myRG \
  --server myPrimaryServer \
  --failover-policy Automatic \
  --grace-period 1

A.6 Security: Always Encrypted, TDE, Dynamic Data Masking

Transparent Data Encryption (TDE)

Encrypts data and log files at rest. Enabled by default since 2017.

TDE uses a Database Encryption Key (DEK):
  DEK encrypted by → TDE Protector (cert managed by Microsoft OR key in Azure Key Vault)
  
  Bring Your Own Key (BYOK):
    TDE Protector = key in Azure Key Vault (customer controls)
    Revoking the key → database immediately inaccessible

Always Encrypted

Client-side encryption: data is never in plaintext on the server.

-- Example: SSN column encrypted with Always Encrypted
CREATE TABLE Patients (
    PatientID   INT PRIMARY KEY,
    LastName    NVARCHAR(100),
    SSN         NVARCHAR(11)
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = MyCEK,
            ENCRYPTION_TYPE = Deterministic,  -- or Randomized
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        )
);
-- The client driver decrypts with the master key (CMK) in Key Vault.
-- The DBA administrator never sees the SSN in plaintext.

Dynamic Data Masking (DDM)

Masks sensitive data in query results for unprivileged users, without modifying stored data.

-- Partially mask a phone number
ALTER TABLE Customers
ALTER COLUMN Phone NVARCHAR(20)
    MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');

-- An unprivileged user will see: XXX-XXX-1234
-- A DBA or admin will see: 514-555-1234

Microsoft Defender for SQL

Section B – Azure SQL Managed Instance

B.1 Definition and Positioning

Azure SQL Managed Instance (MI) is a fully managed SQL Server instance deployed in an Azure Virtual Network (VNet). It offers near-100% compatibility with on-premises SQL Server, unlike Azure SQL Database which imposes certain limitations.

flowchart LR
    SQL_SERVER["SQL Server\non-premises\n(100% compat)"]
    MI["SQL Managed Instance\n(~100% compat)\nVNet injected"]
    SQLDB["Azure SQL Database\n(~90% compat)\nPublic PaaS"]
    
    SQL_SERVER -- "Lift & Shift\nminimal effort" --> MI
    SQL_SERVER -- "Refactoring\nrequired" --> SQLDB

B.2 Features Exclusive to Managed Instance

B.3 Network Architecture (VNet Injection)

Azure Virtual Network (VNet: 10.0.0.0/16)
  └── MI Subnet (10.0.1.0/24) — delegated to Microsoft.Sql/managedInstances
        └── Managed Instance
              ├── Private endpoint (10.0.1.4) — accessible only from VNet
              └── No public access by default

Connection from on-premises:
  VPN Gateway or ExpressRoute → VNet → Managed Instance

Key point: SQL MI does not have a public endpoint by default (unlike SQL Database). Access requires a private network or VPN.

B.4 Migration from On-Premises with Azure DMS

Azure Database Migration Service (DMS) automates migration from on-premises SQL Server to SQL MI.

flowchart TD
    SOURCE["SQL Server On-Premises\n(2008, 2012, 2014, 2016, 2019)"]
    ASSESS["Azure Migrate / SSMA\nCompatibility Assessment"]
    DMS["Azure Database\nMigration Service"]
    BACKUP["Backups .bak\nto Azure Blob Storage"]
    MI["Azure SQL\nManaged Instance"]
    
    SOURCE --> ASSESS
    ASSESS --> DMS
    SOURCE --> BACKUP
    BACKUP --> DMS
    DMS --> MI

B.5 Service Tiers

Section C – Azure Database for PostgreSQL

C.1 Deployment Options

C.2 Flexible Server

Flexible Server — Characteristics:
  ├── Supported versions: PostgreSQL 11, 12, 13, 14, 15, 16
  ├── HA: Active standby (same zone or different zone)
  ├── Configurable maintenance window
  ├── PITR: 1 to 35 days
  ├── Read replicas: up to 5 in the same region or cross-region
  ├── Built-in PgBouncer (connection pooling)
  └── Extensions: PostGIS, pgcrypto, pg_stat_statements, etc.

High Availability

flowchart TD
    APP[Application] --> LB[Load Balancer / DNS]
    LB --> PRIMARY["Primary\n(Zone 1)"]
    PRIMARY -- "Sync replication" --> STANDBY["Standby\n(Zone 2 or 3)"]
    STANDBY -. "Auto-failover ~60-120s" .-> LB

• Zone-redundant HA: Primary and Standby in different availability zones.
• Same-zone HA: For regions without availability zones.
• Automatic failover: approximately 60–120 seconds.

C.3 Connection Pooling with PgBouncer

PostgreSQL creates one process per connection → significant overhead at scale. PgBouncer acts as a pool proxy.

Without PgBouncer:
  1,000 clients → 1,000 PostgreSQL processes → memory/CPU overload

With PgBouncer (built into Flexible Server):
  1,000 clients → PgBouncer → 100 active PostgreSQL connections
  
  Transaction Pooling mode: connection released after each transaction
  Session Pooling mode    : connection maintained for the entire session

# Connection string with PgBouncer (port 6432 instead of 5432)
host=myserver.postgres.database.azure.com
port=6432
dbname=mydb
user=myadmin
sslmode=require

C.4 Citus – Horizontal Distribution

Citus transforms PostgreSQL into a distributed database via table sharding.

-- Enable Citus
CREATE EXTENSION citus;

-- Distribute a table on a sharding column
SELECT create_distributed_table('orders', 'tenant_id');

-- Create a reference table (replicated on all shards)
SELECT create_reference_table('products');

C.5 Point-in-Time Restore

PITR PostgreSQL Flexible Server:
  Retention      : 1 to 35 days
  Granularity    : down to the second
  Target         : new server (no in-place restore)
  
  Azure CLI:
  az postgres flexible-server restore \
    --resource-group myRG \
    --name myRestoredServer \
    --source-server myOriginalServer \
    --restore-time "2024-03-15T14:30:00Z"

C.6 Read Replicas

Primary Server (East US)
  ├── Replica 1 (East US)     — local reads
  ├── Replica 2 (West Europe) — reads from EU
  └── Replica 3 (Southeast Asia) — reads from Asia

Replication: asynchronous, based on WAL (Write-Ahead Logs)
Promotion  : manual (the replica becomes an independent server)

Section D – Azure Database for MySQL

D.1 Flexible Server

Since 2022, MySQL Flexible Server is the only recommended model (Single Server deprecated).

Flexible Server — Characteristics:
  ├── Versions: MySQL 5.7, 8.0
  ├── Compute : Burstable (B-series), General Purpose, Memory Optimized
  ├── Storage : 20 GB to 16 TB, auto-grow possible
  ├── PITR    : 1 to 35 days
  └── Access  : Public (with firewall) or Private (VNet)

D.2 High Availability

flowchart TD
    APP[Application] --> EP[DNS Endpoint]
    EP --> P["Primary Server\n(Zone 1)"]
    P -- "Sync replication\n(before ACK to client)" --> S["Standby Server\n(Zone 2)"]
    S -. "Auto-failover\n~60-120s" .-> EP

D.3 Replication

-- Check replication status (on the replica)
SHOW REPLICA STATUS\G

-- Key parameters:
-- Seconds_Behind_Source: replication lag in seconds
-- Replica_IO_Running: YES if replication is active
-- Replica_SQL_Running: YES if change application is active

D.4 Backup and Restore

Automatic backups:
  Type          : Full (weekly) + Differential (daily) + Log (every 5 min)
  Retention     : 1 to 35 days
  Storage       : LRS, ZRS, or GRS (geo-redundant)
  PITR          : Restore to the second within the retention window
  
  Long-term retention: Not natively integrated (use mysqldump + Azure Blob)

Section E – Azure Cosmos DB: Deep Dive

E.1 Multi-Model Architecture

Cosmos DB is a single platform exposing multiple interchangeable APIs on the same ARS (Atom-Record-Sequence) storage engine.

flowchart TD
    ARS["ARS Engine\n(Atom-Record-Sequence)"]
    ARS --> NOSQL["NoSQL API\n(Native JSON Documents)"]
    ARS --> MONGO["MongoDB API\n(Wire Protocol 4.0+)"]
    ARS --> CASS["Cassandra API\n(CQL)"]
    ARS --> GREM["Gremlin API\n(Graph)"]
    ARS --> TABLE["Table API\n(OData)"]
    ARS --> PSQL["PostgreSQL API\n(Distributed Citus)"]

E.2 The 5 Consistency Levels

flowchart LR
    S["Strong\n(strongest)"] --> BS["Bounded\nStaleness"] --> SES["Session\n(default)"] --> CP["Consistent\nPrefix"] --> EV["Eventual\n(weakest)"]

AZ-900 tip: Session is the default level and suits 80% of applications.

E.3 Request Units (RUs) Model

Approximate cost formula:

  Read  1 KB document  = 1 RU
  Write 1 KB document  = 5 RUs
  Upsert 1 KB document = 6 RUs
  Delete               = 3 RUs
  Query (full scan)    = variable depending on documents scanned

Sizing example:
  10,000 reads/sec × 1 RU  =  10,000 RU/s
   2,000 writes/sec × 5 RU =  10,000 RU/s
  ─────────────────────────────────────────
  Total required            =  20,000 RU/s

E.4 Partitioning

Partitioning is at the core of Cosmos DB scalability.

// Container with partition key on /categoryId
{
  "id": "item-001",
  "categoryId": "electronics",      // ← partition key
  "name": "Laptop Pro 15",
  "price": 1299.99,
  "stock": 42
}

Logical partition : all documents with the same partition key value
Physical partition: grouping of logical partitions (max 50 GB, ~10,000 RU/s)

Good partition key:
  ✓ High cardinality (many distinct values)
  ✓ Uniform distribution of reads/writes
  ✓ Frequently used in WHERE queries

Bad partition key:
  ✗ /gender (only 2-3 values → hot partition)
  ✗ /status (few values)

E.5 Change Feed

The Change Feed is an ordered stream of all modifications (inserts and updates) on a container.

flowchart LR
    APP[Application] -- Inserts/Updates --> CONTAINER["(Cosmos DB\nContainer)"]
    CONTAINER -- "Change Feed\n(ordered by partition)" --> FUNC["Azure Function\n(Trigger)"]
    FUNC --> SEARCH["Azure Cognitive Search\n(indexing)"]
    FUNC --> CACHE["Azure Cache for Redis\n(invalidation)"]
    FUNC --> HUB["Azure Event Hub\n(analytics)"]

// Azure Function - Cosmos DB Change Feed Trigger
[FunctionName("ProcessChangeFeed")]
public static async Task Run(
    [CosmosDBTrigger(
        databaseName: "mydb",
        containerName: "orders",
        Connection = "CosmosDBConnection",
        LeaseContainerName = "leases",
        CreateLeaseContainerIfNotExists = true)]
    IReadOnlyList<Order> changedItems,
    ILogger log)
{
    foreach (var item in changedItems)
    {
        log.LogInformation($"Order changed: {item.Id}");
        // Invalidate cache, update index, etc.
    }
}

E.6 Time To Live (TTL)

// Configure TTL on the container (in seconds)
{
  "defaultTtl": 86400  // 24 hours by default
}

// Override per document
{
  "id": "session-xyz",
  "userId": "user-123",
  "ttl": 3600           // expires in 1 hour
}
// ttl = -1 : never expire (even if defaultTtl is set)
// ttl = 0  : delete immediately

TTL use cases: user sessions, temporary tokens, audit logs, cache data.

E.7 Global Distribution and Multi-Write

Multi-region configuration with multi-write:

  Regions:
    ├── East US   (write + read)
    ├── West EU   (write + read)
    └── SEA       (write + read)
  
  Conflict type "Last Write Wins" (LWW):
    Based on _ts (timestamp) — the last write wins
    
  Custom Conflict Resolution:
    Use an Azure Function or stored procedure to resolve conflicts

Section F – Azure Cache for Redis

F.1 Definition

Azure Cache for Redis is a managed in-memory cache service based on open-source Redis. It dramatically reduces application latency by storing frequently accessed data in RAM.

flowchart LR
    APP[Application] --> CACHE["Azure Cache\nfor Redis"]
    CACHE -- "Cache HIT\n< 1ms" --> APP
    CACHE -- "Cache MISS" --> DB["(Database)"]
    DB --> CACHE
    DB --> APP

F.2 Caching Patterns

Cache-Aside (Lazy Loading) — most common

// Cache-Aside pattern
public async Task<Product> GetProductAsync(string productId)
{
    var cacheKey = $"product:{productId}";
    
    // 1. Look in cache
    var cached = await _redis.StringGetAsync(cacheKey);
    if (cached.HasValue)
        return JsonSerializer.Deserialize<Product>(cached);
    
    // 2. Cache MISS → look in database
    var product = await _db.Products.FindAsync(productId);
    
    // 3. Store in cache (TTL 10 minutes)
    await _redis.StringSetAsync(
        cacheKey,
        JsonSerializer.Serialize(product),
        TimeSpan.FromMinutes(10));
    
    return product;
}

Read-Through and Write-Through

F.3 Eviction Policies

When the cache is full, Redis must decide what to remove:

F.4 Azure Cache for Redis Tiers

F.5 Redis Clustering

Redis Cluster (Premium tier):
  ├── Shard 0: slots 0–5460     (primary node + replica)
  ├── Shard 1: slots 5461–10922 (primary node + replica)
  └── Shard 2: slots 10923–16383 (primary node + replica)

  Total: 16,384 hash slots distributed across N shards
  Scaling: add shards to increase throughput and memory

F.6 Essential Redis Commands

# Basic operations
SET user:1001 '{"name":"Alice","email":"alice@example.com"}' EX 3600
GET user:1001
DEL user:1001
EXISTS user:1001
TTL user:1001           # Remaining time before expiration

# Data structures
HSET product:500 name "Laptop" price 999 stock 42
HGET product:500 price
HGETALL product:500

LPUSH notifications:user1 "Order confirmed"
LRANGE notifications:user1 0 -1   # All elements

SADD tags:article1 "azure" "cloud" "database"
SMEMBERS tags:article1

# Sorted Sets (leaderboard)
ZADD leaderboard 5000 "Alice" 4200 "Bob" 3800 "Charlie"
ZREVRANGE leaderboard 0 2 WITHSCORES  # Top 3

Section G – Azure Synapse Analytics

G.1 Overview

Azure Synapse Analytics is a unified analytical workspace combining:

• Data Warehousing
• Big Data (Apache Spark)
• Data Integration (ETL/ELT pipelines)
• Visualization (Power BI integration)

flowchart TD
    subgraph SOURCES["Data Sources"]
        SQLDB["(Azure SQL)"]
        BLOB["(Azure Data Lake\nGen2)"]
        COSMOS["(Cosmos DB)"]
        ON_PREM["(On-premises)"]
    end
    subgraph SYNAPSE["Azure Synapse Analytics Workspace"]
        PIPE["Synapse Pipelines\n(ETL/ELT)"]
        SPARK["Apache Spark Pools\n(Big Data / ML)"]
        DED["Dedicated SQL Pool\n(ex-SQL DW)"]
        SLESS["Serverless SQL Pool\n(ad-hoc queries)"]
        LINK["Synapse Link\n(HTAP)"]
    end
    PBI["Power BI\n(Visualization)"]
    
    SOURCES --> PIPE
    COSMOS -- "Synapse Link\n(zero ETL)" --> LINK
    PIPE --> SPARK
    PIPE --> DED
    LINK --> SPARK
    LINK --> SLESS
    DED --> PBI
    SLESS --> PBI
    SPARK --> PBI

G.2 Dedicated SQL Pool (ex-Azure SQL Data Warehouse)

Massively Parallel Processing (MPP) Architecture:
  ├── Control Node   : receives queries, plans
  ├── Compute Node 1 : processes 1/N of the data
  ├── Compute Node 2 : processes 1/N of the data
  └── Compute Node N : processes 1/N of the data
  
  Storage : Azure Data Lake Gen2 (decoupled from compute)
  Scale   : 100 DWU to 30,000 DWU (Data Warehouse Units)
  Pause/Resume: stop compute without losing data

-- Distributed tables (distribution key essential for performance)
CREATE TABLE FactSales (
    SaleID      INT           NOT NULL,
    CustomerID  INT           NOT NULL,
    ProductID   INT           NOT NULL,
    Amount      DECIMAL(10,2) NOT NULL,
    SaleDate    DATE          NOT NULL
)
WITH (
    DISTRIBUTION = HASH(CustomerID),  -- distribute across compute nodes
    CLUSTERED COLUMNSTORE INDEX       -- columnar compression for analytics
);

-- Dimension table replicated on all nodes
CREATE TABLE DimProduct (
    ProductID   INT PRIMARY KEY,
    ProductName NVARCHAR(200),
    Category    NVARCHAR(100)
)
WITH (
    DISTRIBUTION = REPLICATE          -- copy on each compute node
);

G.3 Serverless SQL Pool

No infrastructure to provision — SQL queries directly on files in Azure Data Lake Gen2.

-- Query Parquet files without ETL
SELECT
    year,
    country,
    SUM(revenue) AS total_revenue
FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/sales/year=*/country=*/*.parquet',
    FORMAT = 'PARQUET'
) AS rows
GROUP BY year, country
ORDER BY year, total_revenue DESC;

G.4 Apache Spark Pools

# PySpark example in Synapse — data transformation
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum as spark_sum

spark = SparkSession.builder.getOrCreate()

# Read from Data Lake (Delta format)
df_sales = spark.read.format("delta").load(
    "abfss://container@mydatalake.dfs.core.windows.net/sales/"
)

# Aggregation by month
df_monthly = (
    df_sales
    .groupBy("year", "month", "region")
    .agg(spark_sum("amount").alias("total_amount"))
    .orderBy("year", "month")
)

# Write to Dedicated SQL Pool
df_monthly.write \
    .format("com.microsoft.sqlserver.jdbc.spark") \
    .mode("overwrite") \
    .option("url", "jdbc:sqlserver://mysynapse.sql.azuresynapse.net") \
    .option("dbtable", "dbo.MonthlySalesSummary") \
    .save()

G.5 Synapse Link (HTAP)

Synapse Link enables analyzing operational data from Cosmos DB or Azure SQL in real time, without impacting transactional workloads (OLTP), without ETL.

HTAP: Hybrid Transactional/Analytical Processing

  Cosmos DB (OLTP, row store)
       │
       └── Synapse Link → Analytical Store (column store, automatic)
                                │
                                └── Synapse Spark / Serverless SQL
                                    (analysis without impact on OLTP)

Advantage: zero ETL, latency < a few minutes between OLTP and OLAP

Section H – Migrating Data to Azure

H.1 Azure Database Migration Service (DMS)

Azure DMS is a managed service that orchestrates database migrations to Azure.

flowchart TD
    ASSESS["Assessment\n(Azure Migrate / DMA)"]
    SCHEMA["Schema migration\n(SSMA or native tools)"]
    DATA["Data migration\n(Azure DMS)"]
    CUTOVER["Cutover\n(application switchover)"]
    POST["Post-migration\n(validation, optimization)"]
    
    ASSESS --> SCHEMA
    SCHEMA --> DATA
    DATA --> CUTOVER
    CUTOVER --> POST

H.2 SQL Server Migration Assistant (SSMA)

SSMA automates schema conversion and T-SQL code conversion during cross-platform migrations.

SSMA for Oracle:
  1. Connect to Oracle source
  2. Analyze objects (tables, views, procedures, triggers)
  3. Convert Oracle schema → T-SQL
  4. Conversion error report
  5. Apply converted schema on Azure SQL
  6. Migrate data

Automatically converted objects:
  ✓ Tables, indexes, constraints
  ✓ Views
  ✓ Stored procedures (partially)
  ✗ Complex Oracle packages → manual conversion

SSMA available for: Oracle, MySQL, PostgreSQL, Sybase, Access, DB2.

H.3 Offline vs Online Migration

flowchart TD
    subgraph OFFLINE["Offline Migration"]
        O1["Stop application"] --> O2["Full backup"]
        O2 --> O3["Restore on Azure"] --> O4["Validation"] --> O5["Restart on Azure"]
    end
    subgraph ONLINE["Online Migration (minimum downtime)"]
        N1["Full backup + restore\n(app continues on source)"] --> N2["Continuous log\nsynchronization"]
        N2 --> N3["Cutover at chosen time\n(a few minutes of downtime)"]
    end

H.4 Azure Migrate

Azure Migrate is the central hub for assessing and migrating workloads to Azure (servers, databases, web applications).

Azure Migrate Hub:
  ├── Discovery and Assessment  → Inventory servers, estimate Azure costs
  ├── Azure Migrate: Database   → SQL Server compatibility assessment
  ├── Azure Migrate: Web Apps   → ASP.NET / Java assessment
  └── Azure Migrate: Migration  → Effective migration (replication, failover)

DMA Report (Database Migration Assessment):
  ✓ Features supported in Azure SQL
  ✗ Unsupported features (blockers list)
  ! Impacted features (warnings)
  Target tier / SKU recommendation

Section I – Choosing the Right Database Service

I.1 Decision Tree

flowchart TD
    START([New data storage\nrequirement]) --> Q1{Relational\ndata?}
    
    Q1 -- Yes --> Q2{SQL Server\ncompatibility required?}
    Q1 -- No --> Q3{Data type?}
    
    Q2 -- "Yes (VNet, SQL Agent,\nLinked Servers)" --> MI["Azure SQL\nManaged Instance"]
    Q2 -- No --> Q4{Scale?}
    
    Q4 -- Standard --> SQLDB["Azure SQL Database\n(General Purpose)"]
    Q4 -- "Very large (100 TB+)" --> HYPER["Azure SQL Database\nHyperscale"]
    Q4 -- "Analytical (DW)" --> SYNAPSE["Azure Synapse\nDedicated SQL Pool"]
    
    Q3 -- Documents/JSON --> Q5{Global\ndistribution required?}
    Q3 -- "Graph" --> GREMLIN["Cosmos DB\nGremlin API"]
    Q3 -- "Fast key-value" --> REDIS["Azure Cache\nfor Redis"]
    Q3 -- "Wide columns" --> CASSAPI["Cosmos DB\nCassandra API"]
    
    Q5 -- Yes --> COSMOS["Azure Cosmos DB\nNoSQL / MongoDB API"]
    Q5 -- No --> Q6{PostgreSQL or MySQL?}
    
    Q6 -- PostgreSQL --> PSQL["Azure Database\nfor PostgreSQL\nFlexible Server"]
    Q6 -- MySQL --> MYSQL["Azure Database\nfor MySQL\nFlexible Server"]
    Q6 -- "Neither" --> COSMOS
    
    MI --> RESULT([Service chosen])
    SQLDB --> RESULT
    HYPER --> RESULT
    SYNAPSE --> RESULT
    COSMOS --> RESULT
    GREMLIN --> RESULT
    REDIS --> RESULT
    CASSAPI --> RESULT
    PSQL --> RESULT
    MYSQL --> RESULT

I.2 Relational vs NoSQL Comparison

I.3 OLTP vs OLAP

flowchart LR
    subgraph OLTP["OLTP (Transactional)"]
        T1["Many small transactions\n(INSERT, UPDATE, DELETE)"]
        T2["High concurrency\n(hundreds of simultaneous users)"]
        T3["Latency < 10ms"]
        T4["Normalized data"]
    end
    subgraph OLAP["OLAP (Analytical)"]
        A1["Few queries\nbut very complex"]
        A2["Massive volumes\n(billions of rows)"]
        A3["Aggregations, wide JOINs"]
        A4["Denormalized data\n(star schema)"]
    end
    
    OLTP -- "ETL / ELT\nSynapse Pipelines" --> OLAP

I.4 Summary Table of All Services

Section J – Review Questions

12 Multiple Choice Questions

Question 1 You are developing a multi-tenant SaaS application with hundreds of moderately sized databases whose peak usage is staggered. Which Azure service minimizes costs by sharing resources between these databases?

• A) Azure Cosmos DB with autoscale
• B) Azure SQL Database with Elastic Pool
• C) Azure SQL Managed Instance
• D) Azure Synapse Analytics Serverless SQL Pool

Answer: B — Elastic Pools are designed exactly for this scenario: databases share a budget of DTUs or vCores, which optimizes costs when peaks are staggered over time.

Question 2 A company is migrating an on-premises SQL Server that uses SQL Server Agent, Linked Servers, and cross-database queries. Which Azure target service is most appropriate with the least refactoring?

• A) Azure SQL Database General Purpose
• B) Azure SQL Database Hyperscale
• C) Azure SQL Managed Instance
• D) Azure Database for PostgreSQL Flexible Server

Answer: C — SQL Managed Instance supports SQL Agent, Linked Servers, CLR, and cross-database queries. Azure SQL Database does not support these features.

Question 3 Which Azure Cosmos DB feature guarantees that, within a user session, a read will always return at least the data that user just wrote?

• A) Consistent Prefix
• B) Bounded Staleness
• C) Session
• D) Eventual

Answer: C — The Session consistency level guarantees read-your-own-writes within a session context. This is the default level for Cosmos DB.

Question 4 You want to encrypt columns containing sensitive data (social security numbers) so that even DBA administrators cannot read them in plaintext. Which Azure SQL feature do you use?

• A) Transparent Data Encryption (TDE)
• B) Dynamic Data Masking
• C) Always Encrypted
• D) Azure Defender for SQL

Answer: C — Always Encrypted performs client-side encryption. Data never transits or is stored in plaintext on the SQL server. TDE encrypts only files at rest, and DDM only masks the display of results.

Question 5 What is the main difference between Active Geo-Replication and Auto-Failover Groups in Azure SQL Database?

• A) Active Geo-Replication supports 10 secondaries, Failover Groups support 4
• B) Failover Groups provide a single DNS endpoint that automatically switches, Active Geo-Replication requires manually updating the connection string
• C) Active Geo-Replication is only available in Premium DTU mode
• D) Failover Groups do not allow reading from secondaries

Answer: B — Failover Groups offer a single (DNS) endpoint that automatically points to the new primary after failover. With Active Geo-Replication alone, the application would need to update its connection string manually.

Question 6 A cloud architect needs to store IoT sensor data from 50 countries, with latency requirements below 10ms for reads and writes from any region. Which service do you recommend?

• A) Azure SQL Database with Active Geo-Replication
• B) Azure Database for PostgreSQL Flexible Server
• C) Azure Cosmos DB with global distribution and multi-region writes
• D) Azure Synapse Analytics Dedicated SQL Pool

Answer: C — Cosmos DB is designed for global distribution with multi-region writes and a latency SLA of < 10ms. Other options do not guarantee this latency globally.

Question 7 What is the primary function of Azure Cache for Redis in an application architecture?

• A) Store structured relational data like a SQL database
• B) Serve as an in-memory cache layer to reduce latency and load on the main database
• C) Replace Cosmos DB for NoSQL data
• D) Ensure geographic replication of SQL data

Answer: B — Redis is an in-memory cache. Its primary role is to reduce access to the main database by storing frequently consulted results in RAM, with latencies < 1ms.

Question 8 In Azure Synapse Analytics, which option allows querying Parquet files in Azure Data Lake Gen2 without provisioning infrastructure and paying only per TB of data scanned?

• A) Dedicated SQL Pool
• B) Apache Spark Pool
• C) Serverless SQL Pool
• D) Synapse Link

Answer: C — The Serverless SQL Pool allows executing T-SQL queries directly on files (Parquet, CSV, JSON, Delta) in the Data Lake with no provisioning, billed per TB scanned.

Question 9 You are using the Cache-Aside pattern with Azure Cache for Redis. What happens when a cache MISS occurs?

• A) Redis returns a 404 error to the application
• B) The application queries the database directly, then stores the result in Redis
• C) Redis automatically queries the database and updates the cache
• D) The request is rejected until the cache is populated

Answer: B — In the Cache-Aside pattern, it is the application that manages the cache. On a MISS, the application fetches from the database, then stores the result in Redis for subsequent requests.

Question 10 Which Cosmos DB feature provides a real-time stream of all inserts and modifications made to a container, ideal for triggering event-driven processing?

• A) TTL (Time To Live)
• B) Synapse Link
• C) Change Feed
• D) Request Units

Answer: C — The Change Feed is an ordered stream of modifications (inserts and updates) to a Cosmos DB container. It can trigger Azure Functions, feed Event Hubs, or update search indexes.

Question 11 A startup is developing a mobile game application with a global leaderboard requiring real-time score updates and very fast reads. Which service is best suited to store this leaderboard?

• A) Azure SQL Database General Purpose
• B) Azure Cache for Redis (Sorted Sets)
• C) Azure Synapse Dedicated SQL Pool
• D) Azure Database for MySQL Flexible Server

Answer: B — Redis Sorted Sets are the ideal structure for a leaderboard: each member has a score, the order is maintained automatically, and ranking operations (ZADD, ZREVRANGE) are O(log N).

Question 12 During a migration of on-premises SQL Server to Azure SQL Managed Instance, which strategy minimizes application downtime?

• A) Offline migration with full backup and restore during a maintenance window
• B) Online migration with Azure DMS, using continuous log synchronization and a planned cutover
• C) CSV export then import into Azure SQL MI
• D) Manually replicate table by table with INSERT INTO SELECT scripts

Answer: B — Online migration with Azure DMS continuously synchronizes log transactions between source and target. The application remains available throughout the migration and downtime is limited to the few minutes of the final cutover.

Final Summary – All Services

mindmap
  root((Azure\nDatabase\nServices))
    Relational
      Azure SQL Database
        DTU / vCore / Serverless
        General Purpose / Business Critical / Hyperscale
        Elastic Pools
        Active Geo-Replication
        Auto-Failover Groups
        TDE / Always Encrypted / DDM
      SQL Managed Instance
        100% SQL Server compat
        VNet Injection
        SQL Agent / Linked Servers
        DMS Migration
      PostgreSQL Flexible Server
        PgBouncer
        Citus Hyperscale
        Read Replicas
      MySQL Flexible Server
        Zone-Redundant HA
        Read Replicas
    NoSQL
      Cosmos DB
        Multi-model APIs
        5 consistency levels
        Change Feed
        TTL
        Global distribution
    Cache
      Azure Cache for Redis
        Cache-Aside
        Eviction policies
        Clustering
        Geo-replication
    Analytics
      Azure Synapse Analytics
        Dedicated SQL Pool
        Serverless SQL Pool
        Spark Pools
        Synapse Link
    Migration
      Azure DMS
      SSMA
      Azure Migrate

Search Terms

azure · fundamentals · databases · core · infrastructure · microsoft · sql · database · data · definition · migration · redis · restore · service · active · architecture · availability · distribution · geo-replication · groups · high · point-in-time · server · serverless