'Terraform - Azure - Using "azurerm_windows_virtual_machine" and "azurerm_mssql_virtual_machine" together - but SQL Storage isn't getting configured
This is regarding Terraform on Azure. In my previous project I have used the legacy "azurerm_virtual_machine" resource + ARM Template to provision the "Microsoft.SqlVirtualMachine/SqlVirtualMachines" resource and have the data disks, luns configured.
That works pretty well.
In my current project, we are making use of the newer resource "azurerm_windows_virtual_machine" + "azurerm_mssql_virtual_machine" together to spin up the SQL VMs. However, it's been a dud so far.
Terraform docs example uses the legacy resource "azurerm_windows_virtual_machine".
Problems
Didn't find a way to describe the data disk and lun id in "azurerm_windows_virtual_machine" As a result
When I don't mention the storage_configuration block in the "azurerm_mssql_virtual_machine", Azure portal shows "Drive is not found in the volumes list." under the SQL Virtual Machine resource ( not the Virtual Machine resource) > Configuration section. I have attached a screenshot.
See error below :
If I try to mention data disk and lun in the storage_configuration block of the "azurerm_mssql_virtual_machine" , the provisioning fails with the error
creating Sql Virtual Machine (Sql Virtual Machine Name "ASQLVM" / Resource Group "a-resource-group"): sqlvirtualmachine.SQLVirtualMachinesClient#CreateOrUpdate: Failure sending request: StatusCode=0 -- Original Error: Code="InvalidDefaultFilePath" Message="Invalid Default File Path"
Is there a good way to provision SQL Virtual Machines using the new "azurerm_windows_virtual_machine" ,+ "azurerm_mssql_virtual_machine" together ?
Solution 1:[1]
Check out the following code. I hope it answers your questions if you haven't already succeeded.
resource "azurerm_windows_virtual_machine" "vm" {
count = length(var.instances)
name = upper(element(var.instances, count.index))
location = azurerm_resource_group.resourcegroup[count.index].location
resource_group_name = azurerm_resource_group.resourcegroup[count.index].name
network_interface_ids = [azurerm_network_interface.nic[count.index].id]
size = var.instancesize
zone = var.instancezone
admin_username = var.vmadmin
admin_password = data.azurerm_key_vault_secret.vmadminpwd.value
enable_automatic_updates = "false"
patch_mode = "Manual"
provision_vm_agent = "true"
tags = var.tags
source_image_reference {
publisher = "MicrosoftSQLServer"
offer = "sql2019-ws2019"
sku = "enterprise"
version = "latest"
}
os_disk {
name = "${element(var.instances, count.index)}-osdisk"
caching = "ReadWrite"
storage_account_type = "StandardSSD_LRS"
disk_size_gb = 250
}
}
# add a data disk - we were going to iterate through a collection, but this is easier for now
resource "azurerm_managed_disk" "datadisk" {
count = length(var.instances)
name = "${azurerm_windows_virtual_machine.vm[count.index].name}-data-disk01"
location = azurerm_resource_group.resourcegroup[count.index].location
resource_group_name = azurerm_resource_group.resourcegroup[count.index].name
storage_account_type = "Premium_LRS"
zones = [var.instancezone]
create_option = "Empty"
disk_size_gb = 1000
tags = var.tags
}
resource "azurerm_virtual_machine_data_disk_attachment" "datadisk_attach" {
count = length(var.instances)
managed_disk_id = azurerm_managed_disk.datadisk[count.index].id
virtual_machine_id = azurerm_windows_virtual_machine.vm[count.index].id
lun = 1
caching = "ReadWrite"
}
# add a log disk - we were going to iterate through a collection, but this is easier for now
resource "azurerm_managed_disk" "logdisk" {
count = length(var.instances)
name = "${azurerm_windows_virtual_machine.vm[count.index].name}-log-disk01"
location = azurerm_resource_group.resourcegroup[count.index].location
resource_group_name = azurerm_resource_group.resourcegroup[count.index].name
storage_account_type = "Premium_LRS"
zones = [var.instancezone]
create_option = "Empty"
disk_size_gb = 500
tags = var.tags
}
resource "azurerm_virtual_machine_data_disk_attachment" "logdisk_attach" {
count = length(var.instances)
managed_disk_id = azurerm_managed_disk.logdisk[count.index].id
virtual_machine_id = azurerm_windows_virtual_machine.vm[count.index].id
lun = 2
caching = "ReadWrite"
}
# configure the SQL side of the deployment
resource "azurerm_mssql_virtual_machine" "sqlvm" {
count = length(var.instances)
virtual_machine_id = azurerm_windows_virtual_machine.vm[count.index].id
sql_license_type = "PAYG"
r_services_enabled = true
sql_connectivity_port = 1433
sql_connectivity_type = "PRIVATE"
sql_connectivity_update_username = var.sqladmin
sql_connectivity_update_password = data.azurerm_key_vault_secret.sqladminpwd.value
#The storage_configuration block supports the following:
storage_configuration {
disk_type = "NEW" # (Required) The type of disk configuration to apply to the SQL Server. Valid values include NEW, EXTEND, or ADD.
storage_workload_type = "OLTP" # (Required) The type of storage workload. Valid values include GENERAL, OLTP, or DW.
# The storage_settings block supports the following:
data_settings {
default_file_path = var.sqldatafilepath # (Required) The SQL Server default path
luns = [azurerm_virtual_machine_data_disk_attachment.datadisk_attach[count.index].lun]
}
log_settings {
default_file_path = var.sqllogfilepath # (Required) The SQL Server default path
luns = [azurerm_virtual_machine_data_disk_attachment.logdisk_attach[count.index].lun] # (Required) A list of Logical Unit Numbers for the disks.
}
# temp_db_settings {
# default_file_path = var.sqltempdbfilepath #- (Required) The SQL Server default path
# luns = [3] #- (Required) A list of Logical Unit Numbers for the disks.
# }
}
}
Solution 2:[2]
On the contrary to the answer above, the proposed code does not solve the problem. The problem is:
There´s no clear format on how to pass the default path to the terraform sql resource.
resource "azurerm_mssql_virtual_machine" "sqlserver" {
virtual_machine_id = azurerm_windows_virtual_machine.win.id
sql_license_type = "PAYG"
r_services_enabled = true
auto_patching {
day_of_week = "Sunday"
maintenance_window_duration_in_minutes = 60
maintenance_window_starting_hour = 2
}
storage_configuration {
disk_type = "NEW"
storage_workload_type = "OLTP"
data_settings {
default_file_path = "D:\\Data"
luns = [0]
}
log_settings {
default_file_path = "E:\\log"
luns = [1]
}
temp_db_settings {
default_file_path = "F:\\bin"
luns = [2]
}
}
As you see here, I´m trying to define the correct path setting. But it´s not working.
The target is to attach the disks, and format them so that the SQL resource can take control of the path/disk.
Solution 3:[3]
You need use letter of temporary drive D: and omit E: because it's DVD drive. You need to do it like this:
storage_configuration {
disk_type = "NEW"
storage_workload_type = "OLTP"
data_settings {
default_file_path = "F:\\Data"
luns = [0]
}
log_settings {
default_file_path = "G:\\Log"
luns = [1]
}
temp_db_settings {
default_file_path = "D:\\TempDb"
luns = []
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | K. R. Angeles |
| Solution 2 | ouflak |
| Solution 3 | Mieszko |