Azure SQL Database LTR policy not working as expected

sumeet15 commented 2 years 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.1.3

AzureRM Provider Version

2.99.0

Affected Resource(s)/Data Source(s)

azurerm_mssql_database

Terraform Configuration Files

~ long_term_retention_policy {
          ~ monthly_retention = "P6M" -> "PT0S"
          ~ weekly_retention  = "P1W" -> "P3M"
            # (2 unchanged attributes hidden)
        }

Debug Output/Panic Output

The LTR policy for the Azure SQL Database does not change even though in terraform output, it shows as "Modification Complete". The terraform state file gets updated properly, but not the actual resource:

            "long_term_retention_policy": [
              {
                "monthly_retention": "PT0S",
                "week_of_year": 0,
                "weekly_retention": "P3M",
                "yearly_retention": "PT0S"
              }
            ]

Expected Behaviour

The terraform state and actual resource should match for Azure SQL DB LTR policy value.

Actual Behaviour

The terraform state file has the correctly values in Long term retention policy, but the actual resource shows the old values and the next time, when terraform apply is run on the same resource, it again goes on to modify the resource since the values in resource and tfstate file differ and it tries to bring it in sync.

Steps to Reproduce

No response

Important Factoids

No response

References

No response

sinbai commented 2 years ago

@sumeet15 thank you for opening this issue here. Could you please provide the terraform config to help repro/troubleshooting? It is worth mentioning that if ledger_enabled is set to true (means all tables in the database are ledger tables), the long_term_retention_policy will not be updated.

sumeet15 commented 2 years ago

Please find below is the terraform config: terraform { required_providers { azurerm = { source = "hashicorp/azurerm" version = "2.99.0" } } } provider "azurerm" { features { } }

resource "azurerm_mssql_database" "mssql_database" { name = var.mssql_database_name server_id = azurerm_mssql_server.mssql_server.id collation = "SQL_Latin1_General_CP1_CI_AS license_type = "LicenseIncluded"

max_size_gb = 100 read_scale = var.read_scale sku_name = "GP_Gen5_4" zone_redundant = true storage_account_type = "LRS"

short_term_retention_policy { retention_days = var.retention_days }

long_term_retention_policy { weekly_retention = "P3M" monthly_retention = "PT0S" }

threat_detection_policy { state = "Enabled" retention_days = xxxx storage_account_access_key = xxxxxxx storage_endpoint = xxxxxxx } } Ledger is not enabled.

sinbai commented 2 years ago

I could not reproduce the issue using the following full terraform configuration and steps to reproduce. Could you reproduce with it? Could you provide step-by-step repro steps to help repro/troubleshoot?

Step1: Run terraform apply with the following tf configuration:

terraform {
required_providers {
    azurerm = {
      source = "hashicorp/azurerm"
      version = "2.99.0"
    }
  }
}

provider "azurerm" {
   features {
 }
}

resource "azurerm_resource_group" "test" {
  name     = "RG-mssql-sample"
  location = "eastus"
}

resource "azurerm_storage_account" "test" {
  name                     = "teststorageaccsample"
  resource_group_name      = azurerm_resource_group.test.name
  location                 = azurerm_resource_group.test.location
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

resource "azurerm_mssql_server" "test" {
  name                         = "sqlserver-sample"
  resource_group_name          = azurerm_resource_group.test.name
  location                     = azurerm_resource_group.test.location
  version                      = "12.0"
  administrator_login          = "mradministrator"
  administrator_login_password = "thisIsDog11"
}

resource "azurerm_mssql_database" "mssql_database" {
  name = "databasesample"
  server_id = azurerm_mssql_server.test.id
  collation = "SQL_Latin1_General_CP1_CI_AS"
  license_type = "LicenseIncluded"

  max_size_gb = 100
  read_scale = false
  sku_name = "GP_Gen5_4"
  zone_redundant = true
  storage_account_type = "LRS"

  short_term_retention_policy {
  retention_days = 3
}

long_term_retention_policy {
  weekly_retention = "P1W"
  monthly_retention = "P6M"
  week_of_year      = 1
}

 threat_detection_policy {
    retention_days             = 15
    state                      = "Enabled"
    storage_account_access_key = azurerm_storage_account.test.primary_access_key
    storage_endpoint           = azurerm_storage_account.test.primary_blob_endpoint
  }
}

Step 2: Change the weekly_retention to "P3M" and monthly_retention to "PT0S", run terraform apply.

long_term_retention_policy {
  weekly_retention = "P3M"
  monthly_retention = "PT0S"
  week_of_year      = 1
}

Actually: The result of calling Long Term Retention Policies List API is expected.

"properties": {
        "weeklyRetention": "P3M",
        "monthlyRetention": "PT0S",
        "yearlyRetention": "PT0S",
     ...

KezHalls commented 2 years ago

I have the same issue where the below is not reflecting any changes in state at all. long_term_retention_policy { weekly_retention = "P35D" monthly_retention = "P14W" yearly_retention = "P3Y"
week_of_year = 1
}

niklastanner commented 7 months ago

We've got the same issue using an SQL Database within an elastic pool:

long_term_retention_policy {
  weekly_retention          = "P6W"
  monthly_retention         = "P1Y"
  yearly_retention          = "P10Y"
  week_of_year              = 1
  immutable_backups_enabled = false
}

short_term_retention_policy {
  retention_days = 10
}

Since its creation this settings have not been changed. But every terraform plan shows the following:

~ long_term_retention_policy  {
  ~ monthly_retention : "PT0S" -> "P1Y"
  ~ monthly_retention : "PT0S" -> "P6W"
  ~ monthly_retention : "PT0S" -> "P10Y"
  ... 2 unchanged attributes hidden
  }

~ short_term_retention_policy {
  ~ retention_days: 7 -> 10
  ... 1 unchanged attributes hidden
  }

Setting this configuration manually within the azure portal is possible and does solve the issue, but this shouldn't be necessary.

hashicorp / terraform-provider-azurerm