Copy-pasting code snippets from StackOverflow isn’t (always) the best way to do infrastructure as a code (IaC) database deployments. In fact, if you’re ever planning to deploy more than two database services, you should probably consider building your own infrastructure as a code templates and repositories.
Having IaC repositories and templates gives you the ability to deploy database services, over and over again, with consistent quality. The idea of having “golden images” isn’t new. Most database professionals, I’ve spoken with, have their own deployment scripts. The one thing that has changed, is the way we build our servers, using only code. While this can sound complex at first, like with most things, there’s a good chance that someone already did most of the heavy lifting for you. The task that you’re left with, is to find the best starting point, and building guardrails around how the code is used.
In this article, I’ll look into the basic prerequisites of creating your own database deployment repositories, and the additional things you should be doing to ensure continuous quality of the deployments, when using them.
Infrastructure as a code prerequisites
I will not go too deeply into how to actually do the code for the deployments, but I’ll share a few resources to help you get started with IaC. Before you can get started with the coding, there are a couple of things that you’ll need to decide on first. They are.
- Source control system, here we have plenty of options, and many of them come with a free tier.
- Which declarative language or a tool to use for creating deployment, in here the options are to go with cloud native, or cloud-agnostics tools.
- Code editor for writing the deployment code. The best ones, like VS Code, come with extensions that help you write the code by providing templates and auto-completion capabilities.
The selection of the source control system will typically also guide the selection of the deployment mechanism. Azure DevOps has its own way of creating deployment pipelines, and GitHub and Bitbucket have their own. It is, however, possible to use something else for deployments. One variation I’ve seen is the combination of GitHub for source control, and Terraform Cloud for the deployment pipeline.
When thinking about which language to pick for deploying cloud resources, there are a couple of things to consider. The most important one is, are you building on more than one cloud platform? If the answer is “yes”, then I would pick a cloud-agnostic language like Terraform. I know it’s tempting to think, that you would be able to re-use your code between different platforms, if you’ve selected a cloud-agnostic language. However, this is rarely, if ever, the case. The benefit that is there, however, is that you only need to learn one language.
Additionally, you could also pick a cloud- and language-agnostic tool such as Pulumi. This would allow you to build and deploy infrastructure in the language of your own choosing, such as Python or even Java.
Getting started with infrastructure as a code
After setting up your repository and picking the declarative language to use, you’re ready to start. And this is, very commonly, the tricky part. There’s nothing as dreadful as an empty code editor, with that cursor blinking there and mocking you, for not being able to produce a single line of code.
Luckily, you don’t need to start from the scratch.
And no, I am not saying that you should (always) go and find the code from StackOverflow. You can, if you really want to, but cloud providers also offer a nice selection of starter kits and samples for deploying services to their clouds. There are a couple of the official sources, linked below, that I’ve found to be most helpful when getting started with IaC for database deployments.
For the rest of this post, I will focus on sharing my own experiences on how to use the code you have efficiently, and with some safeties in place. And with that, let’s move on to the next two topics.
Re-usable code for database deployments
Once you have a template that’s ready to be used for deployments, the first instinct might be to take that code and change it for each deployment. My advice here is, that you should avoid doing changes or updates to that template directly. An alternative approach, and one which I use, is to use parameters in your deployments. My personal recommendation, especially when working with Bicep, is to use parameter files. There can be dozens of things that could be unique for each deployment, so putting them in a file makes it easy to keep track of the changes. It also gives you a history, and a change log, when you’re storing that parameter file in the source control system.
Ideally, you should have one template that never changes, and a single parameter file for each environment you are deploying. The parameter files themselves are relatively simple JSON files. As an example below, I have a parameter called miName in my Bicep file, and the value for that can be found from the parameter file.
Bicep template:
@description('Managed Instance name')
param miName string
Parameter file:
"miName": {
"value": "mi-name-here"
},
In total, I have little over 30 different parameters I use to deploy a Managed Instance. Now that we have a good template, and we know how to re-use it to deploy multiple Managed Instances. There’s still one topic to tackle.
Keeping the IaC template up to date, and working
The thing about IaC templates is, that just like any other code, it too, evolves. And when code changes after the initial deployment, how can you keep the confidence that it’s still working as it should? The answer is obvious, I think. You test it. As great as it is, having your IaC in repositories, it’s getting even better when you’re properly testing it.
My answer to this issue is to have what I call a “template health check deployment”. The idea of a template health check deployment is to take the code you’ve committed, and use it to create the service(s). I can’t think of a better way to test if something works, than using it. And that’s one of the main reasons I like the cloud, you will always have enough resources for testing.
At first, this might sound like a lot of work, but pretty much everything after you commit the code happens automatically in the background. Let’s take a look at how I’ve set this up for testing the code I’ve created for deploying SQL Server Managed Instances.
Azure DevOps pipeline for database deployments
For this particular deployment, I’ve had to create separate pipelines for deploying and cleaning up. The reason for this is, that sometimes the deployment can last up to 7 or 8 hours. Most of the real-life deployments will be much quicker. This is because the first deployment, for some services, will also trigger some additional changes behind the scenes in Azure. The two pipelines I have for testing the code, are:
- azure-managedinstance-deployment
- azure-mi-hc-deployment-cleanup
In Azure DevOps portal, the view I have for health check deployments looks something like this.
The first one will deploy the Managed Instance, and some scaffolding I’ll need for the testing (VNet, Storage Account, KeyVault). The scaffolding mostly contains services that I’ll expect to have in place for production deployments, but for the testing I’ll create my own. The second one, is for cleaning up the resource group after I am done. In some cases, this could be part of the health check deployment, but for this specific one, I needed to remove it to its own pipeline. The reason being, that Managed Instance deployments are sometimes unpredictable in how long they take.
Just something to note: While I prefer Azure DevOps and BICEP myself, it’s equally easy to set up pipelines for running CloudFormation or Terraform, with Azure DevOps.
IaC deployment pipeline
The pipeline is actually just a YAML file, the one I’ll have here is a cleaned up version of it, so it’s easier to re-use elsewhere. I’ve also broken the pipeline to three parts, to better explain what’s happening where. The first part is basically setting up the pipeline to trigger after the code changes hit the main branch. Besides that, we exclude few files that we want to change, without triggering the pipeline itself. In our use cases, these are typically text and image files, which make up the documentation on how to use the solution. Variables are used in the different stages of the pipeline.
trigger:
batch: "true"
branches:
include:
- main
paths:
exclude:
- azure-pipelines.yml
- README.md
- '**/*.png'
name: MI health check deployment
variables:
vmImageName: 'ubuntu-latest'
azSubscription: 'subscriptionForAzure'
stages:
First stage, build the code
I’ve shorted this pipeline to only have 2 stages, to keep it short and clean. The first is the part where we do the code build. And this is the first part, where you are checking the quality of the code, before attempting to deploy it. What the build actually does, is that it takes the BICEP file(s) and compiles it into a JSON file for Azure Resource Manager (ARM).
stages:
- stage: BICEP_Build
jobs:
- job: Upgrade_AzCli
displayName: Updating azCli
pool:
vmImage: $(vmImageName)
steps:
- task: Bash@3
inputs:
targetType: 'inline'
script: curl -sL https://aka.ms/InstallAzureCLIDeb | sudo bash
- job: Build_BICEP
displayName: Building the BICEP code
pool:
vmImage: $(vmImageName)
steps:
- script: |
az bicep build --file managed-instance.bicep
There are two jobs inside this stage.
- Upgrade_AzCli, which downloads and installs the latest version of az cli. This is a completely optional step, but I prefer to use the latest version.
- Build_BICEP, which run the az bicep build command for our template file.
Second stage, deploy the Managed Instance
In the second stage, after we’ve verified that our code builds, we deploy the Managed Instance.
- stage: Deploy_ManagedInstance
jobs:
- job: Deploy_Managed_Instance
displayName: Deploying the Managed Instance
timeoutInMinutes: "0"
steps:
- checkout: self
- task: AzureCLI@2
inputs:
azureSubscription: $(azSubscription)
scriptType: 'bash'
scriptLocation: 'inlineScript'
inlineScript: |
az deployment group create --resource-group $(resourceGroupName) \
--template-file managed-instance.bicep \
--parameters @mi-deployment-params.json --no-wait
One of the things I have had to do with Managed Instance deployments is to add the –no-wait parameter there. Occasionally, it would run several hours and timeout because of that. With the –no-wait, I tell az cli not to wait until the deployment is completed.
Running and monitoring the IaC pipeline
You can run the pipeline manually, but personally, I prefer it to trigger automatically when the code is committed to main branch. Once that happens, and the pipeline starts to run, and you can monitor that progress also in the Azure DevOps portal.
While the pipeline itself runs quickly, sometimes, it takes a while to do the deployment. Other times, like now, it took a really long while. When I checked the deployment from Azure Portal, I noticed that this one actually took more than 8 hours to complete.
One of the reasons I like Azure DevOps, is that there’s a nice number of integrations for monitoring things like builds and pipelines. Besides the normal emails about successful (or failed) code deployments, you can also integrate 3rd party monitoring solutions to Azure DevOps, like Datadog. With this, you can easily create a dashboard that’s showing you the health of all your pipelines, and through that, the health and quality of your code. Alternatively, you could also create a Wiki page where you can list the latest status badges from Azure DevOps. This is a really neat way to create a list of resources and their pipeline execution status.
To wrap it up
For many database professionals, getting started with Infrastructure as a Code can feel like stepping into a completely unknown territory. My advice there would be to treat it like any code you’ve written until this day. Most of us are familiar using either T-SQL or Powershell, to set up and configure your database services. There’s a huge number of resources that’ll help you get started, and with some extra steps we covered in this article, you can bring your database deployments to an entirely new level.
I also encourage you to learn more about building pipelines. My examples in this post are relatively simple, compared to all they can do.
Leave a Reply