Azure DevOps pipeline to build and destroy SQL server

Posted by Nikos Tsirmirakis on 2020-03-09

In my previous post, I have described how to build SQL servers in Azure with a single terraform apply command. Now I will describe how to configure release pipeline in DevOps Azure to build, destroy this environment and how to enable approval before destroying step. Before we create a release pipeline we will need few prerequisites: storage account, variables in variable group and artifact with a code to use. All scripts are available in DBAinTheCloud, Github repository.

Storage account

First of all, we have to create a storage account and a container for terraform backend configuration (doc). Either we can create it in manually in the portal or execute following PowerShell script in Azure shell. This storage account is used in the provider section of the terraform script.

variable group

To secure sensitive variable we will use variable library. It will allow us to store it in a secure place and refer to it as variables in pipeline steps.

Pipeline (build)

Now we will create an artifact to deploy it with our release process. In our case, it is a simple archive zip file with all scripts (yaml). Build pipeline will also assign a build ID to our artifact and we will be able to refer to it in the artifacts section of the release pipeline.


Release pipeline (demo 3) is split into two sections, artifacts (which code to deploy) and stages with a release process (how to deploy the code).


In this section, we select artifact for the release (latest version of dm3).

Build SQL server

Each stage includes jobs and tasks, in this case, we will use a single job to run all our tasks.

Agent job

In this section, we specify which agent pool (Azure Pipelines) and agent specification (ubuntu-18.04) to use.

We also specify which version of the artifact to download.

Extract files

In this section, we specify where to extract files from the artifact zip archive.

Terraform install

Terraform is not preinstalled on agent however Microsoft provides the task which we use to install it. In our case, it will be version 12.20.

Terraform initialise and remaining steps

All remaining tasks are using the same task type to execute the command-line script on the agent. There are third party tasks available to present all parameters in GUI rather as a command-line option however it is introducing a dependency on another vendor and are lucking flexibility to running all terraform features.

In this task we specify the script to run with reference to variables from our variable library and in advanced options working directory with all terraform scripts.

  • Initialise (init)

terraform version

terraform init -backend-config "arm_subscription_id=$(subscription_id)" -backend-config "arm_client_id=$(client_id)" -backend-config "arm_client_secret=$(client_secret)" -backend-config "arm_tenant_id=$(tenant_id)" -backend-config "key=$(backendfile)"
  • Validate

terraform version

terraform validate
  • Validate

terraform version

terraform plan -var "subscription_id=$(subscription_id)" -var "client_id=$(client_id)" -var "client_secret=$(client_secret)" -var "tenant_id=$(tenant_id)" -var-file=04-sql-server-basic.tfvars -var "build=$(Build.BuildId)" -var "adminpassword=$(adminpassword)" -var="allowed_ip=$(allowed_ip)"
  • Apply

terraform version

terraform apply -auto-approve -var "subscription_id=$(subscription_id)" -var "client_id=$(client_id)" -var "client_secret=$(client_secret)" -var "tenant_id=$(tenant_id)" -var-file=04-sql-server-basic.tfvars -var "build=$(Build.BuildId)" -var "adminpassword=$(adminpassword)" -var="allowed_ip=$(allowed_ip)"

destroy SQL server

Destroy stage is very similar to build one with two differences, instead of applying we have a destroy step and we have a pre-deployment approval configured.

  • Destroy
terraform version

terraform destroy -auto-approve -var "subscription_id=$(subscription_id)" -var "client_id=$(client_id)" -var "client_secret=$(client_secret)" -var "tenant_id=$(tenant_id)" -var-file=04-sql-server-basic.tfvars -var "build=$(Build.BuildId)" -var "adminpassword=$(adminpassword)" -var="allowed_ip=$(allowed_ip)"

Pre-deployment approval

Release pipelines allow implementing some logic and conditions, in our case we configure requirement for approval before destroying SQL server.

When we finish using SQL server we approve destroy the stage and the entire environment will be destroyed.


We have created a deployment pipeline in DevOps Azure.

Coming next …

In the next post, I will describe how to run tests with use of Pester framework and publish results in Azure DevOps.