Databases & Data Services

Table of Contents#

1. Structured Data on Azure — the Menu
2. Azure SQL Database — Deployment Options
3. Azure SQL Purchasing Models — DTU, vCore, and Serverless
4. Azure SQL Service Tiers — General Purpose, Business Critical, Hyperscale
5. Azure SQL High Availability and Failover Groups
6. Azure SQL Security — TDE, Always Encrypted, and Auditing
7. Elastic Pools and Query Performance Insight
8. Cosmos DB — Global Distribution Architecture
9. Cosmos DB APIs
10. Cosmos DB Consistency Levels
11. Cosmos DB Partitioning
12. Cosmos DB Request Units and Autoscale
13. Cosmos DB Change Feed
14. Azure Database for PostgreSQL Flexible Server
15. Azure Database for MySQL Flexible Server
16. Choosing Between Azure SQL, Cosmos DB, PostgreSQL, and MySQL
17. Managed Instance vs. Self-Managed Database on a VM
18. Backup and Point-in-Time Restore
19. Read Replicas and Scaling Reads
20. Azure Database Migration Service
21. Azure Cache for Redis
22. A Full Worked Database Bootstrap for Meridian Freight
23. Part 9 CLI Cheat Sheet
24. Common Mistakes and Interview Traps
25. Worked Practice Problems
26. Summary and What's Next

Structured Data on Azure — the Menu#

rates-db (relational, transactional pricing data) uses Azure SQL; a future global-scale shipment-tracking event store would be a natural Cosmos DB fit; and docs-processor's metadata store uses PostgreSQL Flexible Server — this chapter builds the decision criteria for each.

Azure SQL Database — Deployment Options#

az sql db create --name rates-db --server sql-meridian-prod --resource-group rg-shipment-api-prod \
  --service-objective GP_Gen5_2

Azure SQL Purchasing Models — DTU, vCore, and Serverless#

az sql db update --name rates-db-staging --server sql-meridian-staging --resource-group rg-shipment-api-staging \
  --edition GeneralPurpose --compute-model Serverless --auto-pause-delay 60

Azure SQL Service Tiers — General Purpose, Business Critical, Hyperscale#

Why Hyperscale's backup/restore speed is worth calling out specifically, worth stating the underlying reasoning: traditional backup/restore time scales with database SIZE — Hyperscale's storage architecture decouples this, so restoring a Hyperscale database takes roughly the same time whether it's 100 GB or 50 TB, a genuinely different operational characteristic from General Purpose/Business Critical at real scale. A current fact worth noting: Azure Hybrid Benefit stopped applying to NEW Hyperscale databases as of December 2023, with existing ones grandfathered only until December 2026 — a real cost-planning consideration for any Hyperscale adoption today.

Azure SQL High Availability and Failover Groups#

az sql failover-group create --name fg-rates-db --server sql-meridian-prod \
  --resource-group rg-shipment-api-prod --partner-server sql-meridian-secondary \
  --failover-policy Automatic --grace-period 1

A failover group provides a stable connection endpoint that automatically redirects to the current primary — the application connects to the failover group's listener name, never needing to know which specific server is currently active, directly paralleling this series' repeated "abstract the endpoint, not the specific resource" pattern (Front Door origins, Traffic Manager).

Azure SQL Security — TDE, Always Encrypted, and Auditing#

az sql db tde set --name rates-db --server sql-meridian-prod --resource-group rg-shipment-api-prod --status Enabled

az sql db audit-policy update --name rates-db --server sql-meridian-prod \
  --resource-group rg-shipment-api-prod --state Enabled --storage-account stmeridianlogs

Transparent Data Encryption (TDE) encrypts data at rest automatically (on by default); Always Encrypted goes further, encrypting specific sensitive columns CLIENT-SIDE, so even a database administrator with full server access never sees the plaintext value — worth reaching for on genuinely sensitive columns (payment details, if shipment-api ever stores them) where even privileged internal access should be restricted.

