terraform-provider-azurerm: Importing mssql failover group secondary database is destructive

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 0.12.26 AzureRM provider 2.51.0

Affected Resource(s)

• azurerm_mssql_database
• azurerm_sql_failover_group

Terraform Configuration Files

Example Failover Group to reproduce problem:

For reproduction purposes, this terraform will create a failover group that will illustrate the problem:

variable instance_name { 
    type = string
    default = "test-mssql-db" 
}
variable resource_group { 
    type = string
    default = "test-mssql-db-rg" 
}
variable db_name { 
    type = string
    default = "test-db"
}
variable location { 
    type = string 
    default = "eastus2"
}
variable labels { 
    type = map
    default = {} 
}
variable sku_name { 
    type = string 
    default = "GP_Gen5_2"
}
variable max_storage_gb { 
    type = number 
    default = 10
}

provider "azurerm" {
  version = "~> 2.51.0"
  features {}
}

resource "azurerm_resource_group" "azure-sql-fog" {
  name     = var.resource_group
  location = var.location
  tags     = var.labels
}

resource "random_string" "username" {
  length = 16
  special = false
  number = false
}

resource "random_password" "password" {
  length = 64
  override_special = "~_-."
  min_upper = 2
  min_lower = 2
  min_special = 2
}

resource "azurerm_sql_server" "primary_azure_sql_db_server" {
  depends_on = [ azurerm_resource_group.azure-sql-fog ]
  name                         = format("%s-primary", var.instance_name)
  resource_group_name          = var.resource_group
  location                     = var.location
  version                      = "12.0"
  administrator_login          = random_string.username.result
  administrator_login_password = random_password.password.result
  tags = var.labels
}

locals {
  default_pair = {
    // https://docs.microsoft.com/en-us/azure/best-practices-availability-paired-regions
    "eastasia" = "southeastasia"
    "southeastasia" = "eastasia"
    "centralus" = "eastus2"
    "eastus" = "westus"
    "eastus2" = "centralus"
    "westus" = "eastus"
    "northcentralus" = "southcentralus"
    "southcentralus" = "northcentralus"
    "northeurope" = "westeurope"
    "westeurope" = "northeurope"
    "japanwest" = "japaneast"
    "japaneast" = "japanwest"
    "brazilsouth" = "southcentralus"
    "australiaeast" = "australiasoutheast"
    "australiasoutheast" = "australiaeast"
    "australiacentral" = "australiacentral2"
    "australiacentral2" = "australiacentral"
    "southindia" = "centralindia"
    "centralindia" = "southindia"
    "westindia" = "southindia"
    "canadacentral" = "canadaeast"
    "canadaeast" = "canadacentral"
    "uksouth" = "ukwest"
    "ukwest" = "uksouth"
    "westcentralus" = "westus2"
    "westus2" = "westcentralus"
    "koreacentral" = "koreasouth"
    "koreasouth" = "koreacentral"
    "francecentral" = "francesouth"
    "francesouth" = "francecentral"
    "uaenorth" = "uaecentral"
    "uaecentral" = "uaenorth"
    "southafricanorth" = "southafricawest" 
    "southafricawest" = "southafricanorth"
    "germanycentral" = "germanynortheast"
    "germanynortheast" = "germanycentral"
  }   
}

resource "azurerm_sql_server" "secondary_sql_db_server" {
  depends_on = [ azurerm_resource_group.azure-sql-fog ]
  name                         = format("%s-secondary", var.instance_name)
  resource_group_name          = var.resource_group
  location                     = local.default_pair[var.location]
  version                      = "12.0"
  administrator_login          = random_string.username.result
  administrator_login_password = random_password.password.result
  tags                         = var.labels
}

resource "azurerm_mssql_database" "azure_sql_db" {
  name                = var.db_name
  server_id           = azurerm_sql_server.primary_azure_sql_db_server.id
  sku_name            = var.sku_name
  max_size_gb         = var.max_storage_gb
  tags                = var.labels
}

resource "azurerm_sql_failover_group" "failover_group" {
  depends_on = [ azurerm_resource_group.azure-sql-fog ]
  name                = var.instance_name
  resource_group_name = var.resource_group
  server_name         = azurerm_sql_server.primary_azure_sql_db_server.name
  databases           = [azurerm_mssql_database.azure_sql_db.id]
  partner_servers {
    id = azurerm_sql_server.secondary_sql_db_server.id
  }

  read_write_endpoint_failover_policy {
    mode          = "Automatic"
    grace_minutes = 5
  }
}

