Microsoft Azure — Azure SQL Database

#!/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"

-- 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);

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'

-- 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;

# 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

#!/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

{
  "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"
      }
    ]
  }
}

# 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