azurerm_mssql_database does not allow to set backup_interval_in_hours to 0 for hyperscale DBs

[fotto]

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 comments along the lines of "+1", "me too" or "any updates", 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 and review the contribution guide to help.

Terraform Version

1.9.8

AzureRM Provider Version

4.6.0

Affected Resource(s)/Data Source(s)

azurerm_mssql_database

Terraform Configuration Files

resource "azurerm_mssql_database" "this" {
  name      = var.name
  server_id = var.server_id

  sku_name = "HS_S_Gen5_2"

  ...

  short_term_retention_policy {
    retention_days           = 7
    backup_interval_in_hours = 0 # <== only 12 or 24 are accepted
  }

Debug Output/Panic Output

╷
│ Error: expected short_term_retention_policy.0.backup_interval_in_hours to be one of [12 24], got 0
│ 
│   with module.pur_mssql_db[0].azurerm_mssql_database.this,
│   on .terraform/modules/pur_mssql_db/mssql_db_hs_s/main.tf line 20, in resource "azurerm_mssql_database" "this":
│   20:     backup_interval_in_hours = local.fixed_values.str_backup_interval_hours
│ 
╵

Expected Behaviour

provider should not enforce a value of "12" or "24" for HyperScale SKUs.

Rationale

If I set an allowed value, e.g.

short_term_retention_policy {
    retention_days           = 7
    backup_interval_in_hours = 24
  }

then the change gets applied - But the next run of "terrraform plan" will then show the following configuration drift:

      ~ short_term_retention_policy {
          ~ backup_interval_in_hours = 0 -> 24
            # (1 unchanged attribute hidden)
        }

Conclusion: the API sets the value internally to 0 for Hyperscale DBs.

This behavior has changed recently as it seems (within last few days?)

Actual Behaviour

No response

Steps to Reproduce

No response

Important Factoids

No response

References

No response

[fotto]

Workaround: for HyperScale DB set a value that gets accepted by provider plus a suitable ignore_changes setting, e.g.

resource "azurerm_mssql_database" "this" {
  name      = var.name
  server_id = var.server_id

  sku_name = "HS_S_Gen5_2"

  ...

  short_term_retention_policy {
    retention_days           = 7
    backup_interval_in_hours = 24  # only dummy. will internally be set to 0
  }

  ...

  lifecycle {
    ignore_changes = [ short_term_retention_policy[0].backup_interval_in_hours ]
  }

[sinbai]

Hi @fotto thanks for opening this issue. I assume that after PR is merged, for HyperScale DB , by not specifying a value of backup_interval_in_hours instead set it to 0. Could you please remove it and try again? Like this:

resource "azurerm_mssql_database" "this" {
  name      = var.name
  server_id = var.server_id

  sku_name = "HS_S_Gen5_2"

  ...

  short_term_retention_policy {
    retention_days           = 7
  }

[fotto]

Hi @sinbai , many thanks for that hint. I can confirm that it solves the issue:

• tested for update
• and for creation of new HyperScale DB

So, the issue boils down to a pending documentation update for azurerm_mssql_database.
I suggest to add something like this to the description of backup_interval_in_hours:

This attribute must not be set for Hyperscale DBs or for DBs running within a Hyperscale Elastic Pool