Terraform provider AzureRM: Azure SQL DB collation case sensitive – HashiCorp Help Center

Problem

When running a terraform plan, Terraform proposes to replace an azurerm_mssql_database resource because the value of the collation attribute in the configuration differs in case from the value stored in the state, even if the values are otherwise identical.

Terraform will perform the following actions:

  # azurerm_mssql_database.primary_db must be replaced
-/+ resource "azurerm_mssql_database" "primary_db" {
      ~ auto_pause_delay_in_minutes = 0 -> (known after apply)
      ~ collation                     = "SQL_Latin1_General_CP1_CI_AS" -> "SQL_LATIN1_GENERAL_CP1_CI_AS" # forces replacement
      # (18 other attributes unchanged)
    }

Cause

The Azure API for SQL Server treats the collation attribute as case-sensitive. According to the official Microsoft SQL Server documentation, collation settings include case sensitivity.

The options associated with a collation are case sensitivity, accent sensitivity, kana sensitivity, width sensitivity, and variation-selector sensitivity. SQL Server 2019 (15.x) introduces an additional option for UTF-8 encoding.

Because the provider reports the value returned by the API, any difference in casing between your configuration and the remote state will be detected by Terraform as drift, resulting in a plan to replace the resource.

Solutions

There are two primary approaches to resolve this issue.

Solution 1: Align Configuration with the Remote State

Update the collation attribute in your Terraform configuration to match the exact case-sensitive value that the Azure API returns and stores in the Terraform state. In the example above, you would change the configuration to use all uppercase letters.

resource "azurerm_mssql_database" "primary_db" {
  # ... other arguments
  collation = "SQL_LATIN1_GENERAL_CP1_CI_AS"
}

After updating the configuration, running terraform plan should report no changes.

Solution 2: Ignore Changes to the Collation Attribute

If you prefer to prevent Terraform from managing the collation attribute after its initial creation, you can use the lifecycle meta-argument to ignore changes.

Add a lifecycle block to the azurerm_mssql_database resource configuration and add collation to the ignore_changes list. This instructs Terraform to disregard any differences for this specific attribute between your configuration and the remote state.

resource "azurerm_mssql_database" "primary_db" {
  # ... other arguments
  collation = "SQL_Latin1_General_CP1_CI_AS"

  lifecycle {
    ignore_changes = [
      collation,
    ]
  }
}

This approach is useful if the casing is inconsistent or managed outside of Terraform.

Additional Information