Elastic Pools and Query Performance Insight#

az sql elastic-pool create --name pool-meridian-shared --server sql-meridian-prod \
  --resource-group rg-shipment-api-prod --edition GeneralPurpose --capacity 8

az sql db update --name rates-db-tenant-a --server sql-meridian-prod \
  --resource-group rg-shipment-api-prod --elastic-pool pool-meridian-shared

Why an elastic pool is worth reaching for specifically when Meridian Freight has MANY small, independently-scaled databases with unpredictable, non-overlapping usage spikes (a per-carrier-partner database pattern, for instance): pooled resources let one database's quiet period effectively subsidize another's spike, which is cheaper than provisioning each database for its own peak individually — the same statistical multiplexing argument behind cloud computing generally, applied one layer down at the database tier.

# Query Performance Insight surfaces the actual top resource-consuming
# queries — the practical starting point for any performance investigation
az sql db query-performance list --name rates-db --server sql-meridian-prod \
  --resource-group rg-shipment-api-prod

Cosmos DB — Global Distribution Architecture#

az cosmosdb create --name cosmos-meridian --resource-group rg-shipment-api-prod \
  --locations regionName=eastus failoverPriority=0 --locations regionName=westeurope failoverPriority=1 \
  --enable-multiple-write-locations true

Cosmos DB's defining architectural feature, worth stating precisely: adding a region is a configuration change, not a migration — turnkey global distribution with multi-region writes (if enabled) is built into the service itself, a meaningfully different starting point from Azure SQL's failover-group-based approach to multi-region.

Cosmos DB APIs#

Worth stating precisely for the wire-protocol-compatible APIs (MongoDB, Cassandra): consistency level can be set explicitly for these, while Gremlin always uses the account's own default consistency level — a real, API-specific nuance worth confirming before assuming uniform behavior across every API choice.

Cosmos DB Consistency Levels#

Session consistency is the default and the right choice for most applications, worth stating why explicitly: it guarantees a client always sees ITS OWN writes reflected in its own subsequent reads — the most common real requirement (a user submitting data and immediately viewing it back) — without paying Strong consistency's full latency and throughput cost across the entire distributed system.

Cosmos DB Partitioning#

az cosmosdb sql container create --account-name cosmos-meridian --database-name shipments \
  --name events --resource-group rg-shipment-api-prod \
  --partition-key-path "/shipmentId" --throughput 400

Choosing a HIGH-CARDINALITY partition key is worth stating as the single most consequential Cosmos DB design decision, worth explaining precisely why: a low-cardinality key (like status, with only a few possible values) concentrates most data and traffic into a handful of physical partitions — a "hot partition" — while a high-cardinality key like shipmentId or a tenant ID spreads load evenly across many physical partitions, which is what actually lets Cosmos DB scale horizontally. Hierarchical partition keys (a newer capability) let a composite key like tenantId/shipmentId be modeled as real, nested partition levels rather than concatenated into one string key, giving more efficient queries that filter on just the outer level.

# A hierarchical partition key — two real levels, not one concatenated string
az cosmosdb sql container create --account-name cosmos-meridian --database-name shipments \
  --name events-v2 --resource-group rg-shipment-api-prod \
  --partition-key-path "/tenantId" "/shipmentId" --partition-key-version 2

Why hierarchical partition keys are worth preferring over a single concatenated string key ("tenantId_shipmentId") for queries that only filter on the OUTER level, worth stating the underlying reasoning: a query filtering on tenantId alone can target exactly the relevant partitions directly when it's a real, separate partition level — a concatenated string key forces Cosmos DB to fan the query out across every partition, since it has no way to know which partitions might contain a given tenantId prefix without checking all of them.

Cosmos DB Request Units and Autoscale#

