azure-sql

Azure SQL

Deploy and manage Azure SQL Database, Elastic Pools, and Cosmos DB. Covers server provisioning, firewall rules, geo-replication, backup strategies, performance tuning, security hardening, and Terraform configurations.

Prerequisites

These independent examples provision billable resources and can interrupt databases. Confirm subscription, unique names, region/SKU support, SQL_ADMIN_GROUP_ID, cost ceilings, retention/RPO/RTO and tested restore/failover procedures. Use Entra-only authentication below; never put passwords/keys in argv or tracked configuration. Public firewall/VNet rules do not enable a server with public access disabled: configure private endpoints, DNS and an authorized client network. A regional replica is asynchronous and failover can lose data. Cosmos indexing-policy.json and referenced network/secondary-server/storage resources are external prerequisites, not bundled. Choose database-shared or dedicated container throughput and review costs; converting manual throughput to autoscale may require the dedicated migrate operation.

Prerequisites

# Install Azure CLI
# Prerequisite: organization-approved Azure CLI installed from a verified signed package.
az version

# Login and set subscription
az login
az account set --subscription "my-subscription-id"

# Create resource group
az group create --name database-rg --location eastus

SQL Server and Database Creation

Create SQL Server

# Create logical SQL server
az sql server create \
  --resource-group database-rg \
  --name myapp-sqlserver \
  --location eastus \
  --enable-ad-only-auth \
  --external-admin-principal-type Group \
  --external-admin-name "SQL Admins" \
  --external-admin-sid "$SQL_ADMIN_GROUP_ID" \
  --enable-public-network false \
  --minimal-tls-version 1.2

# Enable Azure AD authentication
az sql server ad-admin create \
  --resource-group database-rg \
  --server-name myapp-sqlserver \
  --display-name "SQL Admins" \
  --object-id "{aad-group-object-id}"

# Enable Azure AD only authentication (disable SQL auth)
az sql server ad-only-auth enable \
  --resource-group database-rg \
  --name myapp-sqlserver

Create Databases

# Create General Purpose database
az sql db create \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-db \
  --edition GeneralPurpose \
  --compute-model Serverless \
  --auto-pause-delay 60 \
  --min-capacity 0.5 \
  --max-size 32GB \
  --backup-storage-redundancy Geo \
  --zone-redundant false

# Create Business Critical database for production
az sql db create \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-prod-db \
  --edition BusinessCritical \
  --service-objective BC_Gen5_4 \
  --max-size 256GB \
  --backup-storage-redundancy Geo \
  --zone-redundant true \
  --read-scale Enabled

# Create Hyperscale database for large workloads
az sql db create \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-analytics-db \
  --edition Hyperscale \
  --service-objective HS_Gen5_4 \
  --ha-replicas 2

# List databases on server
az sql db list \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --output table

Elastic Pools

# Create elastic pool
az sql elastic-pool create \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-pool \
  --edition GeneralPurpose \
  --capacity 4 \
  --db-max-capacity 2 \
  --db-min-capacity 0.25 \
  --max-size 256GB \
  --zone-redundant false

# Move database into elastic pool
az sql db update \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-db \
  --elastic-pool myapp-pool

# Monitor elastic pool usage
az sql elastic-pool list-dbs \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-pool \
  --output table

Firewall Rules and Network Security

# Keep public access disabled. The 0.0.0.0 AllowAzureServices exception permits other Azure tenants too; do not enable it as a default.

# Allow specific IP range (office network)
az sql server firewall-rule create \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name AllowOffice \
  --start-ip-address 203.0.113.0 \
  --end-ip-address 203.0.113.255

# Allow your current client IP
az sql server firewall-rule create \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name AllowMyIP \
  --start-ip-address "$APPROVED_CLIENT_IP" \
  --end-ip-address "$APPROVED_CLIENT_IP"

# Create VNet rule for subnet access
az sql server vnet-rule create \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name AllowAppSubnet \
  --vnet-name spoke-prod-vnet \
  --subnet app-subnet

# List firewall rules
az sql server firewall-rule list \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --output table

# Remove a firewall rule
az sql server firewall-rule delete \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name AllowMyIP

Geo-Replication and Failover

# Create failover group with secondary server
az sql server create \
  --resource-group database-rg \
  --name myapp-sqlserver-secondary \
  --location westus \
  --enable-ad-only-auth \
  --external-admin-principal-type Group \
  --external-admin-name "SQL Admins" \
  --external-admin-sid "$SQL_ADMIN_GROUP_ID"

az sql failover-group create \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-failover-group \
  --partner-server myapp-sqlserver-secondary \
  --partner-resource-group database-rg \
  --failover-policy Automatic \
  --grace-period 1 \
  --add-db myapp-prod-db

# Check failover group status
az sql failover-group show \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-failover-group \
  --output table

# Manual failover (for testing or planned maintenance)
az sql failover-group set-primary \
  --resource-group database-rg \
  --server myapp-sqlserver-secondary \
  --name myapp-failover-group

