Managed SQL as Code, Safely - Valentine Anagbogu

Overview

Database changes are where a Terraform mistake hurts most: replacements are slow, some settings can't be undone, and one wrong plan can destroy data. These four changes each came with a specific check that kept them safe.

Architecture

Spoke VNet layout. App-to-database traffic inside one VNet never touches the firewall.

The Four Changes

Change	What I did	The check that mattered
Instance rename	Destroy/recreate under new names, with new private endpoints and DNS records. Push runs plan only, and apply runs on manual dispatch	Recovered partial applies after CI token expiry by importing or deleting orphans
Update policy	Released a module version that exposes the policy, and confirmed a no-change plan before opting in	Flagged that the target policy is one-way and asked the requester to confirm it was needed
Entra auth for SQL VMs	Used firewall logs to show the hub firewall was denying `graph.microsoft.com`	Proposed one scoped FQDN rule instead of a broad allow
Decommission (one env)	`count` / `for_each` gated on environment, plus `moved` blocks	The plan showed a handful of destroys. A run with the pipeline's "enable destroy" option planned dozens, so that option was never used

remove from one environment only (illustrative)

locals {
  copy_enabled = var.environment != "uat"
}

resource "azurerm_storage_account" "copy" {
  count = local.copy_enabled ? 1 : 0
  # ...
}

moved {
  from = azurerm_storage_account.copy
  to   = azurerm_storage_account.copy[0]
}

Reviewing the Request, Not Just the Code

One request asked for new IP groups and a firewall rule between an app and its database. Checking it against the real network showed:

App and database were in the same VNet, and the route table only sent 0.0.0.0/0 to the firewall, so the rule would never see that traffic.
The requested IP groups duplicated existing ones.
One CIDR in the request had a typo.

I raised all three with the requester before opening any networking PRs.

Key Outcomes

Managed instances renamed and reconnected through private endpoints and DNS.
A one-way setting held for explicit sign-off instead of applied by default.
An accidental mass destroy avoided by reading the plan.
An ineffective firewall request caught before it became change noise.

Lessons Learned

Read every destroy line. Count them, and know why each one is there.
Ask "is this reversible?" before "how do I apply it?".
A firewall rule only matters if traffic actually passes through the firewall. Check the routes.

Conclusion

Safe database infrastructure isn't about avoiding change. It's about knowing exactly what each change will do, and stopping to ask when the answer can't be undone.

Technologies Used

Azure SQL Managed InstanceSQL Server on Azure VMsTerraformGitHub ActionsAzure FirewallPrivate EndpointsPrivate DNSIP GroupsRoute TablesEntra ID

About this case study: this write-up is drawn from my hands-on platform engineering experience. Organisation names, system names, figures and timelines have been changed or generalised, and all code, configuration and queries are illustrative examples written for this page.

← Back to Projects Get in Touch