az cosmosdb sql container throughput update --account-name cosmos-meridian --database-name shipments \
  --name events --resource-group rg-shipment-api-prod --max-throughput 4000

Request Units (RUs) are Cosmos DB's abstracted, currency-like unit of throughput — every operation (a point read, a query, a write) costs a specific, predictable number of RUs regardless of the underlying hardware. Autoscale lets provisioned throughput scale between 10% and 100% of a configured maximum automatically, a genuinely better fit than manually-provisioned throughput for docs-processor's bursty, unpredictable ingestion pattern.

Cosmos DB Change Feed#

The change feed is a persistent, ordered log of every insert and update made to a Cosmos DB container — read like an event stream, without needing a separate message broker to capture "what changed" for downstream processing.

az cosmosdb sql container throughput show --account-name cosmos-meridian --database-name shipments \
  --name events --resource-group rg-shipment-api-prod

Why this is worth calling out as a genuinely distinct capability from simply querying the container repeatedly, worth stating explicitly: the change feed guarantees every change is captured exactly once, in order, per logical partition — a polling-based "query for recently modified items" approach can miss changes between poll intervals or double-process others, while the change feed processor tracks its own read position durably. A realistic use for Meridian Freight: feeding a real-time analytics dashboard or triggering an Azure Function (Part 10) the moment a shipment status changes, without the analytics pipeline needing to poll the primary transactional container directly at all.

Azure Database for PostgreSQL Flexible Server#

az postgres flexible-server create --name pg-docs-processor --resource-group rg-docs-processor \
  --sku-name Standard_D2ds_v5 --tier GeneralPurpose --storage-size 128 \
  --high-availability ZoneRedundant

Flexible Server is Microsoft's current-generation PostgreSQL offering, with genuinely useful operational controls the earlier Single Server offering (now retired) lacked: user-controlled maintenance windows, a Burstable compute tier for intermittent workloads, and stop/start capability to pause billing entirely during genuinely idle periods (dev/test environments, most obviously).

Azure Database for MySQL Flexible Server#

az mysql flexible-server create --name mysql-legacy-app --resource-group rg-shipment-api-prod \
  --sku-name Standard_B1ms --tier Burstable --storage-size 32

Structurally parallel to PostgreSQL Flexible Server — the same Burstable/GeneralPurpose/BusinessCritical tier model, the same stop/start capability — worth treating as a genuinely separate service from PostgreSQL Flexible Server despite the parallel structure, not two configurations of the same underlying engine.

Choosing Between Azure SQL, Cosmos DB, PostgreSQL, and MySQL#

Managed Instance vs. Self-Managed Database on a VM#

Worth restating a variant of Part 1's Shared Responsibility discussion, applied concretely here: a managed service (SQL Database, Managed Instance, Flexible Server) handles patching, backups, and HA automatically — a self-managed database on a VM (Part 3) gives full control (custom extensions, exact version pinning) at the cost of the platform team owning every one of those operational responsibilities themselves. Meridian Freight defaults to managed services throughout this chapter specifically because none of its workloads have a genuine requirement forcing self-managed control — the default should be managed unless a specific, real constraint says otherwise.

Backup and Point-in-Time Restore#

az sql db restore --dest-name rates-db-restored --name rates-db --server sql-meridian-prod \
  --resource-group rg-shipment-api-prod --time "2026-08-20T08:00:00Z"

Every managed database service in this chapter provides automated backups with point-in-time restore — Part 14 covers the full backup/DR strategy and RTO/RPO framework; this chapter's scope is knowing every service already includes this by default, unlike a self-managed database requiring its own backup solution built from scratch.

Read Replicas and Scaling Reads#

az postgres flexible-server replica create --replica-name pg-docs-processor-replica \
  --source-server pg-docs-processor --resource-group rg-docs-processor

A read replica offloads read-heavy traffic to a separate, asynchronously-replicated copy — genuinely useful for docs-processor's metadata queries running alongside its write-heavy ingestion path, at the cost of read replicas reflecting slightly stale (replication-lag-bound) data, a real tradeoff worth confirming the application can tolerate before adopting.

