After (lot of ) Hacks - A working way to deploying Azure SQL on Virtual Machine using terraform

I am very new with terraform. I was trying out terraform to deploy an SQL VM to Azure. SQL VM in Azure has undergone many changes recently from using SqlVmIaasExtension to manage SQL VM instance to a new resource type Microsoft.SqlVirtualMachine/SqlVirtualMachines

To implement the best practice of SQL, we had to fall to PowerShell DSC. Now Azure natively support disk management(new Storage Type) via SqlVirtualMachine resource type.

I wanted to get new Storage type implemented via terraform azurerm_mssql_virtual_machine. This resource in terraform under module azurerm supports everything except new storage types.

As seen above, the data disk and other options are configurable - terraform is not yet updated with it. I hope to see that update at the earliest release. The only option I am left with is to use template and azurerm_template_deployment. What this effectively does is creating an ARM template wit new Storage types and deploy them as part of terraform. I used terraform to define vnet, subnet, nsg, data disks, network interface and public IP. Then used template to read files and azurerm_template_deployment for deployment. You can see an detailed examples with code samples can be found from this blog post

All looks good until you encounter an array object to this ARM templates - be it an NSG rule or lun definition it all start to break up. You can read more on the bug report with terraform here.

I’m yet to find a workaround or easier solution for his. So, for now, I made my ARM template simple without any int, bool or array types and some values and deployed them.

Another downside of this method is terraform does not destroy resource created by a template deployment but only delete the template deployment. So when you run terraform destroy, you will get the message that so and so the resource cannot be destroyed. To work around this, create all resources with terraform and use ARM template to link resources.

Another approach I should try is by deploying all resources of SQL VM except azurerm_mssql_virtual_machine and use ARM templates Microsoft.SqlVirtualMachine/SqlVirtualMachines to define SQL VM properties. Probably that will work.It worked.

Working Method

1. Created terraform file with resource and their dependencies
   • azurerm_resource_group
   • azurerm-virtual_network
     • azurerm_subnet
     • azurerm_network_interface
     • azurerm_network_security_group
   • azurerm_subnet_network_security_group_association - azurerm_windows_virtual_machine
   • azurerm_managed_disk
   • azurerm_virtual_machine_data_disk_attachment

2. Create an ARM template to deploy Microsoft.SqlVirtualMachine/SqlVirtualMachines and save it in the same directory as terraform files.

3. Using template provider read file as data

  # Refer ARM template file of SQL
  data "template_file" "sqlvm" {
    template = file("${path.module}/sqlvm.json")
  }

Use these details to create an ARM template deployment using azurerm_template_deployment resource.

Terraform file reference

SQL ARM Template referenced in