# Create active geo-replication (without failover group)
az sql db replica create \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-db \
  --partner-server myapp-sqlserver-secondary \
  --partner-resource-group database-rg

Backup and Restore

# Configure short-term retention (1-35 days)
az sql db str-policy set \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-prod-db \
  --retention-days 14 \
  --diffbackup-hours 12

# Configure long-term retention
az sql db ltr-policy set \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-prod-db \
  --weekly-retention P4W \
  --monthly-retention P12M \
  --yearly-retention P5Y \
  --week-of-year 1

# Restore database to a point in time
az sql db restore \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-db \
  --dest-name myapp-db-restored \
  --time "2026-03-23T10:00:00Z"

# Restore from long-term backup
az sql db ltr-backup list \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --database myapp-prod-db \
  --output table

# BACPAC export needs a separately reviewed supported authentication/network path.
# Do not pass admin passwords/storage account keys in argv; Entra-only setup above does not support the old SQL-login example.
# Prefer tested backup/restore for recovery; BACPAC is not a transaction-log backup.

Performance Tuning

# Configure automatic tuning in an authenticated connection to the intended primary
# database using T-SQL: ALTER DATABASE CURRENT SET AUTOMATIC_TUNING = AUTO;
# Review the Azure defaults and Query Store state before applying; this is not an az sql db subcommand.

# Check database performance recommendations
az sql db advisor list \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --database myapp-prod-db \
  --output table

# Scale database tier
az sql db update \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-prod-db \
  --service-objective BC_Gen5_8

# Enable Query Store (via SQL)
# sqlcmd -S myapp-sqlserver.database.windows.net -d myapp-prod-db -Q "ALTER DATABASE [myapp-prod-db] SET QUERY_STORE = ON"

# View DTU/vCore usage metrics
az monitor metrics list \
  --resource "/subscriptions/{sub}/resourceGroups/database-rg/providers/Microsoft.Sql/servers/myapp-sqlserver/databases/myapp-prod-db" \
  --metric "cpu_percent" "dtu_consumption_percent" "storage_percent" \
  --interval PT1H \
  --output table

Security Hardening

# Enable Advanced Threat Protection
az sql db threat-policy update \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --name myapp-prod-db \
  --state Enabled \
  --email-addresses security@example.com \
  --email-account-admins true

# Enable auditing to storage
az sql server audit-policy update \
  --resource-group database-rg \
  --name myapp-sqlserver \
  --state Enabled \
  --storage-account mystorageacct \
  --retention-days 90

# Enable auditing to Log Analytics
az sql server audit-policy update \
  --resource-group database-rg \
  --name myapp-sqlserver \
  --state Enabled \
  --lats Enabled \
  --lawri "/subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.OperationalInsights/workspaces/{workspace}"

# Enable Transparent Data Encryption (enabled by default)
az sql db tde set \
  --resource-group database-rg \
  --server myapp-sqlserver \
  --database myapp-prod-db \
  --status Enabled

# Enable vulnerability assessment
# Configure the supported Defender for SQL vulnerability-assessment policy for this logical server; az sql vm targets SQL on Azure VMs, a different resource type.

Cosmos DB

# Create Cosmos DB account with SQL API
az cosmosdb create \
  --resource-group database-rg \
  --name myapp-cosmos \
  --default-consistency-level Session \
  --locations regionName=eastus failoverPriority=0 isZoneRedundant=true \
  --locations regionName=westus failoverPriority=1 isZoneRedundant=false \
  --enable-automatic-failover true \
  --enable-multiple-write-locations false

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

# Create container with partition key
az cosmosdb sql container create \
  --resource-group database-rg \
  --account-name myapp-cosmos \
  --database-name myappdb \
  --name orders \
  --partition-key-path "/customerId" \
  --throughput 400 \
  --idx @indexing-policy.json

# Migrate this dedicated-throughput container from manual to autoscale first.
az cosmosdb sql container throughput migrate \
  --resource-group database-rg \
  --account-name myapp-cosmos \
  --database-name myappdb \
  --name orders \
  --throughput-type autoscale

# After migration completes, set the approved autoscale maximum.
az cosmosdb sql container throughput update \
  --resource-group database-rg \
  --account-name myapp-cosmos \
  --database-name myappdb \
  --name orders \
  --max-throughput 4000

Contents

• Terraform Configuration
• Troubleshooting
• Related Skills

When to Use

• You need a fully managed relational database on Azure.
• Your application requires geo-replication for disaster recovery.
• You need elastic scaling across multiple databases with Elastic Pools.
• You are migrating on-premises SQL Server workloads to the cloud.
• You need a globally distributed NoSQL database (Cosmos DB).

Limitations

• Infrastructure commands can disrupt services: confirm target host/scope and have backups/snapshots before mutating state.
• Docs-only import: upstream scripts and templates not bundled.