Microsoft Azure — Azure SQL Database

Azure is Microsoft's cloud computing platform offering over 200 services. This article covers azure sql database with production-ready configurations, best practices, and hands-on examples.

Why Azure SQL Database Demands a DevOps Mindset

Azure SQL Database is not just SQL Server in the cloud. It's a platform-as-a-service (PaaS) offering that abstracts away hardware, OS, and SQL Server patching. But this abstraction introduces new failure modes: connection pooling exhaustion, DTU throttling, and geo-replication lag. A DevOps approach—infrastructure as code, automated deployments, and monitoring—is essential to avoid outages. Treating Azure SQL as a black box leads to production incidents. Instead, you must codify every aspect: firewall rules, elastic pools, backup policies, and performance tuning. This article walks through a production-ready pipeline for Azure SQL Database, from provisioning to monitoring, with real code and battle-tested practices.

#!/bin/bash
# Provision Azure SQL Database with Terraform
# Requires: Azure CLI, Terraform >=1.5

resource_group="rg-devops-sql"
location="eastus"
server_name="sqlserver-devops-$(openssl rand -hex 4)"
database_name="ordersdb"
admin_login="sqladmin"
admin_password=$(openssl rand -base64 16)

az group create --name $resource_group --location $location

az sql server create \
  --name $server_name \
  --resource-group $resource_group \
  --location $location \
  --admin-user $admin_login \
  --admin-password $admin_password

az sql db create \
  --name $database_name \
  --server $server_name \
  --resource-group $resource_group \
  --service-objective S2 \
  --zone-redundant false

echo "Server: $server_name.database.windows.net"
echo "Database: $database_name"
echo "Admin: $admin_login"
echo "Password: $admin_password"

Schema Migrations with Flyway: Version Control for Your Database

Database schema changes must be versioned, repeatable, and automated. Flyway is a mature tool that applies SQL migration scripts in order. Each migration is a SQL file with a version number (e.g., V1__create_orders.sql). Flyway tracks applied migrations in a flyway_schema_history table. This approach eliminates manual schema drift and enables rollbacks. In a CI/CD pipeline, run Flyway as a build step before deploying the application. For Azure SQL, use the JDBC driver with integrated security or SQL authentication. Always test migrations against a staging database that mirrors production schema and data volume.

-- Migration: Create orders table
-- Version: 1
-- Author: devops@thecodeforge.io

