Azure VMs & SQL Extension: A Terraform example deployment step-by-step

Introduction
Today we will be deploying an Azure Virtual Machine with the SQL extension using Terraform.
The SQL VM extension in Azure is awesome as it offers a multitude of nice features. It provides a flexible way to manage and configure your SQL Server instances running on Azure VMs. With this extension, you can:
- Automate tasks such as patching the operating system, backups, and more
- Integrate with Azure services like Azure Backup, Azure Key Vault, and Azure Monitor, thereby enhancing the security and manageability of your SQL Server deployments.
We will be provisioning the Azure VM with the SQL extension using Terraform, a popular Infrastructure as Code (IaC) tool. This will enable you to automate and simplify your infrastructure management, allowing you to consistently deploy and manage your SQL Server instances in Azure.
Pre-requisites
- Install Terraform: Ensure that Terraform is installed on your local machine.
- Install Azure CLI
- Have an Azure Subscription where we will deploy resources
- Have an IDE installed on your machine, VS Code for instance
Setting up our project
In your CLI create your working directory:
mkdir sql_deploy
cd sql_deploy/
code . # This opens VS Code using the sql_deploy folder
Login to Azure via the AZ CLI
az login
az account set -s <sub-id> && az account show
Structure your files
- I don't like one big main.tf file with all of the code so I split things up to make it more readable. Inside the
sql_deploy
main folder please create a new file calledmain.tf
and insert the following code.
terraform {
required_version = ">= 1.3.0"
required_providers {
azurerm = {
source = "hashicorp/azurerm"
version = "3.71.0"
}
}
}
provider "azurerm" {
features {}
}
- This code sets up the Azure provider for Terraform.
- Then we want to first create a resource group. Create a new file called
rg.tf
resource "azurerm_resource_group" "rg" {
name = "rg-prod-we-sql"
location = "West Europe"
}
- Next we want a VNET with a subnet for our SQL server, create a file called
vnet.tf
resource "azurerm_virtual_network" "vnet" {
name = "vnet-prod-we-sql"
address_space = ["10.0.0.0/22"]
location = azurerm_resource_group.rg.location
resource_group_name = azurerm_resource_group.rg.name
}
resource "azurerm_subnet" "subnet" {
name = "sn-prod-we-sql"
resource_group_name = azurerm_resource_group.rg.name
virtual_network_name = azurerm_virtual_network.vnet.name
address_prefixes = ["10.0.0.0/24"]
}
- With all of those pre-requisites out of the way we can go ahead and actually deploy the VM, create a file called
vm.tf
resource "azurerm_network_interface" "nic" {
name = "nic-vm-prod-we-sql-01"
location = azurerm_resource_group.rg.location
resource_group_name = azurerm_resource_group.rg.name
ip_configuration {
name = "ipconfig01"
subnet_id = azurerm_subnet.subnet.id
private_ip_address_allocation = "Dynamic"
}
}
resource "azurerm_virtual_machine" "vm" {
name = "vm-prod-we-sql-01"
location = azurerm_resource_group.rg.location
resource_group_name = azurerm_resource_group.rg.name
network_interface_ids = [azurerm_network_interface.nic.id]
vm_size = "Standard_D2s_v3"
storage_image_reference {
publisher = "MicrosoftSQLServer"
offer = "SQL2022-WS2022"
sku = "standard-gen2"
version = "latest"
}
storage_os_disk {
name = "myOSDisk"
caching = "ReadWrite"
create_option = "FromImage"
}
os_profile {
computer_name = "hostname"
admin_username = "adminuser"
admin_password = "P@ssw0rd!" # Bad idea in PROD, this is only for lab environments
}
os_profile_windows_config {
provision_vm_agent = true
}
}
resource "azurerm_virtual_machine_extension" "sql" {
name = "SQLIaasExtension"
virtual_machine_id = azurerm_virtual_machine.vm.id
publisher = "Microsoft.SqlServer.Management"
type = "SqlIaaSAgent"
type_handler_version = "2.0"
auto_upgrade_minor_version = true
}
resource "azurerm_managed_disk" "sql_data_disk" {
name = "disk-vm-prod-we-sql-01-data"
resource_group_name = azurerm_resource_group.rg.name
location = azurerm_resource_group.rg.location
storage_account_type = "Premium_LRS"
create_option = "Empty"
disk_size_gb = "1024"
}
resource "azurerm_virtual_machine_data_disk_attachment" "add_sql_data_disk" {
managed_disk_id = azurerm_managed_disk.sql_data_disk.id
virtual_machine_id = azurerm_virtual_machine.vm.id
lun = "10"
caching = "ReadOnly"
}
resource "azurerm_managed_disk" "sql_log_disk" {
name = "disk-vm-prod-we-sql-01-log"
resource_group_name = azurerm_resource_group.rg.name
location = azurerm_resource_group.rg.location
storage_account_type = "Premium_LRS"
create_option = "Empty"
disk_size_gb = "1024"
}
resource "azurerm_virtual_machine_data_disk_attachment" "add_sql_log_disk" {
managed_disk_id = azurerm_managed_disk.sql_log_disk.id
virtual_machine_id = azurerm_virtual_machine.vm.id
lun = "20"
caching = "None"
}
resource "azurerm_managed_disk" "sql_temp_disk" {
name = "disk-vm-prod-we-sql-01-temp"
resource_group_name = azurerm_resource_group.rg.name
location = azurerm_resource_group.rg.location
storage_account_type = "StandardSSD_LRS"
create_option = "Empty"
disk_size_gb = "128"
}
resource "azurerm_virtual_machine_data_disk_attachment" "add_sql_temp_disk" {
managed_disk_id = azurerm_managed_disk.sql_temp_disk.id
virtual_machine_id = azurerm_virtual_machine.vm.id
lun = "30"
caching = "None"
}
- Note that in lab environments you may want to alter the disk configuration from line 54 in
vm.tf
to cheaper alternatives. I have kept them according to what is best practice for Storage for SQL on IaaS VM, you can find this checklist here
Deploy our SQL
Now that our configuration is complete it is time to actually run our deployment. Make sure your terminal is in the correct directory which is the sql_deploy
folder, you can see this by running pwd
in the terminal which prints your working directory.
- Next we wish to initiate our Terraform workspace
terraform init
- Output should contain
Terraform has been successfully initialized!
The terraform init
command is used to initialize a working directory containing Terraform configuration files It performs several different initialization steps in order to prepare the working directory for Terraform usage.
- Downloads the necessary provider plugins specified in the configuration.
- Sets up the backend for storing your Terraform state.
- Validates the Terraform configuration files in the directory.
- It's safe to run this command multiple times to bring the working directory up to date with changes in the configuration.
- Now we are ready to run our deployment plan
terraform plan
- The
terraform plan
command is used to create an execution plan. It determines what actions are necessary to achieve the desired state defined in the Terraform configuration files. This command is a dry run and does not make any changes to the actual resources or state, think of-WhatIf
in powershell. Instead, it outputs the actions that Terraform will perform when you call terraform apply, allowing you to preview the changes before actually applying them.

