SQL as a Governed Blueprint

Most of the estate this series describes treats Terraform stacks as interchangeable: thin roots, one module call, an operator pipeline. Then you reach the SQL stacks and everything changes — real resource blocks (the only ones in the whole library), a governance service most people have never used in anger, twelve-hour timeouts, and PowerShell bookends on both ends of the apply. The databases got this treatment for the obvious reason: they are the one resource class where a botched apply is not an outage but a data loss.

This part dissects the estate's richest single artifact — the SQL Database and SQL Managed Instance blueprint stacks — and the design question they answer: how do you deploy something whose destruction you cannot afford, using a tool whose core loop is destroy and recreate?

Why Azure Blueprints, of all things

The stacks do not create SQL servers directly. They create an azurerm_blueprint_assignment — an instance of an Azure Blueprint, a governance artifact that bundles ARM templates, policies and role assignments into a versioned, subscription-scoped package. Blueprints are (it must be said) a service on its way to deprecation, but the reason the team reached for one is evergreen, and the assignment resource carries all of it:

resource "azurerm_blueprint_assignment" "sqlmi" {
  name                   = local.assignment_name       # computed: "bpa-${local.instance_name}-${local.blueprint_version}"
  target_subscription_id = data.azurerm_subscription.current.id
  version_id             = data.azurerm_blueprint_published_version.sqlmi.id
  location               = local.region

  lock_mode = "AllResourcesDoNotDelete"

  lock_exclude_principals = [
    data.azurerm_client_config.current.object_id,
  ]

  identity {
    type         = "UserAssigned"
    identity_ids = [data.azurerm_user_assigned_identity.deployer.id]
  }

  resource_groups = <<GROUPS
    {
      "sqlmi-rg": {
        "name": "${local.resource_group}",
        "location": "${local.region}"
      }
    }
  GROUPS

  parameter_values = data.template_file.blueprint_parameters.rendered

  timeouts {
    create = "12h"
  }
}

Six details each deserve a paragraph.

lock_mode = "AllResourcesDoNotDelete" is the whole thesis. Every resource the blueprint deploys — instance, databases, diagnostics wiring — comes into existence behind a platform-level delete lock that even resource-group Owners cannot casually remove; only the principals in lock_exclude_principals (here, the deploying service principal itself) bypass it. Terraform cannot delete what it created without going back through the blueprint machinery. For most resources that would be infuriating. For databases, it converts “someone ran destroy against prod” from a catastrophe into an error message. Guardrails at the platform layer beat guardrails in the pipeline, because they hold no matter which pipeline — or portal session — comes at them.

The UserAssigned identity is a governance choice, not plumbing. The assignment executes as a dedicated managed identity whose prerequisites are documented in comments right above the resource: Blueprint Operator and Contributor at subscription scope, and — because the blueprint wires up Azure AD authentication — owner of the AAD group that holds the Directory Reader role. The deployment has an identity with an auditable, minimal permission set, separate from the pipeline's own credentials.

The assignment name embeds the blueprint version. bpa-<instance>-<version> means a version bump does not update an assignment in place; it produces a differently-named one. Upgrades are explicit replacements.

resource_groups is a JSON heredoc, because the provider models blueprint resource groups as an opaque JSON string rather than blocks — the first hint that this resource is a thin shim over an ARM-shaped API.

timeouts { create = "12h" } is not paranoia. A SQL Managed Instance provision genuinely runs four to twelve hours; the matching pipeline job sets timeoutInMinutes: 1200. When your apply is measured in shifts, everything about delivery changes — more on that below.

And parameter_values is a rendered template, which deserves its own section.

Forty parameters, one template

Blueprint parameters arrive as one JSON document. The stacks render it with a template_file data source — roughly forty entries for the SQL Database flavour, high twenties for Managed Instance — splicing in values from the locals machinery of Part 2:

data "template_file" "blueprint_parameters" {
  template = <<PARAMETERS
{
  "managedInstanceName":                        { "value": "${var.instance_name}" },
  "${var.artifact_prefix}_skuName":             { "value": "${var.sku_name}" },
  "${var.artifact_prefix}_vCores":              { "value": ${var.v_cores} },
  "${var.artifact_prefix}_storageSizeInGB":     { "value": ${var.size_in_gb} },
  "${var.artifact_prefix}_collation":           { "value": "${var.collation}" },
  "${var.artifact_prefix}_sqlAdminGroupId":     { "value": "${var.admin_group_object_id}" },
  "${var.artifact_prefix}_aadDirectoryReaderGroup": { "value": "${var.directory_reader_group}" },
  "${var.artifact_prefix}_customerManagedKVKey":    { "value": "${var.cmk_key_name}" },
  "${var.artifact_prefix}_customerManagedKeyVersion": { "value": "${var.cmk_key_version}" },
  "${var.artifact_prefix}_singleDatabaseNames": { "value": ${jsonencode(var.database_names)} },
  "${var.artifact_prefix}_eplWeeklyRetention":  { "value": ${jsonencode(var.retention_weekly)} }
}
PARAMETERS
}

Three idioms to notice. Blueprint parameters are namespaced per artifact, so a variable holds the artifact prefix and every key is assembled by interpolation — change the artifact name in the blueprint and one variable follows it. Strings interpolate directly, but lists and numbers pass through jsonencode(), because the template is text that must parse as JSON — every value type is the author's responsibility, which is foreshadowing. And the CMK key version is not typed in by hand: the platform-variables JSON carries the full Key Vault key ID, and a split("/", local.json_data.cmk_key_id)[5] peels the version segment off the URI, so rotating the key upstream flows into the blueprint without anyone copying GUID-shaped strings between files.

Everything governance cares about is in that document as data: customer-managed-key encryption, an AAD admin group rather than a person, the directory-reader group the instance needs to resolve AAD logins, long-term retention tiers, diagnostics storage, sizing (GP_Gen5, vCores, storage), collation, timezone, the delegated subnet. Reviewing a new environment is reviewing one JSON-shaped map.

The bookends: what Terraform cannot see

Now the honest part. Azure Blueprints have lifecycle behaviours Terraform cannot model, and the stacks wrap the assignment in null_resource bookends to compensate — the escape-hatch toolbox from Part 8, used at full stretch.

Before the apply: repair the platform. A blueprint assignment can wedge — a failed deployment leaves an assignment object that blocks the next attempt, and Terraform, believing its resource either exists or does not, cannot fix it. So a predeployment null_resource runs PowerShell that checks for an existing assignment and removes it, then polls for the removal to land:

resource "null_resource" "predeployment" {
  triggers = {
    assignment_name = var.assignment_name
    instance_name   = var.instance_name
    sku_name        = var.sku_name
    cmk_key_version = var.cmk_key_version
    # ...one trigger per blueprint input, kept in lockstep
  }

  provisioner "local-exec" {
    interpreter = ["PowerShell", "-command"]
    command     = <<EOT
      $existing = Get-AzBlueprintAssignment -Name ${self.triggers.assignment_name} -ErrorAction Ignore
      if ($null -ne $existing -and $existing.ProvisioningState -ne "Deploying") {
        Remove-AzBlueprintAssignment -Name ${self.triggers.assignment_name}
        # poll up to 5 x 10s for the removal, then fail loudly
      }
    EOT
  }
}

The two SQL stacks show this pattern at two ages. The older (SQL Database) version triggers on timestamp() — run every apply — and only removes assignments in a failed state. The evolved (Managed Instance) version drops the timestamp and instead mirrors every blueprint input in its trigger map, so cleanup re-runs exactly when the assignment will change and not otherwise; and it removes any existing assignment that is not actively deploying, because the team had learned that updating a locked assignment in place is less reliable than replacing it. In the MI stack the predeployment is even promoted to its own module, with the real module depending on it:

module "predeploy_sqlmi" {
  source = "./sqlmi-predeployment"
  # every input the create module receives
}

module "create_sqlmi" {
  source     = "./sqlmi"
  depends_on = [module.predeploy_sqlmi]
  # ...
}

Two-phase composition: phase one makes the platform safe to deploy into, phase two deploys. Terraform's graph enforces the ordering; the trigger mirror enforces the when.

