5. Deployment

Getting Started proved the service exists. This page makes it repeatable, reviewable, and reversible — and adds the concern that makes databases harder to deploy than anything else in the catalogue: the schema and the data are stateful, and Terraform does not manage them.

That last point deserves stating before any code. Infrastructure as code provisions the database; it does not manage what's inside it. Two pipelines exist for every real system:

Conflating them is the most common Azure SQL deployment mistake. Keep them separate, and never let the infrastructure pipeline's destroy be your schema rollback.

Tool order

Terraform is primary throughout this article, Ansible is secondary for day-2 and post-provision configuration, Bicep/ARM is third.

1. Terraform — the primary path

Module shape

modules/sql-database/
├── main.tf
├── variables.tf
└── outputs.tf

variables.tf

variable "name_prefix" {
  type        = string
  description = "Short workload identifier, e.g. \"orders\""
}

variable "environment" {
  type = string
  validation {
    condition     = contains(["dev", "staging", "prod"], var.environment)
    error_message = "environment must be dev, staging or prod."
  }
}

variable "location" {
  type    = string
  default = "uksouth"
}

variable "sku_name" {
  type        = string
  description = "e.g. GP_S_Gen5_2 (serverless), GP_Gen5_4, BC_Gen5_4, HS_Gen5_4"
  default     = "GP_S_Gen5_2"
}

variable "sql_admin_group_object_id" {
  type        = string
  description = "Object ID of the Entra group that administers the server. Never an individual."
}

variable "subnet_id" {
  type        = string
  description = "Subnet for the private endpoint."
}

variable "private_dns_zone_id" {
  type        = string
  description = "Resource ID of the privatelink.database.windows.net zone."
}

variable "log_analytics_workspace_id" {
  type = string
}

variable "tags" {
  type    = map(string)
  default = {}
}

main.tf

terraform {
  required_version = ">= 1.6"
  required_providers {
    azurerm = { source = "hashicorp/azurerm", version = "~> 4.0" }
    azapi   = { source = "Azure/azapi", version = "~> 2.0" }
  }
}

provider "azurerm" {
  features {
    # The features block is where destroy-time behaviour is decided. For a database,
    # this is the difference between "terraform destroy" being recoverable and not.
    resource_group {
      prevent_deletion_if_contains_resources = true
    }
  }
}

locals {
  suffix    = "${var.name_prefix}-${var.environment}"
  is_prod   = var.environment == "prod"
  base_tags = merge(var.tags, {
    Environment = var.environment
    ManagedBy   = "terraform"
    Workload    = var.name_prefix
  })
}

resource "azurerm_resource_group" "this" {
  name     = "rg-sql-${local.suffix}"
  location = var.location
  tags     = local.base_tags
}

resource "azurerm_mssql_server" "this" {
  name                = "sql-${local.suffix}-${random_string.unique.result}"
  resource_group_name = azurerm_resource_group.this.name
  location            = azurerm_resource_group.this.location
  version             = "12.0"
  minimum_tls_version = "1.2"

  # The single most valuable line on this page: no public endpoint in any environment.
  public_network_access_enabled = false

  azuread_administrator {
    login_username              = "sql-admins-${var.environment}"
    object_id                   = var.sql_admin_group_object_id
    tenant_id                   = data.azurerm_client_config.current.tenant_id
    azuread_authentication_only = true # no SQL logins, no passwords, no password-reset escalation
  }

  identity { type = "SystemAssigned" }

  tags = local.base_tags
}

resource "random_string" "unique" {
  length  = 5
  special = false
  upper   = false
}

data "azurerm_client_config" "current" {}

resource "azurerm_mssql_database" "this" {
  name      = "sqldb-${local.suffix}"
  server_id = azurerm_mssql_server.this.id
  sku_name  = var.sku_name
  collation = "SQL_Latin1_General_CP1_CI_AS"

  # Serverless-only settings; ignored by the provider for provisioned SKUs.
  min_capacity                = startswith(var.sku_name, "GP_S") ? 0.5 : null
  auto_pause_delay_in_minutes = startswith(var.sku_name, "GP_S") ? (local.is_prod ? -1 : 60) : null

  zone_redundant       = local.is_prod
  storage_account_type = local.is_prod ? "GeoZone" : "Local"

  short_term_retention_policy {
    retention_days = local.is_prod ? 35 : 7
  }

  dynamic "long_term_retention_policy" {
    for_each = local.is_prod ? [1] : []
    content {
      weekly_retention  = "P4W"
      monthly_retention = "P12M"
      yearly_retention  = "P7Y"
      week_of_year      = 1
    }
  }

  tags = local.base_tags

  lifecycle {
    prevent_destroy = true # see "Rollback & blast radius" below
  }
}