output primary_server_name { value = azurerm_sql_server.primary_azure_sql_db_server.name }
output secondary_server_name { value = azurerm_sql_server.secondary_sql_db_server.name }
output failover_group_name { value = azurerm_sql_failover_group.failover_group.name }
output resource_group { value = var.resource_group }
output db_name { value = azurerm_mssql_database.azure_sql_db.name }

Terraform to import resulting failover group

provider.tf

provider "azurerm" {
  version = "~> 2.51.0"
  features {}
}

main.tf

resource "azurerm_mssql_database" "primary_db" {
}

resource "azurerm_mssql_database" "secondary_db" {
}

resource "azurerm_sql_failover_group" "failover_group" {
}

Debug Output

Panic Output

Expected Behaviour

Once terraform import for the three resources is complete and the resulting terraform is cleaned terraform apply (with an update to the tags) should result in 0 to add, 1 to change, 0 to destroy

Actual Behaviour

If the secondary db has a create_mode of Secondary, the db is destroyed and recreated.

if the secondary db does not have a create_mode of Secondary, the apply fails with

Error: failure in issuing create/update request for SQL Database "test-db" Blob Auditing Policies(SQL Server "test-mssql-db-secondary"/ Resource Group "test-mssql-db-rg"): sql.ExtendedDatabaseBlobAuditingPoliciesClient#CreateOrUpdate: Failure responding to request: StatusCode=400 -- Original Error: autorest/azure: Service returned an error. Status=400 Code="BlobAuditingIsNotSupportedOnGeoDr" Message="Blob auditing can be configured on primary databases only."bash

Steps to Reproduce

Create an example failover group

Use the Example Failover Group to reproduce problem segment above to create a failover group and get the Azure ID’s for the resources

terraform init
terraform apply
export PRIMARY_DB_ID=$(az sql failover-group show --name $(terraform output failover_group_name | tr -d '\n\r') --server $(terraform output primary_server_name | tr -d '\n\r') --resource-group $(terraform output resource_group | tr -d '\n\r') --query databases[0] -o tsv)
export SECONDARY_DB_ID=$(az sql failover-group show --name $(terraform output failover_group_name | tr -d '\n\r') --server $(terraform output secondary_server_name | tr -d '\n\r') --resource-group $(terraform output resource_group | tr -d '\n\r') --query databases[0] -o tsv)
export FOG_ID=$(az sql failover-group show --name $(terraform output failover_group_name | tr -d '\n\r') --server $(terraform output primary_server_name | tr -d '\n\r') --resource-group $(terraform output resource_group | tr -d '\n\r') --query id -o tsv)

Import the failover group and db’s

In a new directory, put provider.tf and main.tf files from above, import the db’s and failover group, and use show to get the updated main.tf

terraform init
terraform import azurerm_mssql_database.primary_db ${PRIMARY_DB_ID}
terraform import azurerm_mssql_database.secondary_db ${SECONDARY_DB_ID}
terraform import azurerm_sql_failover_group.failover_group ${FOG_ID}
mv main.tf main.tf.orig
terraform show -no-color > main.tf

Clean out the new main.tf

Some values are readonly and can’t be applied.

Remove or comment out the following from main.tf

• azurerm_mssql_database.primary_db.id
• azurerm_mssql_database.primary_db.long_term_retention_policy
• azurerm_mssql_database.secondary_db.id
• azurerm_mssql_database.secondary_db.long_term_retention_policy
• azurerm_sql_failover_group.failover_group.id
• azurerm_sql_failover_group.failover_group.location
• azurerm_sql_failover_group.failover_group.role
• azurerm_sql_failover_group.failover_group.partner_servers.location
• azurerm_sql_failover_group.failover_group.partner_servers.role

Attempt to apply

terraform apply

Should fail with

Error: failure in issuing create/update request for SQL Database "test-db" Blob Auditing Policies(SQL Server "test-mssql-db-secondary"/ Resource Group "test-mssql-db-rg"): sql.ExtendedDatabaseBlobAuditingPoliciesClient#CreateOrUpdate: Failure responding to request: StatusCode=400 -- Original Error: autorest/azure: Service returned an error. Status=400 Code="BlobAuditingIsNotSupportedOnGeoDr" Message="Blob auditing can be configured on primary databases only."

