# PART 4: DATABASE SERVICES

---

## 4.1 Azure SQL Database

### Overview
Fully managed SQL Server database. PaaS. Auto-backup, patching, HA built in.

### Deployment Options
| Option | Description | Use Case |
|---|---|---|
| **Single Database** | One database with dedicated resources | Single app database |
| **Elastic Pool** | Shared resources across multiple databases | Multi-tenant SaaS, variable workloads |
| **Managed Instance** | Near-100% SQL Server compatibility | Lift-and-shift migration |

### Purchasing Models
- **DTU Model** — Simple, bundled compute+storage. Basic, Standard, Premium tiers.
- **vCore Model** — Choose compute (Gen5, M-series, DC-series) + storage separately. General Purpose, Business Critical, Hyperscale.

### Service Tiers (vCore)
| Tier | HA | Storage | Use Case |
|---|---|---|---|
| **General Purpose** | Readable replica | Up to 4 TB | Most business workloads |
| **Business Critical** | 3 replicas + readable secondaries | Up to 4 TB | Low latency, high IOPS |
| **Hyperscale** | Up to 4 readable replicas | Up to 100 TB | Very large databases |

### Key Features
- **Automatic Backups** — 7-35 days retention. PITR (Point-In-Time Restore).
- **Long-Term Retention (LTR)** — Up to 10 years for compliance.
- **Geo-Replication** — Active geo-replication (async, readable secondaries in other regions).
- **Auto-Failover Groups** — Automatic failover to secondary region. Decouples app from DB endpoint.
- **Read Scale-Out** — Route read-only queries to readable replica (Business Critical, Hyperscale).
- **Transparent Data Encryption (TDE)** — Encrypt data at rest. Enabled by default.
- **Always Encrypted** — Encrypt columns client-side. DB never sees plaintext.
- **Dynamic Data Masking** — Mask sensitive data in query results (e.g., SSN: XXX-XX-1234).
- **Row-Level Security (RLS)** — Filter rows based on user context.
- **Auditing** — Track database events to Storage Account / Log Analytics.
- **Microsoft Defender for SQL** — Vulnerability assessment, threat detection.
- **Ledger** — Immutable, cryptographically verified records (blockchain-like).

### Interview Q&A
**Q: Single Database vs Elastic Pool vs Managed Instance?**
A: Single DB = isolated, predictable workloads. Elastic Pool = shared eDTU/vCore across databases with variable peaks (cost-effective). Managed Instance = near-full SQL Server compatibility for migration, instance-scoped features (SQL Agent, cross-DB queries).

**Q: Active Geo-Replication vs Failover Groups?**
A: Active Geo-Replication = up to 4 readable secondaries, manual failover. Failover Groups = automatic failover, group of databases, transparent connection string (listener endpoint). Use Failover Groups for production DR.

---

## 4.2 Azure SQL Managed Instance

### Overview
Near-100% SQL Server compatibility. Lift-and-shift with minimal changes.

### Key Differences from Azure SQL Database
- Instance-scoped features: SQL Agent, cross-database queries, Service Broker
- Native VNet integration (no public endpoint needed)
- SQL Server Agent for jobs
- Database Mail
- Common language runtime (CLR)
- More like on-prem SQL Server in the cloud

### Instance Pools
- Pack multiple managed instances into shared compute
- Cost-effective for small instances

---

## 4.3 Azure Database for PostgreSQL

### Overview
Managed PostgreSQL. Two deployment options:

| Option | Description |
|---|---|
| **Flexible Server** | Recommended. Custom maintenance window, burstable compute, stop/start, zone-redundant HA |
| **Single Server** | Legacy (being retired). Simpler but less flexible |

### Key Features (Flexible Server)
- Burstable, General Purpose, Memory Optimized tiers
- Zone-redundant HA (automatic failover)
- Read replicas (async)
- Logical replication
- PgBouncer built-in (connection pooling)
- Stop/start to save costs
- VNet integration (private access)

---

## 4.4 Azure Database for MySQL

### Overview
Managed MySQL. Flexible Server (recommended) is the current deployment option.

- Similar features to PostgreSQL Flexible Server
- Burstable, General Purpose, Memory Optimized
- Zone-redundant HA, read replicas, stop/start
- VNet integration

---

## 4.5 Azure Cosmos DB

### Overview
Globally distributed, multi-model NoSQL database. 99.999% SLA. Turnkey global distribution.

### APIs (Choose Based on Your Workload)
| API | Compatible With | Best For |
|---|---|---|
| **NoSQL (SQL)** | Native Cosmos DB API | New applications, document store |
| **MongoDB** | MongoDB wire protocol | Migrating from MongoDB |
| **Cassandra** | Cassandra wire protocol | Migrating from Cassandra |
| **Gremlin** | Apache TinkerPop/Gremlin | Graph database (social networks, relationships) |
| **Table** | Azure Table Storage API | Simple key-value, migrating from Table Storage |

### Consistency Levels (5 levels — unique to Cosmos DB)
| Level | Behavior | Use Case |
|---|---|---|
| **Strong** | Linearizable. Read always returns latest write. | Financial transactions, critical data |
| **Bounded Staleness** | Reads lag behind writes by K versions or T time | Gaming, ticketing |
| **Session** | Consistent within a session. Read-your-own-writes. | Default. Most applications. |
| **Consistent Prefix** | Reads see writes in order, but may be behind | Scenarios where order matters |
| **Eventual** | No ordering guarantee. Eventually consistent. | Lowest latency, highest availability |

