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

provider "azurerm" {
  features {}
}

• Resources

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

module "network" {
  source = "./modules/network"
}

• Variables

variable "location" {
  type    = string
  default = "UK South"
}

• Outputs

output "sql_server_name" {
  value = azurerm_sql_server.example.name
}

• State
• Provisioners

provisioner "local-exec" {
  command = "echo Deployment complete"
}

• Terraform CLI Commands

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.
• Active azure subscription

Script out Terraform

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

2. Create a providers.tf file

terraform {
  required_version = ">=1.0"
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = "~>3.0"
    }
    random = {
      source  = "hashicorp/random"
      version = "~>3.0"
    }
  }
}
provider "azurerm" {
  features {}
}

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

3. 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
}

4. 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
}

Initialize Terraform

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

Create TF Execution Plan

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

Apply Plan

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.