Deploying Azure VM With SQL Server Using Terraform

Before we start, let's download and install Terraform.

wget https://releases.hashicorp.com/terraform/0.12.9/terraform_0.12.9_linux_amd64.zip

unzip terraform_0.12.9_linux_amd64.zip -d /usr/local/bin/

terraform -v

Now that Terraform is installed, let's create some files.

• provider.tf contains the credentials for connecting to azure subscription
• main.tf contains the deployment code
• variables.tf contains the value of defined resources

Here is my provider.tf file that contains the credentials to connect to the Azure subscription.

provider "azurerm" {
subscription_id = "xxxx-xxxx-xxxx-xxxx-xxxx"
client_id = "xxxx-xxxx-xxxx-xxxx-xxxx"
client_secret = "xxxx-xxxx-xxxx-xxxx-xxxx"
tenant_id = "xxxx-xxxx-xxxx-xxxx-xxxx"
}

Following is my main.tf that will create a resource group, create the virtual network and create the Windows VM with SQL Server installed on it.

//Create an azure Resource Group
resource "azurerm_resource_group" "xlabsrg" {
  name     = "${var.resourcegroup}"
  location = "${var.location}"
}

//The following section creates a virtual network in the 10.0.0.0/16 address space:
resource "azurerm_virtual_network" "xlabsvn" {
    name = "${var.virtnetname}"
    address_space = ["${var.vnet_addr_space}"]
    location = "${azurerm_resource_group.xlabsrg.location}"
    resource_group_name = "${azurerm_resource_group.xlabsrg.name}"
}

//The following section creates a subnet in the above virtual network:
resource "azurerm_subnet" "xlabssnet" {
    name = "${var.subnetname}"
    resource_group_name = "${azurerm_resource_group.xlabsrg.name}"
    virtual_network_name = "${azurerm_virtual_network.xlabsvn.name}"
    address_prefix = "${var.subnet_addr_prefix}"
}

// Create public IPs
resource "azurerm_public_ip" "xlabsip" {
    name                         = "${var.publicip}"
    location                     = "${azurerm_resource_group.xlabsrg.location}"
    resource_group_name          = "${azurerm_resource_group.xlabsrg.name}"
    allocation_method            = "Dynamic"

    tags = {
        environment = "stage"
    }
}

// Create Network Security Group and rule For Sql Server vm
resource "azurerm_network_security_group" "xlabssecgrp" {
    name                = "${var.sqlsecgrp}"
    location            = "${azurerm_resource_group.xlabsrg.location}"
    resource_group_name = "${azurerm_resource_group.xlabsrg.name}"
    
    security_rule {
        name                       = "${var.sqlserver_port1_name}"
        priority                   = "1001"
        direction                  = "Inbound"
        access                     = "Allow"
        protocol                   = "Tcp"
        source_port_range          = "*"
        destination_port_range     = "${var.sql_port_number1}"
        source_address_prefix      = "*"
        destination_address_prefix = "*"
    }

    security_rule {
        name                       = "${var.sqlserver_port2_name}"
        priority                   = "1002"
        direction                  = "Inbound"
        access                     = "Allow"
        protocol                   = "Tcp"
        source_port_range          = "*"
        destination_port_range     = "${var.sql_port_number2}"
        source_address_prefix      = "*"
        destination_address_prefix = "*"
    }
}

//Now connect the VM to the  virtual network, public IP address, and network security group.

//This creates a virtual NIC and connects it to the virtual networking resources you have created:

resource "azurerm_network_interface" "xlabsnic" {
    name = "${var.sqlnicnew}"
    location = "${azurerm_resource_group.xlabsrg.location}"
    resource_group_name = "${azurerm_resource_group.xlabsrg.name}"

    network_security_group_id = "${azurerm_network_security_group.xlabssecgrp.id}" 

    ip_configuration {
        name = "${var.sqlvmipnew-configuration}"
        subnet_id = "${azurerm_subnet.xlabssnet.id}"
        private_ip_address_allocation = "dynamic"
      public_ip_address_id = "${azurerm_public_ip.xlabsip.id}"
    }
}

//The following section creates a VM and attaches the virtual NIC to it.
resource "azurerm_virtual_machine" "xlabsvm" {
    name = "${var.sqlvmname}"
    location = "${azurerm_resource_group.xlabsrg.location}"
    resource_group_name = "${azurerm_resource_group.xlabsrg.name}"
    network_interface_ids = ["${azurerm_network_interface.xlabsnic.id}"]
    vm_size = "${var.vm_size}"

//https://docs.microsoft.com/en-us/azure/virtual-machines/linux/cli-ps-findimage
//Search the VM images in the Azure Marketplace using Azure CLI tool

//az vm image list --location westeurope  --publisher MicrosoftSQLServer  --all --output table

    storage_image_reference {
        offer     = "${var.i_offer}" 
        publisher = "${var.i_publisher}"
        sku       = "${var.i_sku}" 
        version   = "${var.i_version}"
        }

//boot diagnosetic: here you can provide  the url of the blob for the boot logs storage
    boot_diagnostics {
        enabled     = true
        storage_uri = "${var.boot_url}"
        }

//Windows OS disk by default it is of 128 GB
    storage_os_disk {
        name              = "${var.os_disk}"
        caching           = "ReadWrite"
        create_option     = "FromImage"
        managed_disk_type = "Standard_LRS"
            }

// Adding additional disk for persistent storage (need to be mounted to the VM using diskmanagement )
    storage_data_disk {
        name = "${var.add_disk_name}"
        managed_disk_type = "Standard_LRS"
        create_option = "Empty"
        lun = 0
        disk_size_gb = "${var.add_disk_size}"
        }

//Assign the admin uid/pwd and also comupter name
    os_profile {
        computer_name  = "${var.computer_name}"
        admin_username = "${var.admin_username}"
        admin_password = "${var.admin_password}"
    }

//Here defined autoupdate config and also vm agent config
    os_profile_windows_config {  
    //enable_automatic_upgrades = true  
    provision_vm_agent         = true  
  }  
}

