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"
  }
}

```