# --- private connectivity -------------------------------------------------
resource "azurerm_private_endpoint" "sql" {
  name                = "pe-sql-${local.suffix}"
  location            = azurerm_resource_group.this.location
  resource_group_name = azurerm_resource_group.this.name
  subnet_id           = var.subnet_id

  private_service_connection {
    name                           = "psc-sql-${local.suffix}"
    private_connection_resource_id = azurerm_mssql_server.this.id
    subresource_names              = ["sqlServer"] # the sub-resource name matters
    is_manual_connection           = false
  }

  private_dns_zone_group {
    name                 = "default"
    private_dns_zone_ids = [var.private_dns_zone_id]
  }

  tags = local.base_tags
}

# --- observability: diagnostic settings are NOT on by default -------------
resource "azurerm_monitor_diagnostic_setting" "sqldb" {
  name                       = "diag-to-law"
  target_resource_id         = azurerm_mssql_database.this.id
  log_analytics_workspace_id = var.log_analytics_workspace_id

  enabled_log { category = "SQLInsights" }
  enabled_log { category = "AutomaticTuning" }
  enabled_log { category = "QueryStoreRuntimeStatistics" }
  enabled_log { category = "Errors" }
  enabled_log { category = "Timeouts" }
  enabled_log { category = "Blocks" }
  enabled_log { category = "Deadlocks" }

  metric { category = "Basic" }
}

outputs.tf

output "server_fqdn" { value = azurerm_mssql_server.this.fully_qualified_domain_name }
output "database_id"  { value = azurerm_mssql_database.this.id }
output "server_principal_id" {
  value       = azurerm_mssql_server.this.identity[0].principal_id
  description = "Grant this Directory Readers if the server must resolve Entra principals."
}

The loop, unchanged from any other Terraform:

terraform init -backend-config=backends/prod.hcl
terraform plan  -var-file=env/prod.tfvars -out=tfplan
terraform apply tfplan

Where azapi earns its place

azurerm lags new Azure SQL features by weeks to months. When a feature exists in the REST API but not in the provider — a new tier setting, a preview maintenance-window option, a ledger configuration — use azapi rather than a null_resource calling az:

resource "azapi_update_resource" "preview_setting" {
  type        = "Microsoft.Sql/servers/databases@2023-08-01-preview"
  resource_id = azurerm_mssql_database.this.id
  body = {
    properties = {
      # a property azurerm does not yet expose
    }
  }
}

⚠️ Preview API versions have no SLA and can change without notice. Pin the API version explicitly and revisit it.

Remote state and locking

Local state on a team is a footgun: two people apply concurrently, one overwrites the other's record of reality, and the next plan proposes to destroy a production database.

terraform {
  backend "azurerm" {
    resource_group_name  = "rg-tfstate"
    storage_account_name = "sttfstateprod"
    container_name       = "tfstate"
    key                  = "sql-orders-prod.tfstate"
    use_azuread_auth     = true # OIDC/managed identity, not a storage key
  }
}

Azure's backend uses native blob leases for locking — there is no separate lock table to create and no equivalent of the DynamoDB table AWS requires. One less resource, one less way to get it wrong.

Workspaces vs. directory-per-environment: workspaces keep one codebase and switch state keys, which is tidy but makes it easy to apply to prod thinking you're in dev, and makes per-environment structural differences awkward. Directory (or backend-config) per environment is more files and less rope. For a production database, take the extra files.

2. Ansible — the secondary path

Ansible's honest role here is post-provision configuration and day-2 operations: the things that happen after the resource exists and that Terraform either can't express or shouldn't own. Creating database users is the canonical example — a contained user for a managed identity is a data-plane object that has no Terraform resource and no place in infrastructure state.