//extension configuration section
resource "azurerm_virtual_machine_extension" "xlabsextension" {
  name                 = "SqlIaasExtension"
  location             = "${azurerm_resource_group.xlabsrg.location}"
  resource_group_name  = "${azurerm_resource_group.xlabsrg.name}"
  virtual_machine_name = "${azurerm_virtual_machine.xlabsvm.name}"
  publisher            = "Microsoft.SqlServer.Management"
  type                 = "SqlIaaSAgent"
  type_handler_version = "1.2"

  settings = <<SETTINGS
  {
    "AutoTelemetrySettings": {
      "Region": "West Europe"
    },
    "AutoPatchingSettings": {
      "PatchCategory": "WindowsMandatoryUpdates",
      "Enable": true,
      "DayOfWeek": "Sunday",
      "MaintenanceWindowStartingHour": "2",
      "MaintenanceWindowDuration": "60"
    },
    "KeyVaultCredentialSettings": {
      "Enable": false,
      "CredentialName": ""
    },
    "ServerConfigurationsManagementSettings": {
      "SQLConnectivityUpdateSettings": {
          "ConnectivityType": "Public",
          "Port": "1433"
      },
      "SQLWorkloadTypeUpdateSettings": {
          "SQLWorkloadType": "GENERAL"
      },
      "AdditionalFeaturesServerConfigurations": {
          "IsRServicesEnabled": "true"
      } ,
       "protectedSettings": {
             
           }
           }}
SETTINGS
  tags = {
    terraform = "true"
    Service = "SQL"
  }

}

The variables.tf file will contain values for resources that we've created in our main.tf. Here, my password is in clear text, but it is possible to fetch the password from an Azure Key-vault.

Here is my variables.tf

//Create a azure Resource Group
variable "resourcegroup" {
  description = "Name of the azure ResourceGroup"
  default     = "pm247-stage-resources"
}
variable "location" {
  description = "location for all the resources"
  default     = "West Europe"
}

//The following section creates a virtual network in the 10.0.0.0/16 address space:
variable "virtnetname" {
  description = "name of the virtualNetwork"
  default     = "xVirtualNetwork"
}
variable "vnet_addr_space" {
  description = "range of the ip's for virt. network address space"
  default = "10.0.0.0/16"
}

//The following section creates a subnet in the above virtual network:
variable "subnetname" {
  description = "Name of the SubnetMask"
  default     = "xSubnet"
}

variable "subnet_addr_prefix" {
  description = "subnet address space"
  default = "10.0.2.0/24"
}

// Create public IPs
variable "publicip" {
  description = "Public Ip of the VM"
  default     = "sqlPublicIP"
}

// Create Network Security Group and rule For Sql Server vm
variable "sqlsecgrp" {
  description = "Security Group name of the VM"
  default     = "sql_sec_grp"
}

variable "sqlserver_port1_name" {
  description = "name of the port"
  default = "rdp"
}

variable "sql_port_number1" {
  description = "rdp port"
  default = "3389"
}

variable "sqlserver_port2_name" {
  description = "name of the port2"
  default = "sql"
}

variable "sql_port_number2" {
  description = "sql server port"
  default = "1433"
}

//This creates a virtual NIC named acctNIC connected to the virtual networking resources we have created:
variable "sqlnicnew" {
  description = "sqlvm network interface card"
  default = "sqlnicnew"
}

variable "sqlvmipnew-configuration" {
  description = "name of the sqlvmipnew-configuration"
  default = "sqlvmipnew-configuration"
}

//The following section creates a VM and attaches the virtual NIC to it.

variable "sqlvmname" {
  description = "sqlvmname"
  default = "sqlvminstancenew"
}
variable "vm_size" {
  description = "vm_size"
  default = "Standard_D4s_v3"
}

//Find the VM images in the Azure Marketplace with the Azure CLI tool
variable "i_offer" {
  description = "offer for the vm"
  default = "SQL2016SP1-WS2016"
}
variable "i_publisher" {
  description = "Publisher for the  vm"
  default = "MicrosoftSQLServer"
}

variable "i_sku" {
  description = "sku for the vm"
  default = "SQLDEV"
}
variable "i_version" {
  description = "version for the vm"
  default = "latest"
}

//boot diagnosetic: 
variable "boot_url" {
  description = "boot_url"
  default = "https://xxxxxx.blob.core.windows.net"
}

//Windows OS disk by default it is of 128 GB
variable "os_disk" {
  description = "os_disk"
  default = "sqlnewOsDisk"
}
// Adding additional disk for persistent storage
variable "add_disk_name" {
  description = "additional_disk"
  default = "sqlvm_disk_1"
}
variable "add_disk_size" {
  description = "disk size in GB"
  default = "128"
}

//Here defined admin uid/pwd and also comupter name
variable "computer_name" {
  description = "name"
  default = "sqlxvm"
}

variable "admin_username" {
  description = "name"
  default = "myxadmin"
}

variable "admin_password" {
  description = "passwd"
  default = "213rdssword"
}

It is now time to execute the deployment of our environment. Navigate to the main folder and do a terraform init, this will initialize the project.

Now run terraform plan to see what changes it will do in the Azure subscription. 

terraform plan
output:  Plan: 8 to add, 0 to change, 0 to destroy.

Finally, to apply these changes in azure subscription  

terraform apply