- Finally we are ready to apply the changes once we've reviewed the plan output
terraform apply
- The
terraform apply
command is used to apply the changes required to reach the desired state of the configuration. - It executes the actions proposed in the Terraform execution plan. The command will prompt for confirmation before making any changes unless it's run with the
-auto-approve
option, which automatically approves the execution plan and applies the changes. - The changes could be creating, updating, or deleting resources. After the command is run, Terraform will output a summary of the changes made.
Verify our deployment
- While still in the terminal we can list all the resources that we have deployed with Terraform with the
terraform state list
command:
azurerm_managed_disk.sql_data_disk
azurerm_managed_disk.sql_log_disk
azurerm_managed_disk.sql_temp_disk
azurerm_network_interface.nic
azurerm_resource_group.rg
azurerm_subnet.subnet
azurerm_virtual_machine.vm
azurerm_virtual_machine_data_disk_attachment.add_sql_data_disk
azurerm_virtual_machine_data_disk_attachment.add_sql_log_disk
azurerm_virtual_machine_data_disk_attachment.add_sql_temp_disk
azurerm_virtual_machine_extension.sql
azurerm_virtual_network.vnet
- The
terraform state list
command is used to list all resources in the Terraform state file. The state file is a snapshot of your infrastructure's resources at a point in time and Terraform uses it to create plans and make changes to your infrastructure. - When you run
terraform state list
, it will output a list of all the resources that Terraform is managing, and we can see all the resources that we have created
If you head to the Azure Portal and open the resource-group we have created, you can see your new SQL virtual machine that we have deployed.
You could expand on this code-base by creating production-specific variables instead of hard-coding so many values as we have done now. I will usually have a dev.tfvars
and a prod.tfvars
file. Then when I want to apply I point to the specific file with terraform apply -var-file prod.tfvars
for example.
Cleanup
- Once you are satisfied with your testing you can remove all resources by simply running the
terraform destroy
command and approve the destruction of your resources, alternatively runterraform destroy -auto-approve
to skip the verification step.
Summary
In this post we walked through the process of provisioning an Azure VM with the SQL extension using Terraform. We started by setting up the Azure provider and defining the necessary resources, including the resource group, virtual network, subnet, virtual machine and finally of course the SQL extension for the VM. We split these files up for better readability instead of using one large main.tf
file which I always recommend doing.
We deploy the SQL Server extension which enhances the manageability and security of our SQL Server deployment. We used the terraform init
, terraform plan
, and terraform apply
commands to initialize our Terraform configuration, create an execution plan, and apply the changes, respectively.
After applying the changes, we verified our deployment by using the terraform state list
command to list all the resources that Terraform is managing. This gave us a snapshot of our infrastructure's resources at a point in time.
Finally, we discussed how to clean up our resources by using the terraform destroy
command. This command removes all the resources that Terraform has created, allowing us to start fresh or end our testing.
By using Terraform to manage your infrastructure, you can automate and simplify your infrastructure management, allowing you to consistently deploy and manage your SQL Server instances in Azure.
References

About me
