Invalid Sql Storage Settings Extend Payload error when storage settings are not changed for `azurerm_mssql_virtual_machine`

msl0 commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Community Note

Please vote on this issue by adding a :thumbsup: 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.4.6

AzureRM Provider Version

3.54.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
  }

  storage_configuration {
    disk_type             = "NEW"
    storage_workload_type = "OLTP"

    data_settings {
      default_file_path = "${local.data_disks["data"].letter}:\\data"
      luns              = [azurerm_virtual_machine_data_disk_attachment.data_disk["data"].lun]

    }
    log_settings {
      default_file_path = "${local.data_disks["log"].letter}:\\log"
      luns              = [azurerm_virtual_machine_data_disk_attachment.data_disk["log"].lun]
    }

    temp_db_settings {
      default_file_path = "D:\\tempDb"
      luns              = []
      data_file_count   = var.temp_db_data_file_count
    }
  }

  lifecycle {
    ignore_changes = [
      storage_configuration,
      sql_instance,
    ]
  }

  depends_on = [
    azurerm_virtual_machine_data_disk_attachment.data_disk
  ]

  tags = local.tags
}

Debug Output/Panic Output

# module.sql-server.azurerm_mssql_virtual_machine.db will be updated in-place
  ~ resource "azurerm_mssql_virtual_machine" "db" {
        id                               = "xxx"
      ~ sql_connectivity_port            = 0 -> 1433
      + sql_connectivity_type            = "PRIVATE"
        # (5 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

Plan: 1 to add, 1 to change, 0 to destroy.
module.sql-server.azurerm_mssql_virtual_machine.db: Modifying... [id=xxx]
╷
│ Error: creating Sql Virtual Machine (Subscription: "xxx"
│ Resource Group Name: "xxx"
│ Sql Virtual Machine Name: "xxx"): performing CreateOrUpdate: sqlvirtualmachines.SqlVirtualMachinesClient#CreateOrUpdate: Failure sending request: StatusCode=0 -- Original Error: Code="InvalidExtendPayload" Message="Invalid Sql Storage Settings Extend Payload. Only support extend one drive at a time."
│ 
│   with module.sql-server.azurerm_mssql_virtual_machine.db,
│   on .terraform/modules/sql-server/vm-windows-sql-server/mssql_server.tf line 1, in resource "azurerm_mssql_virtual_machine" "db":
│    1: resource "azurerm_mssql_virtual_machine" "db" {
│ 
╵
##[error]Script failed with exit code: 1

Expected Behaviour

Changing the database connection port should not cause a problem with the storage settings when storage_configuration is included in ignore_changes list. terraform apply command should be successful

Actual Behaviour

terraform apply command fails with following error related to mssql storage settings when not modifed and storage settings block has been added to ignore_changes. I noticed similar situation only when resource tags are modified.

Sql Virtual Machine Name: "xxx"): performing CreateOrUpdate: sqlvirtualmachines.SqlVirtualMachinesClient#CreateOrUpdate: Failure sending request: StatusCode=0 -- Original Error: Code="InvalidExtendPayload" Message="Invalid Sql Storage Settings Extend Payload. Only support extend one drive at a time."

Steps to Reproduce

Create MSSQL VM resource without sql_connectivity_port specified
Set sql_connectivity_port using default value (1433) and rerun terraform apply

Important Factoids

No response

References

https://learn.microsoft.com/en-us/rest/api/sqlvm/2021-11-01-preview/sql-virtual-machines/create-or-update?tabs=HTTP https://learn.microsoft.com/en-us/rest/api/sqlvm/2021-11-01-preview/sql-virtual-machines/update?tabs=HTTP

msl0 commented 1 year ago

I did some additional research on this issue. I noticed that when I'm modifying tags for azurerm_mssql_virtual_machine then Terraform sends the whole object to API, not only tags (changed part of the object).

PUT /subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/xxx?api-version=2022-02-01 HTTP/1.1
Host: management.azure.com
User-Agent: Go/go1.19.3 (amd64-windows) go-autorest/v14.2.1 hashicorp/go-azure-sdk/sqlvirtualmachines/2022-02-01 HashiCorp Terraform/1.4.4 (+https://www.terraform.io) Terraform Plugin SDK/2.10.1 terraform-provider-azurerm/dev pid-...
Content-Length: 1146
Content-Type: application/json; charset=utf-8
X-Ms-Correlation-Request-Id: ...
Accept-Encoding: gzip

{"location":"eastus","properties":{"autoBackupSettings":{"enable":false},"serverConfigurationsManagementSettings":{"additionalFeaturesServerConfigurations":{"isRServicesEnabled":false},"sqlConnectivityUpdateSettings":{"connectivityType":"","port":0,"sqlAuthUpdatePassword":"xxx","sqlAuthUpdateUserName":"xxx"},"sqlInstanceSettings":{}},"sqlManagement":"Full","sqlServerLicenseType":"PAYG","storageConfigurationSettings":{"diskConfigurationType":"EXTEND","sqlSystemDbOnDataDisk":false,"sqlTempDbSettings":{"dataFileCount":0,"dataFileSize":0,"dataGrowth":0,"defaultFilePath":"","logFileSize":0,"logGrowth":0,"luns":[]},"storageWorkloadType":""},"virtualMachineResourceId":"/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Compute/virtualMachines/xxx"},"tags":{"tag":"value"}}

So Terraform uses PUT https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/{sqlVirtualMachineName}?api-version=2021-11-01-preview endpoint There is also PATCH https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/{sqlVirtualMachineName}?api-version=2021-11-01-preview endpoint. Maybe this can be used by provider to solve this issue...

myc2h6o commented 1 year ago

Hi @msl0 thanks for opening the issue! The PATCH of this resource only supports patching tags, thus we may not be able to use this workaround. However, the issue here is indeed a problem, I've done some tests and find out it seems feasible to remove the storageConfiguration from PUT request when it is an update if the value is not changed (including marked as ignore_changes), which will not touch the value for storageConfiguration on service side while updating other properties. I'll take a further look to see if we can make this change and add some test cases to verify it.

myc2h6o commented 1 year ago

@msl0 I've taken a further look, the property storageConfiguration from the API does not represent the current status. Instead, it stores the last operation detail, which does not make sense. There is an existing issue https://github.com/Azure/azure-sdk-for-go/issues/18434 opened at Azure. As for the issue within the provider, I think we'd better not touch the code until we got some response from API team regarding to how this property will be in the future.

As for the workaround, you can remove the whole storage_configuration block from the config as well as removing it form the ignore_changes array, thus it will not show up on the subsequent API requests.

hashicorp / terraform-provider-azurerm