Database Deployment via Terraform - Part 1 - Conversion failed when converting sanity from job..

This post is the first in a mini series talking about the steps involved in deploying Azure SQL Databases via Terraform.

The idea behind this series is to serve as a reference documenting the very first steps involved with deploying azure sql database resources and finishing with some useful tips and tricks to make your deployments easier to manage and more complete.

For this post I will be deploying only the essentials..

Before we begin..

Terraform is an open-source Infrastructure as Code (IaC) tool that allows you to define and provision infrastructure using a declarative configuration language. It enables automated, consistent, and repeatable deployments across multiple cloud providers, including Microsoft Azure.

By defining infrastructure as code, Terraform simplifies the management of cloud resources, reduces manual configuration errors, and ensures that infrastructure can be version-controlled, audited, and easily replicated across different environments. Additionally, Terraform’s state management and execution planning features allow users to preview infrastructure changes before applying them, enhancing control and predictability in deployments.

Why use it for SQL Then ?

Automation & Consistency – Terraform ensures that Azure SQL databases are deployed consistently across different environments (e.g., dev, test, prod) with minimal manual intervention.
Scalability & Efficiency – Infrastructure can be easily scaled or modified by updating the Terraform configuration and applying changes.
Version Control – Terraform configurations can be stored in version control systems (e.g., Git), enabling tracking of changes and collaboration among teams.
Infrastructure as Code (IaC) – Using Terraform allows database configurations to be managed as code, improving maintainability and reducing the risk of manual errors.
Declarative Approach – Instead of manually configuring Azure SQL databases, you define the desired state in Terraform, and it ensures that the infrastructure matches that state.
Easier Rollbacks – If something goes wrong, previous configurations can be restored quickly by reverting to an earlier Terraform state.

Terraform Basics

Terraform has several key components that work together to define, manage, and provision infrastructure. The basic components of Terraform are:

Providers
- Providers are responsible for interacting with cloud platforms and services (e.g., Azure, AWS, Google Cloud).
- Examples: azurerm for Azure, aws for AWS.
- Defined using:

provider “azurerm” {
  features {}
}

provider “azurerm” {

features {}

}

Resources
- Resources define the actual infrastructure components you want to deploy (e.g., virtual machines, databases, storage).
- Example for an Azure SQL Database

resource “azurerm_sql_database” “example” {
  name                = “example-db”
  resource_group_name = azurerm_resource_group.example.name
  location            = azurerm_resource_group.example.location
  server_name         = azurerm_sql_server.example.name
  edition             = “Standard”
}

resource “azurerm_sql_database” “example” {

name = “example-db”

resource_group_name = azurerm_resource_group.example.name

location = azurerm_resource_group.example.location

server_name = azurerm_sql_server.example.name

edition = “Standard”

}

Modules
- Modules are reusable sets of Terraform configuration files that simplify resource organization and management.
- Example usage

module “network” {
  source = “./modules/network”
}

module “network” {

source = “./modules/network”

}

Variables
- Variables allow parameterization of Terraform configurations for flexibility and reusability.
- Example

variable “location” {
  type    = string
  default = “UK South”
}

variable “location” {

type = string

default = “UK South”

}

Outputs
- Outputs provide useful information after Terraform runs, such as resource IDs or connection strings.
- Example

output “sql_server_name” {
  value = azurerm_sql_server.example.name
}

output “sql_server_name” {

value = azurerm_sql_server.example.name

}

State
- Terraform maintains a state file (terraform.tfstate) to track the current infrastructure and manage changes.
- State helps Terraform determine what changes need to be applied.
Provisioners
- Provisioners execute scripts or commands on a resource after creation (e.g., running a script on a VM).
- Example

provisioner “local-exec” {
  command = “echo Deployment complete”
}

provisioner “local-exec” {

command = “echo Deployment complete”

}

Terraform CLI Commands
- terraform init – Initializes Terraform and downloads necessary provider plugins.
- terraform plan – Shows what changes Terraform will make.
- terraform apply – Deploys infrastructure as per the configuration.
- terraform destroy – Destroys all resources defined in the configuration.

Lets Begin

Now that we have covered the basics, lets begin with the deployment.

Prerequisites

Terraform installation – follow the guide below to install terraform on your chosen platform.
- Install and Configure Terraform
Active azure subscription
- Ability to authenticate over MFA

Script out Terraform

Create a directory for creating all the required files whilst also making it the current directory to run terraform from

Create a providers.tf file
- required_version = ">=1.0" → Ensures Terraform 1.0 or newer is used.
- required_providers → Specifies which providers Terraform will use:
  - azurerm (Azure provider): Required for managing Azure resources.
  - random (Random provider): Used to generate random values (e.g., passwords, strings).
- version = "~>3.0" → Uses any version in the 3.x range but prevents automatic upgrades to 4.x.

terraform {
  required_version = “&gt;=1.0”
  required_providers {
    azurerm = {
      source  = “hashicorp/azurerm”
      version = “~&gt;3.0”
    }
    random = {
      source  = “hashicorp/random”
      version = “~&gt;3.0”
    }
  }
}
provider “azurerm” {
  features {}
}