# playbook: post-provision configuration and day-2 ops
- name: Configure Azure SQL database
  hosts: localhost
  connection: local
  gather_facts: false
  collections:
    - azure.azcollection

  vars:
    resource_group: "rg-sql-orders-prod"
    server_name: "sql-orders-prod-a1b2c"
    database_name: "sqldb-orders-prod"
    app_identity_name: "id-orders-api-prod"

  tasks:
    # Idempotent: a second run reports "ok", not "changed".
    - name: Ensure the database exists at the expected SKU
      azure_rm_sqldatabase:
        resource_group: "{{ resource_group }}"
        server_name: "{{ server_name }}"
        name: "{{ database_name }}"
        sku:
          name: GP_Gen5_4
          tier: GeneralPurpose
        state: present
      register: db_state

    - name: Ensure a firewall rule for the build agent egress IP
      azure_rm_sqlfirewallrule:
        resource_group: "{{ resource_group }}"
        server: "{{ server_name }}"
        name: build-agents
        start_ip_address: "203.0.113.0"
        end_ip_address: "203.0.113.31"
        state: present

    # Data-plane work: create the contained user for the app's managed identity.
    # There is no Terraform resource for this, which is exactly why it lives here.
    - name: Create contained user for the application managed identity
      ansible.builtin.command:
        argv:
          - sqlcmd
          - -S
          - "{{ server_name }}.database.windows.net"
          - -d
          - "{{ database_name }}"
          - -G
          - -Q
          - |
            IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '{{ app_identity_name }}')
            BEGIN
              CREATE USER [{{ app_identity_name }}] FROM EXTERNAL PROVIDER;
              ALTER ROLE db_datareader ADD MEMBER [{{ app_identity_name }}];
              ALTER ROLE db_datawriter ADD MEMBER [{{ app_identity_name }}];
            END
      changed_when: false # the T-SQL is idempotent; report honestly rather than always-changed

Authenticate the playbook with a managed identity on the runner (AZURE_USE_MSI=true) or a workload-identity-federated service principal — never a client secret in a vars file.

Idempotency check: run it twice. The azure_rm_* tasks should report ok on the second run, and the IF NOT EXISTS guard makes the T-SQL task safe to repeat. If a task reports changed every run, it is lying, and someone will eventually ignore a real change because of it.

3. Bicep / ARM — the third path

@description('Short workload identifier')
param namePrefix string

@allowed(['dev', 'staging', 'prod'])
param environment string

param location string = resourceGroup().location
param skuName string = 'GP_S_Gen5_2'
param adminGroupObjectId string
param adminGroupName string

var suffix = '${namePrefix}-${environment}'
var isProd = environment == 'prod'

resource sqlServer 'Microsoft.Sql/servers@2023-08-01-preview' = {
  name: 'sql-${suffix}-${uniqueString(resourceGroup().id)}'
  location: location
  identity: { type: 'SystemAssigned' }
  properties: {
    version: '12.0'
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Disabled'
    administrators: {
      administratorType: 'ActiveDirectory'
      principalType: 'Group'
      login: adminGroupName
      sid: adminGroupObjectId
      tenantId: subscription().tenantId
      azureADOnlyAuthentication: true
    }
  }
}

resource sqlDb 'Microsoft.Sql/servers/databases@2023-08-01-preview' = {
  parent: sqlServer
  name: 'sqldb-${suffix}'
  location: location
  sku: { name: skuName }
  properties: {
    zoneRedundant: isProd
    requestedBackupStorageRedundancy: isProd ? 'GeoZone' : 'Local'
  }
}

output serverFqdn string = sqlServer.properties.fullyQualifiedDomainName

az deployment group what-if \
  --resource-group rg-sql-orders-prod \
  --template-file sql.bicep \
  --parameters namePrefix=orders environment=prod \
               adminGroupObjectId=$GROUP_ID adminGroupName=sql-admins-prod

az deployment group create \
  --resource-group rg-sql-orders-prod \
  --template-file sql.bicep \
  --parameters @prod.bicepparam

Schema migrations — the part Terraform doesn't do

Pick one and be strict about it:

• DACPAC / SqlPackage — the Microsoft-native path. SqlPackage /Action:Publish diffs a compiled database project against the live database and generates the change script. Powerful and dangerous: it will happily generate a DROP COLUMN. Always run with /p:BlockOnPossibleDataLoss=true in a pipeline, and always produce the script for review first (/Action:Script).
• EF Core migrations — natural if the app is .NET, but dotnet ef database update from the app at startup is an anti-pattern in production: it needs elevated permissions at runtime and races across instances. Generate an idempotent SQL script at build time and apply it as a pipeline step.
• Flyway / Liquibase — versioned, forward-only SQL files. The most boring and most predictable option, and the one that survives team turnover best.

