Chapter 6: Databases

Azure offers a broad selection of managed database services covering relational, NoSQL, in-memory, and time-series workloads. Managed means Azure handles provisioning, patching, backups, and high availability.

Azure SQL Database

Azure SQL Database is a managed relational database built on SQL Server. Zero administration: no OS patching, no SQL Server upgrades.

Deployment Options

Creating Azure SQL Database

# Create a logical SQL Server
az sql server create \
  --resource-group myapp-rg \
  --name myapp-sql-server \
  --location eastus \
  --admin-user sqladmin \
  --admin-password "MyP@ssw0rd!"

# Create a database (General Purpose, 2 vCores, serverless)
az sql db create \
  --resource-group myapp-rg \
  --server myapp-sql-server \
  --name myappdb \
  --edition GeneralPurpose \
  --family Gen5 \
  --capacity 2 \
  --compute-model Serverless \
  --auto-pause-delay 60 \
  --min-capacity 0.5

# Allow Azure services to access the server
az sql server firewall-rule create \
  --resource-group myapp-rg \
  --server myapp-sql-server \
  --name AllowAzureIPs \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

# Allow your current IP
az sql server firewall-rule create \
  --resource-group myapp-rg \
  --server myapp-sql-server \
  --name MyDevMachine \
  --start-ip-address $(curl -s https://ifconfig.me) \
  --end-ip-address $(curl -s https://ifconfig.me)

Connection String

# Get the ADO.NET connection string
az sql db show-connection-string \
  --server myapp-sql-server \
  --name myappdb \
  --client ado.net

Server=tcp:myapp-sql-server.database.windows.net,1433;
Database=myappdb;
User ID=sqladmin;
Password={your_password};
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;

Service Tiers

Backup & High Availability

• Automatic backups: Full weekly, differential 12-hourly, log every 5–12 minutes
• Point-in-time restore: Restore to any point within 1–35 days (configurable)
• Geo-restore: Restore to any region from geo-redundant backup
• Active Geo-Replication: Up to 4 readable secondary replicas in any region
• Failover Groups: Automatic failover with a single connection string endpoint

# Restore a database to a point in time
az sql db restore \
  --resource-group myapp-rg \
  --server myapp-sql-server \
  --name myappdb-restored \
  --source-database-name myappdb \
  --time "2024-01-15T10:00:00"

Azure Cosmos DB

Azure Cosmos DB is a managed, globally distributed NoSQL database. It supports multiple APIs (SQL/Core, MongoDB, Cassandra, Gremlin, Table) and guarantees <10 ms latency at the 99th percentile.

Key Concepts

Cosmos DB Account
└── Database
    └── Container  (= table / collection)
        └── Items  (= rows / documents)

Partition Key: distributes data across physical partitions
              choose a property with high cardinality and even distribution

Creating Cosmos DB

# Create a Cosmos DB account (SQL/Core API, serverless)
az cosmosdb create \
  --resource-group myapp-rg \
  --name myapp-cosmos \
  --locations regionName=eastus \
  --capabilities EnableServerless \
  --default-consistency-level Session

# Create a database
az cosmosdb sql database create \
  --resource-group myapp-rg \
  --account-name myapp-cosmos \
  --name myappdb

# Create a container
az cosmosdb sql container create \
  --resource-group myapp-rg \
  --account-name myapp-cosmos \
  --database-name myappdb \
  --name orders \
  --partition-key-path /customerId

Consistency Levels

Cosmos DB offers 5 consistency models (strongest to weakest):

Global Distribution

# Add a read region (makes data available in West Europe)
az cosmosdb update \
  --resource-group myapp-rg \
  --name myapp-cosmos \
  --locations regionName=eastus failoverPriority=0 \
               regionName=westeurope failoverPriority=1

# Enable multi-region writes (multi-master)
az cosmosdb update \
  --resource-group myapp-rg \
  --name myapp-cosmos \
  --enable-multiple-write-locations true

Partition Key Design

A poor partition key is the most common Cosmos DB performance problem:

Request Units (RU)

Cosmos DB pricing is based on Request Units (RUs): a normalised unit for CPU, I/O, and memory:

• Reading a 1 KB item = ~1 RU
• Writing a 1 KB item = ~5 RU
• Provisioned throughput: pay for reserved RU/s
• Serverless: pay per RU consumed (better for bursty/dev workloads)

Azure Database for PostgreSQL

Managed PostgreSQL: open-source, no vendor lock-in.

# Create a flexible server
az postgres flexible-server create \
  --resource-group myapp-rg \
  --name myapp-postgres \
  --location eastus \
  --admin-user pgadmin \
  --admin-password "MyP@ssw0rd!" \
  --sku-name Standard_D2ds_v4 \
  --tier GeneralPurpose \
  --storage-size 128 \
  --version 16

# Create a database
az postgres flexible-server db create \
  --resource-group myapp-rg \
  --server-name myapp-postgres \
  --database-name myappdb

# Connect via psql
az postgres flexible-server connect \
  --name myapp-postgres \
  --admin-user pgadmin \
  --admin-password "MyP@ssw0rd!" \
  --database-name myappdb

PostgreSQL vs Azure SQL

Azure Database for MySQL

# Create a MySQL flexible server
az mysql flexible-server create \
  --resource-group myapp-rg \
  --name myapp-mysql \
  --location eastus \
  --admin-user mysqladmin \
  --admin-password "MyP@ssw0rd!" \
  --sku-name Standard_D2ds_v4 \
  --tier GeneralPurpose \
  --storage-size 32 \
  --version 8.0

# Allow Azure services
az mysql flexible-server firewall-rule create \
  --resource-group myapp-rg \
  --name myapp-mysql \
  --rule-name AllowAzure \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

Azure Cache for Redis

Azure Cache for Redis is a managed in-memory cache. Use it to reduce database load, cache session state, pub/sub messaging, and rate limiting.

# Create a Redis cache
az redis create \
  --resource-group myapp-rg \
  --name myapp-redis \
  --location eastus \
  --sku Basic \
  --vm-size C1

# Get the connection string
az redis show \
  --resource-group myapp-rg \
  --name myapp-redis \
  --query "hostName" \
  --output tsv

Redis Tiers

Choosing the Right Database

What type of data?
│
├── Relational (tables, JOINs, transactions)?
│   ├── Already use SQL Server → Azure SQL Managed Instance
│   ├── .NET / Microsoft stack → Azure SQL Database
│   └── Open source preference → Azure PostgreSQL or MySQL
│
├── Document / flexible schema?
│   ├── Existing MongoDB code → Cosmos DB (MongoDB API)
│   └── New project, global scale → Cosmos DB (Core/SQL API)
│
├── Key-value / simple lookups?
│   ├── Caching, sessions → Azure Cache for Redis
│   └── Simple structured data → Cosmos DB Table API / Table Storage
│
├── Wide-column?
│   └── Existing Cassandra workload → Cosmos DB (Cassandra API)
│
└── Graph?
    └── Relationship queries → Cosmos DB (Gremlin API)

Next Steps

Continue to 07-identity-security.md to learn about Microsoft Entra ID, role-based access control, and secrets management with Key Vault.