Deploying Azure SQL Database with Terraform

I have been using Terraform for a few years now, and I have to say that I am a big fan. It is versatile and can be extended using PowerShell, Bash, Command line, and ARM where needed to fill gaps in it’s coverage. Here we are going to have a quick look at how to deploy an Azure SQL Database using Terraform.

Assumptions

I’m going to make the following assumptions about the setup of your client workstation before we start writing any HCL and deploying resources.

  1. You already have an Azure subscription with sufficient permissions to deploy a resource group and resources within it.
  2. You have a scripting IDE for writing the HCL (I’m using VSCode with the Azure Terraform, and Hashicorp Terraform extensions).
  3. You have downloaded and configured Terraform so that it is in the PATH environment variable.
  4. You have installed the Azure CLI, logged in, and set your default subscription.

Code

All of the code for this post can be found in this GitHub repository.

What Are We Deploying?

When we think about what we need to do in order to deploy an Azure SQL Database there are several key resources which need to be deployed to support the database. In order to do this we need to deploy a Resource Group, Azure SQL Server, and then the Azure SQL Database.

Diagram showing Azure Subscription with Resource Group (RG-TerraformSqlDb), SQL Server (sql-terraformsqldb), and SQL Database (sqldb_terraform) nested.
Figure 1. Azure Resources.

Terraform Project Layout

As this is a relatively small Terraform deployment I will look to keep my project structure minimal. I personally, like to break out the Provider definition, Tags, and Variables into their own files. I then define my infrastructure within the main.tf file. However, for larger deployments I will go a bit more granular for ease of code maintenance.

First of all I look to define the Resource Group into which we will deploy our SQL Server and Database. Here, I am defining all of the inputs as variables so that I can easily make a name change etc. or deploy multiple instances of the same resources.

resource "azurerm_resource_group" "rgsqldb" {
  name     = var.ResourceGroupName
  location = var.ResourceGroupLocation
  tags     = local.tags
}

You’ll notice that I am using a local declaration for my tags. There is a great article in the Terraform documentation on how to use the locals for simplification of things like tag declaration, you can find that here.

Now that we have the Resource Group defined we can bring our next resource into play, the Azure SQL Server. You will notice that I am using a combination of variables and resource references to define this resource. By referencing the ‘location’ and ‘resource group name’ to the Resource Group definition I am creating an implicit dependency between the two resources. This means that Terraform will know that the Resource Group needs to exist before it can create the Azure SQL Server.

resource "azurerm_sql_server" "sql01" {
  name                         = var.AzSqlServerName
  location                     = azurerm_resource_group.rgsqldb.location
  resource_group_name          = azurerm_resource_group.rgsqldb.name
  version                      = "12.0"
  administrator_login          = var.AzSqlServerAdminAccountName
  administrator_login_password = var.AzSqlServerAdminAccountPassword
  tags                         = local.tags
}

Again the variables are used to parameterise the definition meaning I don’t have to go back and make multiple changes to re-deploy or create another environment.

Finally, we define the Azure SQL Database. Again I am referencing the Azure SQL Server resource to get a property to build that dependency.

resource "azurerm_mssql_database" "appdb01" {
  name           = var.AzSqlDbName
  server_id      = azurerm_sql_server.sql01.id
  collation      = var.AzSqlDbCollation
  max_size_gb    = var.AzSqlDbMaxSizeGb
  sku_name       = var.AzSqlDbSkuName
  zone_redundant = var.AzSqlDbZoneRedundant
  tags           = local.tags
}

Now we have our basic Azure SQL Server deployment ready to go. What we need to do now is ensure that in our variables.tf file we have defined all of the variables which we need for our deployment. One tip that I have is to make sure that in any variable definition that a description with details about what the variable is for is included. This makes it easier when someone comes along and picks up your code and needs to deploy it. Additionally, if there are any fields which only accept specific values it is important to add that information too.

variable "AzSqlDbMaxSizeGb" {
  type        = number
  description = "Please provide an Integer value for the max size of the SQL Database to be created."
}
variable "AzSqlDbSkuName" {
  type        = string
  description = "Please provide the SKU name for the Azure SQL Database tier. Can be obtained with \"az sql db list-editions -a -l <Azure Region> -o table\""
}
variable "AzSqlDbZoneRedundant" {
  type        = bool
  description = "Please provide confirmation (true|false) that Azure SQL Database should be zone redundant."
}
...

As you can see in the snippet above I have defined variables with the data type and also comments about what needs to be provided. In the case of the SKU it is useful to provide something like the PowerShell or CLI command needed to lookup values if one exists. You can find a full version of the variables.tf file in the GitHub repository for this post.