The rule that saves you: migrations are forward-only and expand-then-contract. To rename a column, you add the new one, backfill it, deploy code that writes both and reads the new, and only then drop the old — in a later release. Down-migrations are a fiction for anything that has already run against production data.

# GitHub Actions — infrastructure and schema as two stages, with OIDC throughout
name: sql-deploy

on:
  pull_request:
  push:
    branches: [main]

permissions:
  id-token: write   # required for workload identity federation
  contents: read

jobs:
  plan:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: azure/login@v2
        with:
          client-id: ${{ vars.AZURE_CLIENT_ID }}       # federated credential — no secret
          tenant-id: ${{ vars.AZURE_TENANT_ID }}
          subscription-id: ${{ vars.AZURE_SUBSCRIPTION_ID }}
      - uses: hashicorp/setup-terraform@v3
      - run: terraform init -backend-config=backends/prod.hcl
      - run: terraform plan -var-file=env/prod.tfvars -out=tfplan
      - uses: actions/upload-artifact@v4
        with: { name: tfplan, path: tfplan }

  apply-infrastructure:
    needs: plan
    if: github.ref == 'refs/heads/main'
    runs-on: ubuntu-latest
    environment: prod        # manual approval gate lives on the environment
    steps:
      - uses: actions/checkout@v4
      - uses: azure/login@v2
        with:
          client-id: ${{ vars.AZURE_CLIENT_ID }}
          tenant-id: ${{ vars.AZURE_TENANT_ID }}
          subscription-id: ${{ vars.AZURE_SUBSCRIPTION_ID }}
      - uses: actions/download-artifact@v4
        with: { name: tfplan }
      - uses: hashicorp/setup-terraform@v3
      - run: terraform init -backend-config=backends/prod.hcl
      - run: terraform apply -auto-approve tfplan

  apply-schema:
    needs: apply-infrastructure
    runs-on: [self-hosted, vnet]   # must reach the private endpoint
    environment: prod
    steps:
      - uses: actions/checkout@v4
      - uses: azure/login@v2
        with:
          client-id: ${{ vars.AZURE_CLIENT_ID }}
          tenant-id: ${{ vars.AZURE_TENANT_ID }}
          subscription-id: ${{ vars.AZURE_SUBSCRIPTION_ID }}
      - name: Publish schema
        run: |
          sqlpackage /Action:Publish \
            /SourceFile:./artifacts/orders.dacpac \
            /TargetServerName:"${{ vars.SQL_FQDN }}" \
            /TargetDatabaseName:"sqldb-orders-prod" \
            /p:BlockOnPossibleDataLoss=true \
            /AccessToken:"$(az account get-access-token --resource https://database.windows.net/ --query accessToken -o tsv)"

Two things this pipeline does deliberately. It authenticates with workload identity federation (a federated credential on an Entra app registration trusting the GitHub OIDC issuer) rather than a client secret — nothing long-lived exists to leak. And the schema stage runs on a runner inside the VNet, because the database has no public endpoint; a hosted runner cannot reach it, and the temptation to "just open the firewall for the pipeline" is how public endpoints come back.

[Image Prompt: 2D minimalistic pipeline diagram showing a git commit flowing into two parallel tracks — an infrastructure track running terraform plan, manual approval, and terraform apply, and a schema track running a DACPAC build and publish — both landing on dev, staging, and prod Azure SQL databases drawn as three separate resource groups, flat design, clean vector art style, white background]

Environments

Where the boundary should be. In Azure, the subscription is the natural blast-radius, quota, and policy boundary — more so than an AWS account is in practice, because management groups let you govern many subscriptions coherently. For a production database, a separate subscription is the answer more often than not: it isolates the vCore quota, it isolates the RBAC, and it lets a management group carry the policies that make prod prod.

Where Azure Policy enforces it rather than convention:

• deny on Microsoft.Sql/servers where publicNetworkAccess != 'Disabled'.
• deny on servers without azureADOnlyAuthentication.
• audit/deny on databases without a diagnostic setting (DeployIfNotExists will add one).
• deny on non-approved SKU families in dev subscriptions — the cheapest way to stop someone provisioning Business Critical for a test.

