The world of IT infrastructure is rapidly evolving with the introduction of cloud offerings. While Azure and AWS portals make provisioning resources easier, manually clicking through the interface can lead to human errors and inefficiency. This is where infrastructure as code (IaC) comes into play. In this article, we will explore Terraform, an infrastructure as code tool, and use it to deploy Azure SQL Database.

What is Terraform?

Terraform is an infrastructure as code offering from HashiCorp. It is a tool that allows you to build, change, and manage infrastructure in a safe and repeatable way. With Terraform, operations and infrastructure teams can use a configuration language called the HashiCorp Configuration Language (HCL) to automate deployments and manage environments.

Setting Up the Development Environment

Before we dive into deploying Azure SQL Database with Terraform, there are a few prerequisites:

• An Azure subscription
• A basic understanding of Azure SQL Database
• Basic Azure infrastructure understanding
• Basic coding knowledge

HashiCorp Terraform is installed by default in the Azure Cloud Shell, which we will use for our development and deployment. To get started, log into the Azure portal and launch the Azure Cloud Shell. Once launched, set up a new directory and check the default Terraform version. You can do this by running the following commands:

pwd
mkdir terraformdemo
cd ./terraformdemo
terraform version

Now that we have our development environment set up, we can start writing our Terraform code to deploy Azure SQL Database.

Writing Terraform Code

The set of files used to describe infrastructure in Terraform is known as a Terraform configuration. Configuration files can be written in either the HashiCorp Configuration Language (HCL) or JSON format. In this example, we will be using HCL.

Here is an example of a basic Terraform configuration to deploy Azure SQL Database:

# Provider block
provider "azurerm" {
  version = "~>1.32.0"
}

# Resource group resource block
resource "azurerm_resource_group" "rg" {
  name     = "myTFResourceGroup"
  location = "westus"
}

# SQL Database resource block
resource "azurerm_sql_server" "primary" {
  name                = "sql-primary-07042020"
  resource_group_name = azurerm_resource_group.rg.name
  location            = "westus"
  version             = "12.0"
  administrator_login = "sqladmin"
  administrator_login_password = "xxxxxxxx"
}

# Firewall setting for primary SQL server
resource "azurerm_sql_firewall_rule" "firewall-primary" {
  name                = "FirewallRule1"
  resource_group_name = azurerm_resource_group.rg.name
  server_name         = azurerm_sql_server.primary.name
  start_ip_address    = "90.195.58.138"
  end_ip_address      = "90.195.58.138"
}

# Secondary logical SQL server
resource "azurerm_sql_server" "secondary" {
  name                = "sql-secondary"
  resource_group_name = azurerm_resource_group.rg.name
  location            = "westus"
  version             = "12.0"
  administrator_login = "sqladmin"
  administrator_login_password = "xxxxxxxx"
}

# Firewall setting for secondary SQL server
resource "azurerm_sql_firewall_rule" "firewall-secondary" {
  name                = "FirewallRule1"
  resource_group_name = azurerm_resource_group.rg.name
  server_name         = azurerm_sql_server.secondary.name
  start_ip_address    = "90.195.58.138"
  end_ip_address      = "90.195.58.138"
}

# Azure SQL Database to be created on Primary
resource "azurerm_sql_database" "sql-database" {
  name                = "sql-prod-db"
  resource_group_name = azurerm_sql_server.primary.resource_group_name
  location            = azurerm_sql_server.primary.location
  server_name         = azurerm_sql_server.primary.name
  requested_service_objective_name = "Basic"
}

# Failover group setup using the 2 logical SQL servers and adding the SQL database into it
resource "azurerm_sql_failover_group" "failovergroup" {
  name                = "ramifailovergroup"
  resource_group_name = azurerm_sql_server.primary.resource_group_name
  server_name         = azurerm_sql_server.primary.name
  databases           = [azurerm_sql_database.sql-database.id]
  partner_servers {
    id = azurerm_sql_server.secondary.id
  }
  read_write_endpoint_failover_policy {
    mode           = "Automatic"
    grace_minutes = 60
  }
}

This code sets up a resource group, primary and secondary logical SQL servers, firewall rules, an Azure SQL Database, and a failover group. You can customize the configuration to fit your specific requirements.

Deploying Azure SQL Database

Once you have written your Terraform code, you can use the Terraform CLI to deploy your infrastructure. The following Terraform commands are commonly used:

• terraform init: Initializes a working directory containing Terraform configuration files
• terraform plan: Creates an execution plan, showing which resources will be created, updated, or destroyed
• terraform apply: Applies the changes required to reach the desired state of the configuration
• terraform destroy: Destroys the Terraform-managed infrastructure

To deploy your Azure SQL Database, follow these steps:

1. Run terraform init to download the provider plugins and initialize the working directory.
2. Run terraform plan to create an execution plan and see the changes that will be made.
3. Review the execution plan and ensure it aligns with your expectations.
4. Run terraform apply to apply the changes and provision the Azure SQL Database.
5. Confirm the changes when prompted.

After a few minutes, your Azure SQL Database setup will be deployed and ready to use.

Testing and Managing Your Setup

Once your Azure SQL Database is deployed, you can test and manage your setup using various tools and techniques. For example, you can use SSMS to connect to the primary or secondary logical SQL servers and perform queries or tests.

If you need to make changes to your setup or manage failover groups, you can update your Terraform code and use the Terraform CLI to apply the changes. Terraform will automatically update your infrastructure to match the desired state defined in your code.

Destroying Your Environment

If you no longer need your Azure SQL Database setup, you can use the Terraform CLI to destroy the infrastructure. Running terraform destroy will remove all the resources that were provisioned by Terraform.

It is important to note that destroying your environment will permanently delete all the resources, so use this command with caution.

Conclusion

Terraform is a powerful tool that allows you to deploy and manage infrastructure as code. By using Terraform to deploy Azure SQL Database, you can automate the provisioning process, reduce human errors, and ensure repeatability. With Terraform, you can easily manage and update your infrastructure as your needs evolve.

By following the steps outlined in this article, you can successfully deploy Azure SQL Database with Terraform and take advantage of the benefits of infrastructure as code.