CREATE TABLE orders (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    total_amount DECIMAL(18,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    CONSTRAINT CK_orders_status CHECK (status IN ('PENDING','SHIPPED','DELIVERED','CANCELLED'))
);

CREATE INDEX IX_orders_customer_id ON orders(customer_id);
CREATE INDEX IX_orders_order_date ON orders(order_date);

CI/CD Pipeline: Deploying Database Changes with Azure DevOps

Integrate Flyway into an Azure DevOps pipeline to automate database deployments. The pipeline should: (1) run Flyway migrate against a staging database, (2) run integration tests, (3) run Flyway migrate against production. Use YAML pipelines with environment approvals. Store migration scripts in the same repo as application code. Use Azure Key Vault to store connection strings. Add a rollback step: Flyway undo (if using paid version) or a manual revert script. Monitor the pipeline for failures—a failed migration should block the release. This pipeline enforces that every schema change goes through code review and automated testing.

trigger:
  branches:
    include:
      - main

variables:
  - group: 'AzureSQL-ConnectionStrings'

stages:
  - stage: Build
    jobs:
      - job: RunMigrations
        steps:
          - task: FlywayTask@1
            inputs:
              flywayCommand: 'migrate'
              url: '$(STAGING_CONNECTION_STRING)'
              locations: 'migrations'
  - stage: DeployToProduction
    dependsOn: Build
    condition: succeeded()
    jobs:
      - deployment: ProductionDeploy
        environment: 'production'
        strategy:
          runOnce:
            deploy:
              steps:
                - task: FlywayTask@1
                  inputs:
                    flywayCommand: 'migrate'
                    url: '$(PROD_CONNECTION_STRING)'
                    locations: 'migrations'

Performance Tuning: Indexing and Query Store

Azure SQL Database includes Query Store, which captures query performance metrics. Use it to identify regressions after deployments. Create indexes based on actual workload, not guesswork. Use sys.dm_db_missing_index_details to find missing indexes. But beware: too many indexes hurt write performance. Use online index operations (ONLINE=ON) to avoid blocking. For read-heavy workloads, consider columnstore indexes. Monitor DTU/DTU consumption: if you hit 100%, you need to scale up or optimize queries. Use Azure SQL Analytics (preview) for historical performance analysis.

-- Find missing indexes with estimated improvement
SELECT
    migs.avg_user_impact,
    migs.avg_total_user_cost,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact DESC;

High Availability and Disaster Recovery: Geo-Replication and Failover Groups

Azure SQL Database offers built-in high availability with a 99.99% SLA for the Premium tier. For cross-region disaster recovery, use active geo-replication or failover groups. Failover groups provide a single endpoint that automatically redirects to the secondary region. Configure a failover group with a readable secondary for reporting. Test failover regularly—don't wait for a disaster. Monitor replication lag using sys.dm_geo_replication_link_status. If lag exceeds your RPO, alert. For critical workloads, use the Business Critical tier with zone-redundant configuration.

# Create failover group with Azure PowerShell
# Requires Az.Sql module

$resourceGroup = "rg-devops-sql"
$primaryServer = "sqlserver-primary"
$secondaryServer = "sqlserver-secondary"
$databaseName = "ordersdb"
$failoverGroupName = "orders-fog"

# Create secondary server (assumes already exists)
# Create failover group
New-AzSqlDatabaseFailoverGroup `
  -ResourceGroupName $resourceGroup `
  -ServerName $primaryServer `
  -FailoverGroupName $failoverGroupName `
  -PartnerResourceGroupName $resourceGroup `
  -PartnerServerName $secondaryServer `
  -Database $databaseName `
  -FailoverPolicy Automatic `
  -GracePeriodWithDataLossHours 1

# Verify
Get-AzSqlDatabaseFailoverGroup `
  -ResourceGroupName $resourceGroup `
  -ServerName $primaryServer `
  -FailoverGroupName $failoverGroupName

Security: Managed Identity, Firewall Rules, and Data Encryption

Azure SQL Database security starts with network isolation: use Azure Private Link to avoid exposing a public endpoint. For authentication, prefer managed identities over SQL authentication—they eliminate credential management. Use Azure AD authentication for users and groups. Encrypt data at rest with Transparent Data Encryption (TDE) enabled by default. For column-level encryption, use Always Encrypted. Configure firewall rules with service tags for Azure services. Regularly audit using Azure SQL Auditing and send logs to Log Analytics. Rotate admin passwords using Key Vault.

#!/bin/bash
# Enable Private Endpoint for Azure SQL Server
# Requires: Azure CLI, existing VNet

resource_group="rg-devops-sql"
server_name="sqlserver-devops"
vnet_name="vnet-prod"
subnet_name="subnet-sql"
private_endpoint_name="pe-sql"

# Create private endpoint
az network private-endpoint create \
  --name $private_endpoint_name \
  --resource-group $resource_group \
  --vnet-name $vnet_name \
  --subnet $subnet_name \
  --private-connection-resource-id $(az sql server show --name $server_name --resource-group $resource_group --query id -o tsv) \
  --group-id sqlServer \
  --connection-name sql-connection

# Configure private DNS zone
az network private-dns zone create \
  --resource-group $resource_group \
  --name privatelink.database.windows.net

az network private-dns link vnet create \
  --resource-group $resource_group \
  --zone-name privatelink.database.windows.net \
  --name sql-dns-link \
  --virtual-network $vnet_name \
  --registration-enabled false

Monitoring and Alerting: Detect Issues Before Users Do

Proactive monitoring is critical for Azure SQL Database. Set up alerts for DTU consumption >80%, deadlocks, failed connections, and long-running queries. Use Azure Monitor metrics and log analytics. Create a dashboard with key metrics: DTU/DTU percentage, active sessions, write log IO, and replication lag. For query-level monitoring, use Query Store and set up a job to report regressions. Use Azure SQL Analytics (preview) for a pre-built monitoring solution. Configure action groups to notify the on-call engineer via SMS or email. Regularly review and tune alert thresholds to avoid alert fatigue.

{
  "location": "East US",
  "properties": {
    "description": "Alert when DTU consumption exceeds 80% for 5 minutes",
    "severity": 2,
    "enabled": true,
    "scopes": ["/subscriptions/.../resourceGroups/rg-devops-sql/providers/Microsoft.Sql/servers/sqlserver-devops/databases/ordersdb"],
    "evaluationFrequency": "PT5M",
    "windowSize": "PT5M",
    "criteria": {
      "allOf": [
        {
          "metricName": "dtu_consumption_percent",
          "operator": "GreaterThan",
          "threshold": 80,
          "timeAggregation": "Average"
        }
      ]
    },
    "actions": [
      {
        "actionGroupId": "/subscriptions/.../resourceGroups/rg-devops-sql/providers/microsoft.insights/actionGroups/ag-oncall"
      }
    ]
  }
}

Cost Management: Right-Sizing and Elastic Pools

Azure SQL Database costs can spiral if not managed. Use the DTU or vCore purchasing model based on workload. For multiple databases with variable usage, use elastic pools to share resources and save costs. Monitor underutilized databases and scale down. Use Azure Advisor recommendations for right-sizing. Consider serverless compute for intermittent workloads—it auto-pauses and scales. Set budgets and alerts in Cost Management. Review reserved capacity for predictable workloads to save up to 40%. Always test performance after scaling to ensure SLAs are met.

# Create an elastic pool with Azure PowerShell
# Requires Az.Sql module

$resourceGroup = "rg-devops-sql"
$serverName = "sqlserver-devops"
$poolName = "orders-pool"
$edition = "Standard"
$dtu = 100
$databaseDtuMax = 20

New-AzSqlElasticPool `
  -ResourceGroupName $resourceGroup `
  -ServerName $serverName `
  -ElasticPoolName $poolName `
  -Edition $edition `
  -Dtu $dtu `
  -DatabaseDtuMax $databaseDtuMax

# Add existing database to pool
Set-AzSqlDatabase `
  -ResourceGroupName $resourceGroup `
  -ServerName $serverName `
  -DatabaseName "ordersdb" `
  -ElasticPoolName $poolName

Hyperscale Tier: Cloud-Native Database at Petabyte Scale

Azure SQL Database Hyperscale is a fundamentally different architecture from General Purpose and Business Critical tiers. It separates compute from storage, enabling nearly instantaneous backups, fast restores (minutes instead of hours), and rapid compute scaling without data movement. Storage auto-grows up to 128 TB with no max size defined at creation — you pay only for allocated storage. Hyperscale supports up to 30 named read-only replicas with independently configurable compute, plus up to 4 geo-replicas for global read scale-out and disaster recovery. The architecture uses a multi-tiered caching system: buffer pool (memory), resilient buffer pool extension (RBPEX on local SSD), and continuous priming that pre-warms secondary replicas with the hottest pages. This ensures consistent performance during failover. Hyperscale is suitable for all workload types — OLTP, HTAP, and analytics. Pricing has no SQL Server license fee, giving it a significant cost advantage over other tiers for high-performance databases. Serverless compute is supported, auto-scaling up to 80 vCores with dynamic memory allocation (up to 24 GB/vCore). Hyperscale limitations: no In-Memory OLTP tables (durable memory-optimized tables), DBCC CHECKDB not directly supported (use DBCC CHECKTABLE), and shrink operations blocked when TDE is disabled. In production, Hyperscale is now the recommended default service tier for all new and modernizing workloads.

# Create a Hyperscale database
az sql db create \
  --resource-group prod-rg \
  --server sqlserver-hs \
  --name orders-hs \
  --service-objective HS_Gen5_32 \
  --hyperscale-edition \
  --zone-redundant true \
  --read-scale-out true \
  --ha-replicas 2

# Add a named replica for read scale-out
az sql db replica create \
  --resource-group prod-rg \
  --server sqlserver-hs-secondary \
  --name orders-hs-replica \
  --partner-database orders-hs \
  --partner-server sqlserver-hs \
  --service-objective HS_Gen5_8

# Check Hyperscale-specific metrics
az monitor metrics list \
  --resource /subscriptions/.../databases/orders-hs \
  --metric "log_write_throughput" "snapshot_backup_size" \
  --interval PT5M

Multiple Geo-Replicas for Hyperscale: Global Read Scale-Out

Active geo-replication for Azure SQL Database Hyperscale now supports up to four readable geo-secondaries (public preview). Previously limited to one geo-replica, this enhancement gives you greater flexibility for disaster recovery, regional read scale-out, and zero-downtime migrations. Each geo-replica is a fully provisioned database in a different Azure region with its own compute resources. Use cases: serve read traffic from multiple global regions for low-latency access, create a warm DR standby in a secondary region while using other replicas for read workloads, migrate databases across regions with near-zero downtime using a planned geo-failover, and change zone redundancy settings during geo-replica creation (not inherited from primary). Important: geo-replicas must be created on different logical servers, chaining (replica of a replica) is not supported, and zone redundancy must be configured during geo-replica creation — it's not inherited from the primary. Failover is straightforward: select the target replica in the portal or use the CLI, then choose failover (planned, no data loss) or forced failover (may lose uncommitted transactions). Monitor replication lag using sys.dm_geo_replication_link_status. For maximum resilience, deploy geo-replicas in the paired region plus one additional region for read scale-out.

# Add geo-replica in West Europe (primary is East US)
az sql db geo-replica create \
  --resource-group prod-rg \
  --server sqlserver-weu \
  --name orders-hs-weu-replica \
  --partner-database orders-hs \
  --partner-server sqlserver-eus \
  --partner-resource-group prod-rg \
  --service-objective HS_Gen5_16 \
  --zone-redundant true

# Add a second geo-replica in Southeast Asia
az sql db geo-replica create \
  --resource-group prod-rg \
  --server sqlserver-sea \
  --name orders-hs-sea-replica \
  --partner-database orders-hs \
  --partner-server sqlserver-eus \
  --partner-resource-group prod-rg \
  --service-objective HS_Gen5_8

# List all geo-replicas
az sql db geo-replica list \
  --resource-group prod-rg \
  --server sqlserver-eus \
  --name orders-hs

# Perform planned failover to West Europe
az sql db geo-replica failover \
  --resource-group prod-rg \
  --server sqlserver-weu \
  --name orders-hs-weu-replica \
  --failover-type Planned

Automatic Tuning: Self-Optimizing Query Performance

Azure SQL Database includes automatic tuning capabilities that continuously monitor and optimize query performance without manual intervention. Two key features: automatic index management (creates/drops indexes based on workload patterns) and automatic plan regression correction (detects when a query plan change causes regressions and reverts to the previous good plan). Automatic tuning uses machine learning to analyze query execution patterns and make optimization decisions. In production, enable automatic tuning with default settings — it's safe and has built-in safeguards to avoid negative impact. You can configure the desired state (ON, OFF, or INHERIT) at the server or database level. For index management: the system identifies missing indexes with high impact, verifies they improve performance after creation, and automatically drops indexes that aren't being used. For plan regression: when a new query plan causes higher CPU or IO compared to the previous plan, the system reverts to the last known good plan within minutes. This is especially valuable after database migrations, version upgrades, or schema changes. Monitor tuning actions via sys.dm_db_tuning_recommendations and Azure Monitor logs. Set up alerts for 'automatic tuning failed' events. While automatic tuning handles the majority of cases, pair it with Query Store for deep analysis of query performance.

-- Enable automatic tuning at the server level
ALTER SERVER CONFIGURATION SET AUTOMATIC_TUNING = ON;

-- View current tuning options
SELECT * FROM sys.dm_db_tuning_status;

-- View automatic tuning recommendations
SELECT
    reason,
    score,
    details,
    create_time
FROM sys.dm_db_tuning_recommendations
ORDER BY score DESC;

-- Manually approve an index recommendation
DECLARE @recommendation_id UNIQUEIDENTIFIER = '...';
EXEC sp_automatic_tuning_approve_recommendation @recommendation_id;