terraform {

required_version = “>=1.0”

required_providers {

azurerm = {

source = “hashicorp/azurerm”

version = “~>3.0”

}

random = {

source = “hashicorp/random”

version = “~>3.0”

}

provider “azurerm” {

features {}

}

Create a main.tf with the following

resource “azurerm_resource_group” “rg” {
  name     = var.resource_group_name
  location = var.resource_group_location
}

resource “random_password” “admin_password” {
  count       = var.admin_password == null ? 1 : 0
  length      = 20
  special     = true
  min_numeric = 1
  min_upper   = 1
  min_lower   = 1
  min_special = 1
}

locals {
  admin_password = try(random_password.admin_password&#91;0].result, var.admin_password)
}

resource “azurerm_mssql_server” “server” {
  name                         = var.sql_server_name
  resource_group_name          = azurerm_resource_group.rg.name
  location                     = azurerm_resource_group.rg.location
  administrator_login          = var.admin_username
  administrator_login_password = local.admin_password
  version                      = “12.0”
}

resource “azurerm_mssql_database” “db” {
  name      = var.sql_db_name
  server_id = azurerm_mssql_server.server.id
}

resource “azurerm_resource_group” “rg” {

name = var.resource_group_name

location = var.resource_group_location

}

resource “random_password” “admin_password” {

count = var.admin_password == null ? 1 : 0

length = 20

special = true

min_numeric = 1

min_upper = 1

min_lower = 1

min_special = 1

}

locals {

admin_password = try(random_password.admin_password[0].result, var.admin_password)

}

resource “azurerm_mssql_server” “server” {

name = var.sql_server_name

resource_group_name = azurerm_resource_group.rg.name

location = azurerm_resource_group.rg.location

administrator_login = var.admin_username

administrator_login_password = local.admin_password

version = “12.0”

}

resource “azurerm_mssql_database” “db” {

name = var.sql_db_name

server_id = azurerm_mssql_server.server.id

}

You can see here the main.tf is creating the thefollowing resources:

Resource Group
Random password object
Local variable to store its output
Logical SQL Server
SQL Database

Create variables.tf

variable “resource_group_location” {
  type        = string
  description = “Location for all resources.”
  default     = “uksouth”
}

variable “resource_group_name” {
  type        = string
  description = “Resource Group Name.”
  default     = “rg”
}

variable “sql_server_name” {
  type        = string
  description = “The name of the SQL Database.”
  default     = “sample-server-1”
}

variable “sql_db_name” {
  type        = string
  description = “The name of the SQL Database.”
  default     = “sample-db-1”
}

variable “admin_username” {
  type        = string
  description = “The administrator username of the SQL logical server.”
  default     = “azureadmin”
}

variable “admin_password” {
  type        = string
  description = “The administrator password of the SQL logical server.”
  sensitive   = true
  default     = null
}

variable “resource_group_location” {

type = string

description = “Location for all resources.”

default = “uksouth”

}

variable “resource_group_name” {

type = string

description = “Resource Group Name.”

default = “rg”

}

variable “sql_server_name” {

type = string

description = “The name of the SQL Database.”

default = “sample-server-1”

}

variable “sql_db_name” {

type = string

description = “The name of the SQL Database.”

default = “sample-db-1”

}

variable “admin_username” {

type = string

description = “The administrator username of the SQL logical server.”

default = “azureadmin”

}

variable “admin_password” {

type = string

description = “The administrator password of the SQL logical server.”

sensitive = true

default = null

}

Create outputs.tf

output “resource_group_name” {
  value = azurerm_resource_group.rg.name
}

output “sql_server_name” {
  value = azurerm_mssql_server.server.name
}


output “admin_password” {
  sensitive = true
  value     = local.admin_password
}

output “resource_group_name” {

value = azurerm_resource_group.rg.name

}

output “sql_server_name” {

value = azurerm_mssql_server.server.name

}

output “admin_password” {

sensitive = true

value = local.admin_password

}

Initialize Terraform

PS C:\GitHub\DBA_Demo\TF_Demo_1&gt; terraform init -upgrade

PS C:\GitHub\DBA_Demo\TF_Demo_1> terraform init –upgrade

Create TF Execution Plan

PS C:\GitHub\DBA_Demo\TF_Demo_1&gt; terraform plan -out main.tfplan

1	PS C:\GitHub\DBA_Demo\TF_Demo_1> terraform plan –out main.tfplan

Apply Plan

terraform apply main.tfplan

1	terraform apply main.tfplan

Key points:

The example terraform apply command assumes you previously ran terraform plan -out main.tfplan.
If you specified a different filename for the -out parameter, use that same filename in the call to terraform apply.
If you didn’t use the -out parameter, call terraform apply without any parameters.

What are the problems with this deployment ?

Structure

While it is possible to define all resources in a single main.tf file, this approach is not recommended for larger or production-grade Terraform deployments. Keeping everything in main.tf can lead to:

Reduced Readability: Harder to navigate and manage as the infrastructure grows.
Difficult Maintenance: Troubleshooting and updating resources become challenging.
Poor Reusability: Makes it difficult to reuse configurations across different projects or environments.
Lack of Modularity: How can you share resources across repo’s if youre using them in individual repos, and are you using old or non approved IaC for certain resources ?

Missing Dependencies

The azurerm_mssql_database resource depends on azurerm_mssql_server, but there’s no explicit dependency defined.

Exposing Admin in Outputs

Even though sensitive = true hides the password from CLI output, it is still stored in Terraform state (terraform.tfstate), which can be a security risk if the state is not properly secured.

Next Steps

This article has showed you how to create a sql database using the bare minimum. Whilst it is a fun exercise, and you have the ability to see how it can be simply done, there are many problems with the approach.

Part 2 of this mini series aims to address some of these problems.

Database Deployment via Terraform – Part 1

Before we begin..

Why use it for SQL Then ?

Terraform Basics

Lets Begin

Prerequisites

Script out Terraform

Initialize Terraform

Create TF Execution Plan

Apply Plan

What are the problems with this deployment ?

Structure

Missing Dependencies

Exposing Admin in Outputs

Next Steps