Azure Database Migration Service#

az dms project create --service-name dms-meridian --resource-group rg-shipment-api-prod \
  --name migrate-sqlserver-to-azuresql --source-platform SQL --target-platform SQLDB

Database Migration Service handles both schema and data migration from an on-premises or self-managed database into a managed Azure service, with online (minimal-downtime, continuous sync until cutover) and offline modes — directly relevant to Meridian Freight's legacy on-premises freight-routing servers' eventual database migration, covered in full in Part 14.

Azure Cache for Redis#

az redis create --name redis-meridian --resource-group rg-driver-portal-prod \
  --sku Standard --vm-size C1

A brief bridge ahead of Part 11's full application-architecture treatment: Azure Cache for Redis is the externalized session-state store Part 6 recommended as the durable fix for session affinity — an in-memory cache any driver-portal instance can read/write to, removing the need for sticky routing entirely once adopted.

A Full Worked Database Bootstrap for Meridian Freight#

# 1. Azure SQL Database for rates-db, General Purpose, vCore, with a failover group
az sql db create --name rates-db --server sql-meridian-prod --resource-group rg-shipment-api-prod \
  --service-objective GP_Gen5_2
az sql failover-group create --name fg-rates-db --server sql-meridian-prod \
  --resource-group rg-shipment-api-prod --partner-server sql-meridian-secondary

# 2. PostgreSQL Flexible Server for docs-processor's metadata, zone-redundant HA
az postgres flexible-server create --name pg-docs-processor --resource-group rg-docs-processor \
  --tier GeneralPurpose --high-availability ZoneRedundant

# 3. Redis for driver-portal's externalized session state
az redis create --name redis-meridian --resource-group rg-driver-portal-prod --sku Standard --vm-size C1

# 4. Enable TDE and auditing on every SQL database
az sql db tde set --name rates-db --server sql-meridian-prod --resource-group rg-shipment-api-prod --status Enabled

Part 9 CLI Cheat Sheet#

Common Mistakes and Interview Traps#

Worked Practice Problems#

Problem 1: Meridian Freight's engineering team builds a Cosmos DB container for shipment tracking events, partitioned by status (a field with only five possible values: pending, in-transit, delivered, delayed, cancelled). As data volume grows, the team observes severe throughput throttling despite provisioning generous RU/s. What's the root cause?

Answer: The root cause is a hot-partition problem caused by choosing a low-cardinality partition key — with only five possible status values, all data (and all traffic) concentrates into at most five physical partitions, regardless of how much total RU/s is provisioned at the container level, since RU/s is distributed across physical partitions and a handful of partitions can only absorb so much load each. The fix is repartitioning around a high-cardinality key like shipmentId, which spreads both data and request load evenly across many physical partitions, allowing the provisioned throughput to actually be used effectively rather than bottlenecking on a handful of overloaded partitions.

Problem 2: An application connects directly to sql-meridian-prod.database.windows.net rather than through a failover group listener. During a planned failover test, the application experiences a multi-minute outage even though the failover itself completed successfully within seconds. What's the disconnect?

Answer: Connecting directly to the specific server's hostname bypasses the failover group's abstraction entirely — after a failover, the SECONDARY server becomes the new primary, but the application is still trying to reach the OLD primary's hostname, which either no longer accepts writes or is unreachable depending on the failover type. The failover group's listener name is specifically designed to always resolve to the current primary automatically; connecting to the underlying server name directly defeats that purpose. The fix is reconfiguring the application's connection string to use the failover group's listener endpoint, not either individual server's own hostname.

Problem 3: A team migrating a legacy on-premises SQL Server application to Azure evaluates Azure SQL Database (single database) versus SQL Managed Instance, prioritizing minimal application code changes. The application relies heavily on cross-database queries and SQL Server Agent jobs. Which service fits, and why would the other be a poor choice?

