Can't define short_term_retention_policy on Hyperscale databases with azurerm_mssql_database

[devblackops]

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 (and AzureRM Provider) Version

Terraform v1.0.6
on windows_amd64
+ provider registry.terraform.io/hashicorp/azurerm v2.74.0

Affected Resource(s)

• azurerm_mssql_database

Terraform Configuration Files

module "naming" {
  source = "github.com/Azure/terraform-azurerm-naming?ref=64b9489"
}

resource "azurerm_resource_group" "hyperscale" {
  name     = "hyperscale"
  location = local.location
}

resource "random_password" "sql_admin_password" {
  length  = 24
  special = true
}

resource "azurerm_mssql_server" "hyperscale" {
  name                = module.naming.sql_server.name_unique
  resource_group_name = azurerm_resource_group.hyperscale.name
  location            = local.location

  identity {
    type = "SystemAssigned"
  }

  version                      = "12.0"
  connection_policy            = "Redirect"
  minimum_tls_version          = "1.2"
  administrator_login          = "sqladmin"
  administrator_login_password = random_password.sql_admin_password.result
}

resource "azurerm_mssql_database" "hyperscale" {
  name = "hyperscale"

  server_id = azurerm_mssql_server.hyperscale.id
  sku_name  = "HS_Gen5_2"

  short_term_retention_policy {
    retention_days = 7
  }
}

Debug Output

Panic Output

Expected Behavior

Hyperscale database is created successfully with the desired short_term_retention_policy.

Actual Behavior

Shortly after resource creation starts, the following error happens saying the short_term_retention_policy value is invalid (even though 7 is a valid value (1-7 days) according to the docs. The DB can be created if short_term_retention_policy is omitted but when adding it after the fact and running an apply again produces the same error.

...
azurerm_mssql_database.hyperscale: Still creating... [2m10s elapsed]
azurerm_mssql_database.hyperscale: Still creating... [2m20s elapsed]
azurerm_mssql_database.hyperscale: Still creating... [2m30s elapsed]

│ Error: issuing create/update request for Sql Server "xxxxx" (Database "hyperscale") Short Term Retention Policies (Resource Group "hyperscale"): sql.BackupShortTermRetentionPoliciesClient#CreateOrUpdate: Failure sending request: StatusCode=0 -- Original Error: Code="InvalidParameterValue" Message="Invalid value given for parameter properties. Specify a valid parameter value."
│
│   with azurerm_mssql_database.hyperscale,
│   on mssql_repro.tf line 31, in resource "azurerm_mssql_database" "hyperscale":
│   31: resource "azurerm_mssql_database" "hyperscale" {

Steps to Reproduce

1. terraform apply

Important Factoids

References

• 0000

[bdorplatt]

We came across this issue as well building a module for MS SQL databases that would cover all possible sku's. As a temporary workaround, we have used a dynamic block where if the retention_days is set to 0, the block won't be processed. Without the block, the Hyperscale retention days defaults to 7 days which is the max for Hyperscale and what we wanted for it anyway.

  dynamic "short_term_retention_policy" {
    for_each = var.retention_days != "0" ? [var.retention_days] : []
    content {
      retention_days       = var.retention_days
   }
  }