Adding sql_instance with defaults in azurerm_mssql_virtual_machine resource recreates whole resource.

[msl0]

Is there an existing issue for this?

• I have searched the existing issues

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Terraform Version

1.3.6

AzureRM Provider Version

3.50.0

Affected Resource(s)/Data Source(s)

azurerm_mssql_virtual_machine

Terraform Configuration Files

resource "azurerm_mssql_virtual_machine" "db" {
  virtual_machine_id               = azurerm_windows_virtual_machine.vm.id
  sql_license_type                 = "PAYG"
  sql_connectivity_update_password = local.admin_password
  sql_connectivity_update_username = var.admin_username
  sql_connectivity_port            = 1433
  sql_connectivity_type            = "PRIVATE"

  sql_instance {
    max_dop              = var.max_dop
    max_server_memory_mb = var.max_server_memory_mb
  }
}

Debug Output/Panic Output

https://gist.github.com/msl0/ce8ec4bc8513cc1ef7132cd771e59033

Expected Behaviour

Adding sql_instance optional block without modifying default values for collation, instant_file_initialization_enabled and lock_pages_in_memory_enabled shouldn't cause whole resource recreation.

Actual Behaviour

Terraform try to recreate azurerm_mssql_virtual_machine resource when added sql_instance block to existing resource.
Adding sql_instance[0] to ignore_changes doesn't help.

  # azurerm_mssql_virtual_machine.db must be replaced
-/+ resource "azurerm_mssql_virtual_machine" "db" {
      ~ id                               = "xxx" -> (known after apply)
      - r_services_enabled               = false -> null
      ~ sql_connectivity_port            = 0 -> 1433
      ~ sql_connectivity_type            = "" -> "PRIVATE"
        # (4 unchanged attributes hidden)

      + sql_instance {
          + adhoc_workloads_optimization_enabled = false
          + collation                            = "SQL_Latin1_General_CP1_CI_AS" # forces replacement
          + instant_file_initialization_enabled  = false # forces replacement
          + lock_pages_in_memory_enabled         = false # forces replacement
          + max_dop                              = 0
          + max_server_memory_mb                 = 2147483647
          + min_server_memory_mb                 = 0
        }

      ~ storage_configuration {
          ~ disk_type                      = "EXTEND" -> "NEW"
          + storage_workload_type          = "OLTP"
            # (1 unchanged attribute hidden)

          + data_settings {
              + default_file_path = "F:\\data"
              + luns              = [
                  + 10,
                ]
            }

          + log_settings {
              + default_file_path = "G:\\log"
              + luns              = [
                  + 11,
                ]
            }

          + temp_db_settings {
              + data_file_count        = 8
              + data_file_growth_in_mb = 512
              + data_file_size_mb      = 256
              + default_file_path      = "D:\\tempDb"
              + log_file_growth_mb     = 512
              + log_file_size_mb       = 256
              + luns                   = []
            }
        }
    }

When I commented out sql_instance and ignore_changes block it doesn't return recreation

  # sql_instance {
  #   max_dop              = var.max_dop
  #   max_server_memory_mb = var.max_server_memory_mb
  # }

The interesting thing is that this sql_instance block is visible in the state file for this resource even though I didn't run the terraform apply command.
Not sure if it is important fact but when I run terraform plan with debug logging, I see that HTTP response body about azurerm_mssql_virtual_machine shows properties.serverConfigurationsManagementSettings.sqlInstanceSettings is empty
[DEBUG] provider.terraform-provider-azurerm_v3.50.0_x5: AzureRM Response for https://management.azure.com/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/xxx?%24expand=%2A&api-version=2022-02-01

