Infrastructure as nearly-all-code

T-SQL Tuesday #142 (September 2021) is hosted by Frank Geisler (b|t) – thanks, Frank! Frank's choice of subject is “using descriptive techniques to build database environments”. He draws a distinction between managing infrastructure as code (IaC) using imperative scripting approaches such as PowerShell, or declarative approaches like ARM templates, Bicep or Terraform. IaC approaches mean that your environments can be well-documented, can be stored under version control, can have changes deployed automatically and can be duplicated easily.

The imperative vs declarative question is familiar to anyone who's ever had to deploy changes to a SQL database. A migration-based approach requires you to write and run SQL scripts to get the database schema from where it is now into its target state. You'll have to tolerate situations where the start state isn't quite what you expected, and recover from any interruptions or failures yourself. A state-based approach uses tools (e.g. Visual Studio, deploying a SQL Server Database Project) to determine the difference between the start and target states, automatically generating and executing the SQL statements required to migrate the database into its new state.

It's a bit like driving across town. When you get behind the wheel, you have to know where you're going and how to get there (or at least know when your satnav is not to be trusted 😉). You may have to adjust your route for traffic, or road closures, and if you get a puncture then you'll need a wheel changed. Or you could just jump in a taxi! Taxis are like declarative code – you just declare (OK, tell)) to the driver where you want to go, and getting there is their problem.

So far, so good, but even taxis get stuck in traffic. I'm a huge fan of declarative IaC, and a heavy user of Terraform, but – like the stuck taxi – there are some things that are simply beyond its control. For example:

• You can Terraform an Azure Key Vault's access policies when creating the vault, or separately, but you can't do both. If you're using a CI/CD pipeline to run Terraform, this makes it hard to grant access simultaneously to the pipeline and to the Azure resources it creates – you may simply need to grant CI/CD pipeline access manually.
• You can Terraform an Azure SQL Database, but you can't grant MSI access to it from a CI/CD pipeline (at least, not without a workaround).
• You can Terraform an Azure Cosmos DB Account and automatically add its access keys to a key vault instance – but when the keys rotate, you'll need to update them yourself.

I've wasted a bunch of time chasing issues in new environments, caused simply because I've forgotten some unavoidable manual step.

My point here is not that IaC has its shortcomings, because the advantages easily outweigh these small inconveniences. Instead, it's simply this: Remember (i.e. document!) where, when and how you make manual interventions. 😀