Answer: SQL Managed Instance is the correct fit — it provides near-100% SQL Server engine compatibility, including cross-database queries and SQL Agent, specifically the two capabilities the application depends on. Azure SQL Database (single database) is scoped to one database with no native cross-database query support and no SQL Agent — adopting it would require re-architecting the application to eliminate cross-database queries and replace SQL Agent jobs with an alternative scheduling mechanism, directly contradicting the stated priority of minimal application changes. Managed Instance exists precisely for this migration profile: maximum compatibility with an existing SQL Server application, trading some of single-database's simplicity and lower cost for that compatibility.

Problem 4: Meridian Freight onboards fifteen small carrier-partner-specific databases, each provisioned individually at a size covering that partner's own occasional peak load, resulting in most databases sitting mostly idle most of the time while still being billed for their individually-provisioned peak capacity. What alternative architecture reduces this cost, and why does it work?

Answer: An elastic pool is the right architecture — moving all fifteen databases into one shared pool lets them draw from a common resource allocation sized for the AGGREGATE, not each database's individual peak. Since the fifteen partners' peak usage windows are unlikely to all align simultaneously (different partners' shipping volumes peak at different times), the pool's shared capacity can be meaningfully smaller than the sum of fifteen individually-provisioned peaks while still comfortably covering each database's actual peak when it occurs — the same statistical multiplexing benefit that makes cloud computing cost-efficient in general, applied at the database tier specifically for this many-small-databases pattern.

Problem 5: A team wants to trigger an Azure Function (Part 10) every time a shipment's status changes in Cosmos DB, to update a real-time dashboard. An engineer proposes polling the container every 30 seconds for recently modified documents. What's the better-suited Cosmos DB capability, and what does it improve over polling?

Answer: The Cosmos DB change feed is the better-suited capability — it's an ordered, persistent log of every insert/update, and a change feed processor (which can trigger an Azure Function directly via a native binding) tracks its own read position durably, guaranteeing each change is processed exactly once, in order, per logical partition. A 30-second polling approach risks missing rapid successive changes to the same document between poll intervals, double-processing changes if the polling query's window overlaps, and adds needless read load against the primary transactional container purely to detect changes rather than to serve real application reads. The change feed is purpose-built for exactly this "react to changes" pattern, removing both the correctness risk and the unnecessary polling overhead.

Summary and What's Next#

• Azure SQL, Cosmos DB, PostgreSQL/MySQL Flexible Server, and Redis each solve a genuinely distinct data problem — relational transactional, globally-distributed flexible-schema, open-source relational, and in-memory caching respectively.
• Cosmos DB's partition key choice is the single most consequential design decision — high cardinality spreads load across physical partitions; low cardinality creates a hot-partition bottleneck no amount of provisioned RU/s fixes.
• Session consistency is Cosmos DB's default for good reason — it satisfies the most common real requirement (a client seeing its own writes) without Strong consistency's full latency/throughput cost.
• A managed service should be the default over self-managing a database on a VM unless a specific, genuine requirement forces otherwise — patching, backup, and HA are already handled.
• Always connect through a failover group's listener, never a specific server's hostname — the listener is what makes failover transparent to the application.
• SQL Managed Instance exists specifically for migration scenarios needing near-full SQL Server compatibility (cross-database queries, SQL Agent) that single-database Azure SQL doesn't provide.
• Elastic pools let many small, non-overlapping-peak databases share provisioned capacity — a real cost win over individually provisioning each for its own peak.
• Cosmos DB's change feed provides a guaranteed, ordered, exactly-once change stream — the correct tool for reacting to data changes, not a polling loop against the primary container.

Continue to Part 10 (10-containers-and-serverless.md) for AKS, Container Apps, and Azure Functions — the compute layer this chapter's databases actually get called from.