{
// removed unnecessary parts of response for better readability
    "properties": {
        "virtualMachineResourceId": "xxxe",
        "provisioningState": "Succeeded",
        "sqlImageOffer": "SQL2019-WS2022",
        "sqlServerLicenseType": "PAYG",
        "sqlManagement": "Full",
        "sqlImageSku": "Developer",
        "autoPatchingSettings": {
            "enable": false
        },
        "autoBackupSettings": {
            "enable": false
        },
        "keyVaultCredentialSettings": {
            "enable": false
        },
        "serverConfigurationsManagementSettings": {
            "sqlConnectivityUpdateSettings": {},
            "sqlWorkloadTypeUpdateSettings": {},
            "sqlStorageUpdateSettings": {},
            "additionalFeaturesServerConfigurations": {},
            "sqlInstanceSettings": {}
        },
    },
    "location": "westeurope",
    "id": "xxx",
    "name": "xxx",
    "type": "Microsoft.SqlVirtualMachine/sqlVirtualMachines"
}

Steps to Reproduce

1. Create MSSQL VM without sql_instance block - terraform apply

data "azurerm_virtual_machine" "example" {
  name                = "example-vm"
  resource_group_name = "example-resources"
}

resource "azurerm_mssql_virtual_machine" "example" {
  virtual_machine_id               = data.azurerm_virtual_machine.example.id
  sql_license_type                 = "PAYG"
  r_services_enabled               = true
  sql_connectivity_port            = 1433
  sql_connectivity_type            = "PRIVATE"
  sql_connectivity_update_password = "test"
  sql_connectivity_update_username = "test"
}

2. Add sql_instance block with default values - terraform plan

resource "azurerm_mssql_virtual_machine" "example" {
  virtual_machine_id               = data.azurerm_virtual_machine.example.id
  sql_license_type                 = "PAYG"
  r_services_enabled               = true
  sql_connectivity_port            = 1433
  sql_connectivity_type            = "PRIVATE"
  sql_connectivity_update_password = "test"
  sql_connectivity_update_username = "test"

  sql_instance {
    collation                            = "SQL_Latin1_General_CP1_CI_AS"
    instant_file_initialization_enabled  = false
    lock_pages_in_memory_enabled         = false
  }
}

Important Factoids

No response

References

No response

[myc2h6o]

Hi @msl0 thanks for opening the issue! Unfortunately, this is by design in Terraform. When the block sql_instance is not specified, the default value of its nested property is not defined, thus when specifying it during an update will set its value from empty to its default value.

[msl0]

Hi @myc2h6o is there any option to create sql_instance block with default values in state file even if not defined in code?
I found workaround for my problem. I added sql_instance instead of sql_instance[0] to ignore_changes and now it works with existing resources

[myc2h6o]

Hi @msl0 currently due to the design limitation, adding the sql_instance when it's not there will cause the full resource to be recreated due to some immutable properties nested in this block. To workaround this, unfortunately you may need a direct REST API call like below to set the SqlInstanceSettings.Collation

PUT https://management.azure.com/subscriptions/.../resourceGroups/.../providers/Microsoft.SqlVirtualMachine/SqlVirtualMachines/...?api-version=2023-01-01-preview
{
    "properties": {
        "virtualMachineResourceId": "/subscriptions/.../resourceGroups/.../providers/Microsoft.Compute/virtualMachines/...",
        "provisioningState": "Succeeded",
        "sqlImageOffer": "SQL2017-WS2016",
        "sqlServerLicenseType": "PAYG",
        "sqlManagement": "Full",
        "leastPrivilegeMode": "Enabled",
        "sqlImageSku": "Developer",
        "enableAutomaticUpgrade": true,
        "ServerConfigurationsManagementSettings": {
            "SqlInstanceSettings": {
                "Collation": "SQL_Latin1_General_CP1_CI_AS"
            }
        }
    },
    "location": "eastus"
}

GET https://management.azure.com/subscriptions/.../resourceGroups/.../providers/Microsoft.SqlVirtualMachine/SqlVirtualMachines/...?api-version=2023-01-01-preview&$expand=*
will return the full properties