### Key Features
- **Global Distribution** — Replicate to any Azure region with one click. Multi-region writes.
- **Partition Key** — Critical design choice. Determines data distribution and scalability. Choose high-cardinality, evenly distributed key.
- **Request Units (RU/s)** — Cost metric. 1 RU ≈ 1 read of 1 KB item. Provisioned (manual/autoscale) or serverless.
- **Change Feed** — Event stream of changes (inserts, updates). For real-time processing, ETL, triggers.
- **Stored Procedures** — JavaScript-based, run server-side within a partition.
- **TTL (Time-to-Live)** — Auto-expire documents after N seconds.
- **Serverless** — Pay per request. For intermittent workloads.
- **Free Tier** — 400 RU/s + 5 GB free for lifetime.

### Interview Q&A
**Q: How do you choose a partition key?**
A: High cardinality, even distribution, aligned with query patterns. Bad partition key = hot partition (uneven RU consumption). The partition key determines which logical partition data goes to. You cannot change partition key after creation.

**Q: Explain Cosmos DB consistency levels?**
A: 5 levels from Strong (most consistent, highest latency) to Eventual (least consistent, lowest latency). Most apps use Session (default) — read-your-own-writes within a session. Consistency is per-request — you can use different levels for different operations.

**Q: What are RUs?**
A: Request Units — unified cost metric. 1 RU = 1 read of 1 KB item by ID. Writes cost more RUs. Provisioned throughput (manual or autoscale) or serverless (pay per request).

---

## 4.6 Azure Cache for Redis

### Overview
Managed Redis cache. In-memory data store for fast access.

### Tiers
| Tier | Features | Use Case |
|---|---|---|
| **Basic** | Single node, no SLA | Dev/test |
| **Standard** | Primary + replica, 99.9% SLA | Production |
| **Premium** | Cluster, VNet, persistence, zones | Enterprise |
| **Enterprise** | Redis Enterprise, RediSearch, RedisJSON | Advanced features |
| **Enterprise Flash** | Cost-optimized large cache | Large cache, cost-sensitive |

### Key Features
- **Persistence** — RDB or AOF snapshots to Storage Account (Premium+)
- **Clustering** — Shard data across multiple nodes (Premium+)
- **VNet Integration** — Private endpoint support (Premium+)
- **Geo-Replication** — Replicate across regions (Premium+)
- **Zone Redundancy** — Spread across availability zones

### Common Use Cases
- Caching (session state, database query results)
- Message broker (pub/sub)
- Leaderboards, rate limiting
- Real-time analytics

---

## 4.7 Azure Synapse Analytics

### Overview
Unified analytics platform. Data warehouse + data lake + data integration. Formerly SQL Data Warehouse.

### Key Components
- **Synapse SQL** — Dedicated SQL pool (former SQL DW, MPP) + Serverless SQL pool (pay per query)
- **Synapse Spark** — Managed Spark for data engineering/science
- **Synapse Pipelines** — ETL/ELT orchestration (like Data Factory)
- **Synapse Studio** — Unified web UI

### Dedicated SQL Pool (Formerly SQL DW)
- Massively Parallel Processing (MPP)
- Compute + Storage separated. Pause compute when not in use.
- DWU (Data Warehouse Units) for compute sizing
- Table types: Heap, Clustered Index, Clustered Columnstore (default, best for analytics)

### Serverless SQL Pool
- Query data in ADLS Gen2 or Blob Storage directly
- Pay per TB processed
- No infrastructure to manage

### Interview Q&A
**Q: Dedicated SQL Pool vs Serverless SQL Pool?**
A: Dedicated = provisioned compute, consistent performance, for heavy/repeated workloads. Serverless = pay per query, ad-hoc queries over data lake, no compute to manage.

---

## 4.8 Azure Databricks

### Overview
Apache Spark-based analytics platform. Collaborative notebooks. Delta Lake built in.

### Key Features
- **Workspace** — Collaborative notebooks (Python, Scala, SQL, R)
- **Clusters** — Auto-scaling Spark clusters. Job clusters (ephemeral) vs All-purpose clusters.
- **Delta Lake** — ACID transactions on data lake. Time travel, schema enforcement, MERGE operations.
- **MLflow** — Track ML experiments, models
- **Photon Engine** — C++ acceleration for Spark SQL (faster, cheaper)
- **Unity Catalog** — Unified governance across workspaces

### Interview Q&A
**Q: What is Delta Lake?**
A: Open-source storage layer on data lake. Adds ACID transactions, time travel, schema enforcement, MERGE (upsert) to Parquet/data lake. Solves data reliability issues. Default format in Databricks.

---

## 4.9 Azure Data Explorer (Kusto)

### Overview
Fast, interactive analytics for log and telemetry data. Optimized for high-volume, time-series data.

- Kusto Query Language (KQL)
- Ingest from Event Hubs, IoT Hub, Blob Storage
- Used by Azure Monitor Log Analytics (same engine)
- For logs, metrics, IoT telemetry, application traces
