terraform plan wants to delete and recreate my sql database

Question

I have a Azure SQL Server and database managed by terraform. I have updated by terraform scripts to add a VM.

When I generate my Plan terraform wants to destroy my database and server and recreate them. I do not want this to happen! The terraform state is stored in a storage account.

I'd like to understand why terraform wants to do this. Is there a mechanism to understand why terraform has made this decision?

Its possible a 3rd party has circumvented process and made changes (such as changing the admin password of the database).

According to the source here: https://github.com/hashicorp/terraform-provider-azurerm/blob/b0c897055329438be6a3a159f6ffac4e1ce958f2/internal/services/sql/sql_server_resource.go

The only things that force new, is a name change, a version change or a admin login name. Non of which appear to have changed!

Here is a subset of the generated plan. Apologies for the formating

  # module.tenant_infrastructure.azurerm_mssql_database.tenant_sqldb will be created
  + resource "azurerm_mssql_database" "tenant_sqldb" {
      + auto_pause_delay_in_minutes = (known after apply)
      + collation                   = (known after apply)
      + create_mode                 = "Default"
      + creation_source_database_id = (known after apply)
      + extended_auditing_policy    = (known after apply)
      + geo_backup_enabled          = true
      + id                          = (known after apply)
      + license_type                = (known after apply)
      + max_size_gb                 = 250
      + min_capacity                = (known after apply)
      + name                        = "sqldb-productname-tenant2"
      + read_replica_count          = (known after apply)
      + read_scale                  = (known after apply)
      + restore_point_in_time       = (known after apply)
      + sample_name                 = (known after apply)
      + server_id                   = (known after apply)
      + sku_name                    = "S1"
      + storage_account_type        = "GRS"
      + tags                        = {
          + "Application" = "productname"
          + "CustomerId"  = "1"
          + "Environment" = "prod"
          + "Tenant"      = "2"
        }
      + zone_redundant              = (known after apply)

      + long_term_retention_policy {
          + monthly_retention = (known after apply)
          + week_of_year      = (known after apply)
          + weekly_retention  = (known after apply)
          + yearly_retention  = (known after apply)
        }

      + short_term_retention_policy {
          + retention_days = (known after apply)
        }

      + threat_detection_policy {
          + disabled_alerts            = (known after apply)
          + email_account_admins       = (known after apply)
          + email_addresses            = (known after apply)
          + retention_days             = (known after apply)
          + state                      = (known after apply)
          + storage_account_access_key = (sensitive value)
          + storage_endpoint           = (known after apply)
          + use_server_default         = (known after apply)
        }

      + timeouts {
          + create = "60m"
          + read   = "60m"
          + update = "60m"
        }
    }

  # module.tenant_infrastructure.azurerm_mssql_server.tenant_sql will be created
  + resource "azurerm_mssql_server" "tenant_sql" {
      + administrator_login               = "companynameAdmin"
      + administrator_login_password      = (sensitive value)
      + connection_policy                 = "Default"
      + extended_auditing_policy          = (known after apply)
      + fully_qualified_domain_name       = (known after apply)
      + id                                = (known after apply)
      + location                          = "uksouth"
      + name                              = "sql-productname-tenant2-prod"
      + primary_user_assigned_identity_id = (known after apply)
      + public_network_access_enabled     = true
      + resource_group_name               = "rg-productname-tenant2-001"
      + restorable_dropped_database_ids   = (known after apply)
      + tags                              = {
          + "Application" = "productname"
          + "CustomerId"  = "1"
          + "Environment" = "prod"
          + "Tenant"      = "2"
        }
      + version                           = "12.0"

      + azuread_administrator {
          + azuread_authentication_only = (known after apply)
          + login_username              = "companyname.productname.TENANT.DB_ADMIN"
          + object_id                   = "1e3f10f4-ade1-44cc-80a7-ea2191e6f5fd"
          + tenant_id                   = "yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy"
        }

      + timeouts {
          + create = "60m"
          + read   = "60m"
          + update = "60m"
        }
    }

  # module.tenant_infrastructure.azurerm_mssql_virtual_network_rule.tenant_sql_subnet_rule_management_access will be created
  + resource "azurerm_mssql_virtual_network_rule" "tenant_sql_subnet_rule_management_access" {
      + id                                   = (known after apply)
      + ignore_missing_vnet_service_endpoint = true
      + name                                 = "allowManagementSubnetAccess"
      + server_id                            = (known after apply)
      + subnet_id                            = "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/rg-productname-shared-001/providers/Microsoft.Network/virtualNetworks/vnet-productname-shared-001/subnets/snet-productname-shared-management-001"
    }

  # module.tenant_infrastructure.azurerm_mssql_virtual_network_rule.tenant_sql_subnet_rule_tenant_access will be created
  + resource "azurerm_mssql_virtual_network_rule" "tenant_sql_subnet_rule_tenant_access" {
      + id                                   = (known after apply)
      + ignore_missing_vnet_service_endpoint = false
      + name                                 = "allowTenantSubnetAccess"
      + server_id                            = (known after apply)
      + subnet_id                            = "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/rg-productname-tenant2-001/providers/Microsoft.Network/virtualNetworks/vnet-productname-tenant2-001/subnets/snet-productname-tenant2-001"
    }



  # module.tenant_infrastructure.azurerm_sql_active_directory_administrator.tenant_sql_aad_admin will be destroyed
  - resource "azurerm_sql_active_directory_administrator" "tenant_sql_aad_admin" {
      - azuread_authentication_only = false -> null
      - id                          = "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/rg-productname-tenant2-001/providers/Microsoft.Sql/servers/sql-productname-tenant2-prod/administrators/activeDirectory" -> null
      - login                       = "companyname.productname.TENANT.DB_ADMIN" -> null
      - object_id                   = "1e3f10f4-ade1-44cc-80a7-ea2191e6f5fd" -> null
      - resource_group_name         = "rg-productname-tenant2-001" -> null
      - server_name                 = "sql-productname-tenant2-prod" -> null
      - tenant_id                   = "yyyyyyy-yyyy-yyyy-yyyy-yyyyyyyy" -> null
    }

  # module.tenant_infrastructure.azurerm_sql_database.tenant_sqldb will be destroyed
  - resource "azurerm_sql_database" "tenant_sqldb" {
      - collation                        = "SQL_Latin1_General_CP1_CI_AS" -> null
      - create_mode                      = "Default" -> null
      - creation_date                    = "2021-12-08T23:08:01.023Z" -> null
      - default_secondary_location       = "UK West" -> null
      - edition                          = "Standard" -> null
      - extended_auditing_policy         = [] -> null
      - id                               = "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/rg-productname-tenant2-001/providers/Microsoft.Sql/servers/sql-productname-tenant2-prod/databases/sqldb-productname-tenant2" -> null
      - location                         = "uksouth" -> null
      - max_size_bytes                   = "268435456000" -> null
      - name                             = "sqldb-productname-tenant2" -> null
      - read_scale                       = false -> null
      - requested_service_objective_id   = "1b1ebd4d-d903-4baa-97f9-4ea675f5e928" -> null
      - requested_service_objective_name = "S1" -> null
      - resource_group_name              = "rg-productname-tenant2-001" -> null
      - server_name                      = "sql-productname-tenant2-prod" -> null
      - tags                             = {
          - "Application" = "productname"
          - "CustomerId"  = "1"
          - "Environment" = "prod"
          - "Tenant"      = "2"
        } -> null
      - zone_redundant                   = false -> null

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

  # module.tenant_infrastructure.azurerm_sql_server.tenant_sql will be destroyed
  - resource "azurerm_sql_server" "tenant_sql" {
      - administrator_login          = "companynameAdmin" -> null
      - administrator_login_password = (sensitive value)
      - connection_policy            = "Default" -> null
      - extended_auditing_policy     = [] -> null
      - fully_qualified_domain_name  = "sql-productname-tenant2-prod.database.windows.net" -> null
      - id                           = "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/rg-productname-tenant2-001/providers/Microsoft.Sql/servers/sql-productname-tenant2-prod" -> null
      - location                     = "uksouth" -> null
      - name                         = "sql-productname-tenant2-prod" -> null
      - resource_group_name          = "rg-productname-tenant2-001" -> null
      - tags                         = {
          - "Application" = "productname"
          - "CustomerId"  = "1"
          - "Environment" = "prod"
          - "Tenant"      = "2"
        } -> null
      - version                      = "12.0" -> null

      - threat_detection_policy {
          - disabled_alerts      = [
              - "",
            ] -> null
          - email_account_admins = false -> null
          - email_addresses      = [
              - "",
            ] -> null
          - retention_days       = 0 -> null
          - state                = "Disabled" -> null
        }
    }

  # module.tenant_infrastructure.azurerm_sql_virtual_network_rule.tenant_sql_subnet_rule_management_access will be destroyed
  - resource "azurerm_sql_virtual_network_rule" "tenant_sql_subnet_rule_management_access" {
      - id                                   = "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/rg-productname-tenant2-001/providers/Microsoft.Sql/servers/sql-productname-tenant2-prod/virtualNetworkRules/allowManagementSubnetAccess" -> null
      - ignore_missing_vnet_service_endpoint = true -> null
      - name                                 = "allowManagementSubnetAccess" -> null
      - resource_group_name                  = "rg-productname-tenant2-001" -> null
      - server_name                          = "sql-productname-tenant2-prod" -> null
      - subnet_id                            = "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/rg-productname-shared-001/providers/Microsoft.Network/virtualNetworks/vnet-productname-shared-001/subnets/snet-productname-shared-management-001" -> null
    }

The module code is below.

Note that I subsequently added a lifecycle block to the SQL Database which didn't seem to prevent the plan from trying to delete the resources!

resource "azurerm_mssql_server" "tenant_sql" {
  name                         = "sql-product-tenant${var.tenant}-${var.environment}"
  resource_group_name          = azurerm_resource_group.tenant_rg.name
  location                     = var.location
  version                      = "12.0"
  administrator_login          = var.sql_server_admin_login
  administrator_login_password = random_password.sql_server_admin_password.result

   tags = {
        Application = "product"
      Tenant = var.tenant
      CustomerId = var.customer_id
      Environment = var.environment
    }

     timeouts {
    create = "60m"
    update = "60m"
    read ="60m"
  }

    azuread_administrator {
    login_username = "COMPANY.PRODUCT.TENANT.DB_ADMIN"
    object_id      = local.groups.COMPANY.PRODUCT.TENANT.DB_ADMIN
    tenant_id           = data.azurerm_client_config.current.tenant_id
  }

  lifecycle {
    prevent_destroy = true
    ignore_changes = ["administrator_login", "administrator_login_password"]
  }
}


resource "azurerm_mssql_virtual_network_rule" "tenant_sql_subnet_rule_tenant_access" {
  name                = "allowTenantSubnetAccess"
  server_id         = azurerm_mssql_server.tenant_sql.id
  subnet_id           = azurerm_subnet.tenant_subnet.id
}

resource "azurerm_mssql_virtual_network_rule" "tenant_sql_subnet_rule_management_access" {
  name                = "allowManagementSubnetAccess"
  server_id         = azurerm_mssql_server.tenant_sql.id
  subnet_id           = data.azurerm_subnet.shared_management_subnet.id
  ignore_missing_vnet_service_endpoint = true
}


resource "azurerm_mssql_database" "tenant_sqldb" {
  name                = "sqldb-product-tenant${var.tenant}"
  server_id         = azurerm_mssql_server.tenant_sql.id
  sku_name          = var.database_is_premium == true ? "P4" : "S1"
  create_mode                      = "Default"


max_size_gb     = 250 

   tags = {
        Application = "product"
      Tenant = var.tenant
      CustomerId = var.customer_id
      Environment = var.environment
    }

  timeouts {
    create = "60m"
    update = "60m"
    read ="60m"
  }
}

Answer 1

By inspecting the provided plan output with formatting in the third iteration of the question, I can see why the re-creation is being forced. The provider does not display any information about a value change forcing a re-creation, nor does it seem to display any real value changes at all. My next theory was that a resource was renamed in the config, and sure enough:

module.tenant_infrastructure.azurerm_sql_database.tenant_sqldb --> module.tenant_infrastructure.azurerm_mssql_database.tenant_sqldb

It may be frustrating to hear this, but debugging something like this really comes down to experience, and having observed it before.

You can fix this by modifying the state with simple terraform state commands within the Terraform CLI:

terraform state mv module.tenant_infrastructure.azurerm_sql_database.tenant_sqldb module.tenant_infrastructure.azurerm_mssql_database.tenant_sqldb
terraform state mv module.tenant_infrastructure.azurerm_sql_server.tenant_sql module.tenant_infrastructure.azurerm_mssql_server.tenant_sql
terraform state mv module.tenant_infrastructure.azurerm_sql_virtual_network_rule.tenant_sql_subnet_rule_management_access module.tenant_infrastructure.azurerm_mssql_virtual_network_rule.tenant_sql_subnet_rule_management_access

The module.tenant_infrastructure.azurerm_sql_database.tenant_sqldb seems to be replaced by the module.tenant_infrastructure.azurerm_mssql_virtual_network_rule.tenant_sql_subnet_rule_tenant_access. These are difference resources, and therefore this single destruction and creation seems to be intentional.

You mention in the comments that you are using ADO to execute TF, in which case you may need to modify the pipeline temporarily to update the state with these commands.

Additionally, if you are using Terraform 1.1 or later, then you can also request the module author to add the moved block to the resources to avoid this state issue with the renaming.

Answer 2

I got my configuration matching what's provisionned :

Now I do some update on azure portal, here adding a configuration value on an azure function :

I run terraform apply without touching the code :

That change is detected and so terraform will "fix" it to match the provided configuration (understand, removing that new configuration done manually).

If you wan't to keep that change, you must import it in your code.