Visualising Our Deployment

Before we move onto deploying our resources, we can look to use a really cool (well, I think it’s cool) feature to generate a diagram of our resources. Terraform has the ability to generate a digraph in the DOT notation so that we can generate a graph diagram using tools such as GraphViz or WebGraphviz. In order to do this we can use the graph command in Terraform, we will need to initialise the Terraform project first (described below) before we can generate the graph. It outputs the digraph definition (snippet below) which we can then put into the graphing tool to generate our diagram.

digraph {
         compound = "true"
         newrank = "true"
         subgraph "root" {
                 "[root] azurerm_mssql_database.appdb01 (expand)" [label = "azurerm_mssql_database.appdb01", shape = "box"]
                 "[root] azurerm_resource_group.rgsqldb (expand)" [label = "azurerm_resource_group.rgsqldb", shape = "box"]
                 "[root] azurerm_sql_server.sql01 (expand)" [label = "azurerm_sql_server.sql01", shape = "box"]
                 "[root] provider[\"registry.terraform.io/hashicorp/azurerm\"]" 
...
!! Definition truncated for post, full output in blog post GitHub repository. !!
...
                 "[root] meta.count-boundary (EachMode fixup)" -> "[root] var.CostCentre"
                 "[root] provider\"registry.terraform.io/hashicorp/azurerm\"" -> "[root] azurerm_mssql_database.appdb01 (expand)"
                 "[root] root" -> "[root] meta.count-boundary (EachMode fixup)"
                 "[root] root" -> "[root] provider\"registry.terraform.io/hashicorp/azurerm\""
         }
 }

The full digraph will give us the following diagram where we can see the dependencies between our resources, variables, providers, and other elements.

Terraform graph diagram of resource dependencies for defined project resources.
Figure 2. Terraform graph resource diagram.

Deploying Resources

Now that we have our project and resources defined we can deploy our resources. The process needed to prepare the project and then deploy it has three key stages, four if you include the destroy phase to remove it all.

Process to show lifecycle of Terraform. Init, Plan, Apply, Destroy.
Figure 3. Terraform resource lifecycle.

Terraform Init

The Terraform Init command will initialise the project and downloads all of the required components. In the case of our project it will download the Azure RM provider and setup all of the internals needed to deploy the project. The Terraform init command needs to be executed in the root folder for our project.

Animated gif showing the terraform init command initialising the terraform project.
Figure 4. Terraform Init Command.

Terraform Plan

Now that we have an initialised project we can look to run Terraform plan to perform a ‘what if’ for our project. This will get terraform to run through and tell us what will be created, updated, or destroyed if we run the terraform apply command. At this point with no resources deployed we will just see the objects to be created, if there were already resources then we would see if any were being changed.

Figure 5. Terraform Plan Command

In the figure above, you will notice that I am using a variables answer file when I run the plan command. This means that terraform will lookup the inputs for the variables there. It is useful when you have several variables and don’t want to enter them at the command line every time.

Terraform Apply

Once we are happy with the resources we want to deploy, we can use Terraform apply to deploy them to our Azure subscription. This will perform a plan, ask us to confirm and then deploy the resources once we have done so.

Terraform apply command deploying Azure SQL database resources to Azure subscription.
Figure 6. Terraform Apply Command

Now we have our resources deployed into our Azure subscription. They are still not quite ready for us to use yet because we need to setup the database firewall and other elements. Almost all of the other configuration can be done with Terraform, or you can do post-deployment scripts. The choice will come down to how much you want to manage with the Terraform state management capability Vs. the flexibility of scripted configuration changes. I will look at both options in other blog posts in the future.

Terraform Destroy

The final stage in this story is about how to get rid of the resources should we want to. If for example we are doing an interactive deployment of an infrastructure to test something then we will want to clear it up. There are two options really, the first is to just delete the resource group via PowerShell or Azure CLI, the other is to use the Terraform Destroy command. For completeness we will clean-up with Terraform as that will also clean up our state files and project folder. If we used the former option then we need to remove those from our project ourselves.

Terraform destroy command removing deployed azure resources.
Figure 7. Terraform Destroy Command.

Now we have cleared up our resources so that we can make sure we are not paying any more than we absolutely have to.

Summary

So, thanks for sticking with this. Now we have been through a focused process of defining and deploying several Azure resources to create an Azure SQL database using Terraform. Hopefully this has helped you see the fundamentals of what is involved and provided a foundation on which you can build more complex deployments.

I’ll be creating more post that build on this to fill in many of the gaps between what we did her and something that is more production ready.

One thought on “Deploying Azure SQL Database with Terraform

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.