After the apply: configure the data plane. The blueprint can build the instance, but it cannot create SQL logins, map AAD users to sysadmin, or register the credential the engine needs to write native backups to blob storage. A postdeployment null_resource (this one honestly triggered on timestamp() — data-plane config is cheap to reassert) runs a script that pulls the generated admin password from Key Vault, creates the backup container with a stored-access-policy SAS that it writes back into Key Vault, then drives Invoke-SqlCmd against the instance to add the credential and the AAD logins from a checked-in JSON list. The script carries a constraint you would never guess from the HCL: Linux build agents cannot reach a SQL Managed Instance on its private endpoint — which is a load-bearing reason the whole estate runs self-hosted Windows agents.

On destroy: clean up what the blueprint leaves behind. A third null_resource with a when = destroy provisioner calls a removal script, because Terraform holds state for the assignment, not for the resources the assignment deployed — without the hook, destroy would remove one governance object and strand a SQL server behind its own delete locks.

The general pattern recurs wherever Terraform meets a stateful platform service: wrap the resource in a pre-phase that repairs the platform's lifecycle gaps and a post-phase that configures inside the data plane. Kubernetes operators, database engines, message brokers — same shape every time.

The only Gen-2 pipeline in the estate

These stacks are also the only ones that earned the four-stage, plan-artifact pipeline: Prepare (publish the variable repo), Plan (publish out.plan as an artifact), Apply (a deployment job behind an environment: approval gate that applies exactly the reviewed plan, timeoutInMinutes: 1200), and Destroy (guarded twice — a pvar-tf-destroy variable must equal Y and its own gated environment must approve — followed by a PowerShell step that cleans up what HCL cannot). Part 4 covers why plan-artifact promotion matters; here it is non-negotiable: when an apply takes twelve hours, you cannot “just re-run it” to check that what applied was what was reviewed.

The defects, because there are always defects

This is the corner of the estate where dormant bugs taught the sharpest lesson. Reading end-to-end surfaced four:

• Undefined locals in the least-exercised template. The Managed Instance library template's root module passes half a dozen locals — the deployer identity name, the artifact prefix, the DNS-zone partner, all four tag values — that locals.tf never defines. The template cannot plan as written. The deployed instance of the same stack quietly defines every one of them: the fix happened downstream, at instantiation, and never flowed back upstream.
• Doubled key names in the platform-variable lookups. The template reads JSON keys like dev_dev_spoke_sql_backup_storage_account_name — an environment prefix pasted twice. Wrapped in try(..., null), the typo does not error; it silently yields null. The deployed copy, again, uses the corrected single-prefix keys.
• The Destroy stage's init omits the state key. Apply's terraform init sets the backend key explicitly; Destroy's is missing that one line — so the most dangerous operation in the most protected stack was wired to bind the wrong (or a default) state.
• The destroy cleanup step references the other stack's script path — copy-pasted from the SQL Database pipeline into the Managed Instance one.

None of these had detonated, for the ironic reason that the guardrails worked: destroys were rare and doubly gated, and MI redeployments rarer. But that is exactly the trap — the least-exercised path in your most critical stack is where broken code sleeps longest. A quarterly terraform plan smoke test per stack per environment — not apply, just plan — would have caught three of the four for pennies. If you take one operational habit from this article, take that one.

What I'd do differently

• Same intent, newer vehicle. Blueprints are on the way out; today the same posture is Template Specs or Bicep modules via azurerm_subscription_template_deployment, Azure Policy denyAction for delete protection, plus management locks. The load-bearing ideas — platform-level locks with an excluded break-glass principal, versioned parameter packages, governance as reviewable data — transfer intact.
• Plan-test the cold paths. Dormant bugs in low-traffic paths are a category, not an anecdote.
• Derive the trigger mirror mechanically. The predeployment trigger map drifting out of sync with the blueprint inputs is the pattern's one maintenance hazard; feeding both from a single local map removes the failure mode.
• Fix upstream first. Every defect above shares one root cause: the deployed copy got patched and the template did not. A library is only golden if fixes flow back into it.

The blueprint builds the destination. The next part is the journey: moving live, business-attached databases into that Managed Instance with minutes of downtime — Part 11, Migrating Live Databases with Log Replay Service.