Policy is the difference between "we agreed prod is private" and "prod cannot be public".

Rollback and blast radius

What "undo" means here, in order of preference:

1. Re-apply the previous commit. Works for configuration: tier, firewall, retention, diagnostics.
2. Scale back down. Tier changes are reversible online (except out of Hyperscale).
3. Point-in-time restore. The real rollback for data. It restores to a new database, which you then rename into place. Rehearse the rename step — it is the part nobody has done before the incident.
4. Geo-restore or failover group failover for a regional event, with the asynchronous-replication RPO from Architecture.
5. Forward-fix the schema. There is no down-migration for data loss.

Operations that are destructive or force replacement — know these before you apply:

• Changing azurerm_mssql_database.name, collation, or server_id forces replacement, which means delete and recreate an empty database. Terraform will tell you in the plan; the plan output is easy to skim past at 5 p.m. This is why the module above carries prevent_destroy = true.
• Changing the server name replaces the server and everything under it.
• Moving between some tiers is online; moving out of Hyperscale generally is not.
• --mode Complete on an ARM/Bicep deployment, as warned above.

The two Azure-specific traps that look like other problems:

• Resource locks. A CanNotDelete lock on the resource group makes terraform destroy and even some updates fail with an error that reads like a permissions problem. Prod database resource groups should carry one — just know that it is the cause when you see it.
• Soft delete and name reuse. A deleted database's name isn't instantly free, and deleted servers can hold their globally unique DNS name for a period. A recreate-with-the-same-name apply can fail in a way that looks like a race condition. ⚠️ verify current retention-of-name behaviour against current Azure docs.

Set locks with Terraform so they're part of the reviewed configuration:

resource "azurerm_management_lock" "prod_db" {
  count      = local.is_prod ? 1 : 0
  name       = "prevent-delete"
  scope      = azurerm_mssql_database.this.id
  lock_level = "CanNotDelete"
  notes      = "Production database. Remove deliberately, in a PR, with a reason."
}

Drift

Someone will scale the database in the portal during an incident. That is correct behaviour at 3 a.m. and a problem at 9 a.m. Detect it:

• Scheduled terraform plan in CI, nightly, failing the build on a non-empty plan. The single most effective control.
• az deployment group what-if for the Bicep path.
• Azure Policy compliance state — catches drift Terraform doesn't manage (a firewall rule added by hand, public access re-enabled).
• Activity log / Change Analysis — tells you who and when, which is the part the plan can't.

# Who changed this database in the last 24 hours?
az monitor activity-log list \
  --resource-group rg-sql-orders-prod \
  --start-time $(date -u -d '24 hours ago' +%Y-%m-%dT%H:%M:%SZ) \
  --query "[?contains(resourceId, 'databases')].{time:eventTimestamp, who:caller, what:operationName.value, status:status.value}" \
  -o table

Then reconcile deliberately. Either update the code to match reality (if the emergency scale should stay) or re-apply to revert it (if it shouldn't) — and either way, say which in the pull request. Silently letting drift persist trains people to ignore the plan.

Teardown

terraform destroy -var-file=env/dev.tfvars

What destroy will not remove:

• Long-term retention backups. They outlive the database and keep billing. Delete them explicitly (az sql db ltr-backup delete) or you will find them on an invoice months later.
• Resources behind a CanNotDelete lock — the destroy fails partway, leaving a mess.
• The database itself, if prevent_destroy = true — deliberately. Removing that line is the conscious act.
• Role assignments and diagnostic settings created outside the state file, including anything a DeployIfNotExists policy added.
• Private DNS records in a zone that Terraform doesn't own, if the private endpoint was removed in an unusual order.
• Contained database users and SQL-level grants — they lived in the database, so they go with it, but the Entra groups they referenced remain and may now grant nothing anywhere.

What you should be able to do now

Provision an Azure SQL database from a reviewed module with no public endpoint and no passwords, migrate its schema through a separate forward-only pipeline authenticated by OIDC, explain what a --mode Complete deployment would do to the resource group, and detect the morning after that someone scaled it by hand.

Next: Integrations →

← Back to the Azure SQL Database overview · ← Previous: Getting Started