Automated SQL Servers Build in Azure With a Single Terraform Command

Posted by Nikos Tsirmirakis on 2020-01-27

In my previous post, I have described how to build a simple SQL server with the use of Terraform. Now I will describe how to parametrise and prepare it for automated deployment with a single command. We will use a manifest file to specify all required variables and service principal for authentication to Azure so we do not have to log in manually.

All scripts are available in DBAinTheCloud, Github repository.

Service principal

The service principal is similar to service account on Windows server and we will use it to connect to Azure to provision resources. It will allow us to avoid manual logging (step with az login). We will use this script to create it by uploading it to Azure Shell and executing it.

TIP: ApplicationPassword is auto-generated and there is no way to retrieve it again afterwards so please save it is a safe place.

Code hardening and modularization

Splitting the files

For better code maintenance we will split it into logical sections like main, vm, storage etc and add provider details, Terraform will read all *.tf files in the folder.

Provider file contains credentials for a service principle which we are using to authenticate to Azure cloud, more details available here.

  provider "azurerm" {
    subscription_id = "${var.subscription_id}"
    tenant_id       = "${var.tenant_id}"
    client_id       = "${var.client_id}"     # Application ID
    client_secret   = "${var.client_secret}" # Application secret
  }

Variable file

Additionally, we will create two files with variables: terraform.tfvars (this file loads automatically) and 03-sql-server-basic.tfvars (this file has to be specified as a parameter). It will allow us to better assign variables. The main goal is to use the same terraform with different variables file for each environment.

Terraform.tfvars - global variable including sensitive information

  subscription_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
  client_id       = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
  client_secret   = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
  tenant_id       = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
    
  allowed-ips     = ["x.x.x.x","x.x.x.x"]
  adminpassword   = "xxx"

  region = "westeurope"
  core_name = "d1-dm2" # environment and project - demo 2
  server_count = 3
  adminusername = "devadmin"
  network_size = 12
  network_no = 2
  build = "20200115.1"

TIP: Terraform.tfvars contains sensitive variables like subscription number and service principal credentials in plain text, to avoid sending it to git repository this file is excluded from replication in .gitignore file.

You can find more information about variables at Terraform website here and here.

Adding build number variable

The build number is generated during build process in Azure DevOps (or any other CI/CD framework) it is handy to assign it as a tag to all resources to help with troubleshooting in the future. This variable is generated by the build process and we will have to specify when running terraform apply command.

terrafotm apply -var “build=.1.2.3.4”

Count - creating multiple objects (i.e. VMs)

Count parameter allows you to create multiple objects of the same type, we are using it, for example, to create multiple VMs of the same type. If we would like to use it as a part of the name we have to increase it by 1 as it is starting at 0.

  resource "azurerm_public_ip" "pip1" {
    count                   = "${var.server_count}"
    name                    = "${local.prefix}-pip${count.index + 1}"
    location                = "${azurerm_resource_group.rg1.location}"
    resource_group_name     = "${azurerm_resource_group.rg1.name}"
    allocation_method       = "Dynamic"
    idle_timeout_in_minutes = 30
    
    tags = "${local.tags}"
  }

Map lookup

This variable allows us to predefine the mapping of variables. In this example, we are using it to predefined vnet address ranges depending on region parameter.

  variable "vnets" {
    type    = "map"
    default = {
      "westeurope" = "10.10.0.0/16"
      "northeurope" = "10.20.0.0/16"
    }
  }

Subnet prefix

Terraform is coming with a very handy function which allows us to calculate subnet prefix based on network range (vnet in our case), subnet size and subnet number. In essence, it will slice vnet in desire subnet chunks and return subnet address in CIDR format for a given index number. You can find more details about this function here.

  resource "azurerm_subnet" "sn1" {
    name                 = "${local.prefix}-sn"
    resource_group_name  = "${azurerm_resource_group.rg1.name}"
    virtual_network_name = "${azurerm_virtual_network.vn1.name}"
    address_prefix       = "${cidrsubnet("${var.vnets["${var.location}"]}", "${var.network_size}", "${var.network_no}")}"
  }

Running terraform script

Now when all scripts are ready and the service principal is in place we can run terraform scripts. We follow exactly the same steps as in my previous post however this time we add additional parameters like variable, variable files and we do not have to separately authenticate to Azure.

Validate

  terraform validate -var-file=03-sql-server-basic.tfvars -var 'build=0.0.0'

Initialise

  terraform init

Appy

  terraform apply -var-file=03-sql-server-basic.tfvars -var 'build=0.0.0'

Destroy

  terraform destroy -var-file=03-sql-server-basic.tfvars -var 'build=0.0.0'

Coming next …

Building environment within Azure DevOps CI/CD pipeline, stay tuned.