Change create_mode to Secondary

Add the following to azurerm_mssql_database.secondary_db:

    create_mode = "Secondary"

Run terraform plan and it’ll want to destroy and recreate the db, which is very unwanted.

terraform plan
Refreshing Terraform state in-memory prior to plan...
The refreshed state will be used to calculate this plan, but will not be
persisted to local or remote state storage.

azurerm_sql_failover_group.failover_group: Refreshing state... [id=/subscriptions/899bf076-632b-4143-b015-43da8179e53f/resourceGroups/test-mssql-db-rg/providers/Microsoft.Sql/servers/test-mssql-db-primary/failoverGroups/test-mssql-db]
azurerm_mssql_database.primary_db: Refreshing state... [id=/subscriptions/899bf076-632b-4143-b015-43da8179e53f/resourceGroups/test-mssql-db-rg/providers/Microsoft.Sql/servers/test-mssql-db-primary/databases/test-db]
azurerm_mssql_database.secondary_db: Refreshing state... [id=/subscriptions/899bf076-632b-4143-b015-43da8179e53f/resourceGroups/test-mssql-db-rg/providers/Microsoft.Sql/servers/test-mssql-db-secondary/databases/test-db]

------------------------------------------------------------------------

An execution plan has been generated and is shown below.
Resource actions are indicated with the following symbols:
-/+ destroy and then create replacement

Terraform will perform the following actions:

  # azurerm_mssql_database.secondary_db must be replaced
-/+ resource "azurerm_mssql_database" "secondary_db" {
        auto_pause_delay_in_minutes = 0
        collation                   = "SQL_Latin1_General_CP1_CI_AS"
      + create_mode                 = "Secondary" # forces replacement
      + creation_source_database_id = (known after apply)
        extended_auditing_policy    = []
      ~ id                          = "/subscriptions/899bf076-632b-4143-b015-43da8179e53f/resourceGroups/test-mssql-db-rg/providers/Microsoft.Sql/servers/test-mssql-db-secondary/databases/test-db" -> (known after apply)
        license_type                = "LicenseIncluded"
        max_size_gb                 = 10
        min_capacity                = 0
        name                        = "test-db"
        read_replica_count          = 0
        read_scale                  = false
      + restore_point_in_time       = (known after apply)
      + sample_name                 = (known after apply)
        server_id                   = "/subscriptions/899bf076-632b-4143-b015-43da8179e53f/resourceGroups/test-mssql-db-rg/providers/Microsoft.Sql/servers/test-mssql-db-secondary"
        sku_name                    = "GP_Gen5_2"
        storage_account_type        = "GRS"
        tags                        = {
            "creator" = "eb"
        }
        zone_redundant              = false

      ~ long_term_retention_policy {
          ~ monthly_retention = "PT0S" -> (known after apply)
          ~ week_of_year      = 0 -> (known after apply)
          ~ weekly_retention  = "PT0S" -> (known after apply)
          ~ yearly_retention  = "PT0S" -> (known after apply)
        }

        short_term_retention_policy {
            retention_days = 7
        }

      ~ threat_detection_policy {
          - disabled_alerts      = [] -> null
            email_account_admins = "Disabled"
          - email_addresses      = [] -> null
            retention_days       = 0
            state                = "Disabled"
          + use_server_default   = "Disabled"
        }

        timeouts {}
    }

Plan: 1 to add, 0 to change, 1 to destroy.

Warning: "extended_auditing_policy": [DEPRECATED] the `extended_auditing_policy` block has been moved to `azurerm_mssql_server_extended_auditing_policy` and `azurerm_mssql_database_extended_auditing_policy`. This block will be removed in version 3.0 of the provider.

  on main.tf line 2, in resource "azurerm_mssql_database" "primary_db":
   2: resource "azurerm_mssql_database" "primary_db" {

(and one more similar warning elsewhere)


------------------------------------------------------------------------

Note: You didn't specify an "-out" parameter to save this plan, so Terraform
can't guarantee that exactly these actions will be performed if
"terraform apply" is subsequently run.

Important Factoids

We have a few customers with many failover groups they would like to assume control over and this behavior is preventing the secondary db from being cleanly imported.

References

Thought the create_mode = "Secondary" was a fix, but since it results in a re-creation of the secondary db, its not.

• https://github.com/terraform-providers/terraform-provider-azurerm/issues/8892