Upgrade Terraform's Azure SQL Resource from `azurerm_sql_database` to `azurerm_mssql_database`

Question

A while ago I created a serverless Azure SQL resource in Terraform using the azurerm_sql_database block. Then in March, in azurerm version 2.3 they came out with the azurerm_mssql_database block, which as I understand is intended to replace azurerm_sql_database.

I need to change the auto_pause_delay_in_minutes setting, which is only available in azurerm_mssql_database. So I guess I need to upgrade now, before there's any official guidance (that I can find) on how to perform the upgrade. If I perform these steps:

• Replace azurerm_sql_database with azurerm_mssql_database
• Remove resource_group_name
• Remove location
• Replace requested_service_objective_name with sku_name
• Replace server_name with server_id

Then terraform tries to delete my database and create a new one, and I get an error like "A resource with the ID [id] already exists - to be managed via Terraform this resource needs to be imported into the State."

How do I perform the upgrade and set auto_pause_delay_in_minutes without deleting my database?

Answer 1

The old resource in Azure needs to be imported into the new resource definition in terraform. Then the old resource state in terraform needs remove. See the following walk through. Modify for whatever additional parameters you need, it is the same workflow.

First build the azurerm_sql_database resource:

# cat .\main.tf
provider "azurerm" {
  version = "~>2.19.0"
  features {}
}

resource "azurerm_resource_group" "example" {
  name     = "example-resources"
  location = "East US"
}

resource "azurerm_sql_server" "example" {
  name                         = "pearcecexamplesqlserver"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = "East US"
  version                      = "12.0"
  administrator_login          = "4dm1n157r470r"
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"

}

resource "azurerm_sql_database" "example" {
  name                = "pearcecexamplesqldatabase"
  resource_group_name = azurerm_resource_group.example.name
  location            = "East US"
  server_name         = azurerm_sql_server.example.name
}

Terraform Apply -- Assuming a clean creation

Change the resource to azurerm_mssql_database and update the parameters

cat .\main.tf
provider "azurerm" {
  version = "~>2.19.0"
  features {}
}

resource "azurerm_resource_group" "example" {
  name     = "example-resources"
  location = "East US"
}

resource "azurerm_sql_server" "example" {
  name                         = "pearcecexamplesqlserver"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = "East US"
  version                      = "12.0"
  administrator_login          = "4dm1n157r470r"
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"

}

resource "azurerm_mssql_database" "example" {
  name                = "pearcecexamplesqldatabase"
  server_id           = azurerm_sql_server.example.id
}

Terraform Apply -- Uh oh

# terraform apply
azurerm_resource_group.example: Refreshing state... [id=/subscriptions/redacted/resourceGroups/example-resources]
azurerm_sql_database.example: Refreshing state... [id=/subscriptions/redacted/resourceGroups/example-resources/providers/Microsoft.Sql/servers/pearcecexamplesqlserver/databases/pearcecexamplesqldatabase]
azurerm_sql_server.example: Refreshing state... [id=/subscriptions/redacted/resourceGroups/example-resources/providers/Microsoft.Sql/servers/pearcecexamplesqlserver]

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

Terraform will perform the following actions:

  # azurerm_mssql_database.example will be created
  + resource "azurerm_mssql_database" "example" {
      + auto_pause_delay_in_minutes = (known after apply)
      + collation                   = (known after apply)
      + create_mode                 = (known after apply)
      + creation_source_database_id = (known after apply)
      + id                          = (known after apply)
      + license_type                = (known after apply)
      + max_size_gb                 = (known after apply)
      + min_capacity                = (known after apply)
      + name                        = "pearcecexamplesqldatabase"
      + 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                   = "/subscriptions/redacted/resourceGroups/example-resources/providers/Microsoft.Sql/servers/pearcecexamplesqlserver"
      + sku_name                    = (known after apply)
      + zone_redundant              = (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)
        }
    }

  # azurerm_sql_database.example will be destroyed
  - resource "azurerm_sql_database" "example" {
      - collation                        = "SQL_Latin1_General_CP1_CI_AS" -> null
      - create_mode                      = "Default" -> null
      - creation_date                    = "2020-07-31T17:54:48.453Z" -> null
      - default_secondary_location       = "West US" -> null
      - edition                          = "GeneralPurpose" -> null
      - id                               = "/subscriptions/redacted/resourceGroups/example-resources/providers/Microsoft.Sql/servers/pearcecexamplesqlserver/databases/pearcecexamplesqldatabase" -> null
      - location                         = "eastus" -> null
      - max_size_bytes                   = "34359738368" -> null
      - name                             = "pearcecexamplesqldatabase" -> null
      - read_scale                       = false -> null
      - requested_service_objective_id   = "f21733ad-9b9b-4d4e-a4fa-94a133c41718" -> null
      - requested_service_objective_name = "GP_Gen5_2" -> null
      - resource_group_name              = "example-resources" -> null
      - server_name                      = "pearcecexamplesqlserver" -> null
      - tags                             = {} -> 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
        }
    }

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

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value:
Apply cancelled

Terraform Import -- Import the resource

# terraform import azurerm_mssql_database.example /subscriptions/redacted/resourceGroups/example-resources/providers/Microsoft.Sql/servers/pearcecexamplesqlserver/databases/pearcecexamplesqldatabase
azurerm_mssql_database.example: Importing from ID "/subscriptions/redacted/resourceGroups/example-resources/providers/Microsoft.Sql/servers/pearcecexamplesqlserver/databases/pearcecexamplesqldatabase"...
azurerm_mssql_database.example: Import prepared!
  Prepared azurerm_mssql_database for import
azurerm_mssql_database.example: Refreshing state... [id=/subscriptions/redacted/resourceGroups/example-resources/providers/Microsoft.Sql/servers/pearcecexamplesqlserver/databases/pearcecexamplesqldatabase]

Import successful!

The resources that were imported are shown above. These resources are now in
your Terraform state and will henceforth be managed by Terraform.

Terraform State Remove -- Remove the old state

terraform state rm azurerm_sql_database.example
Removed azurerm_sql_database.example
Successfully removed 1 resource instance(s)

Terraform Apply - Clean

# terraform apply
azurerm_resource_group.example: Refreshing state... [id=/subscriptions/redacted/resourceGroups/example-resources]
azurerm_sql_server.example: Refreshing state... [id=/subscriptions/redacted/resourceGroups/example-resources/providers/Microsoft.Sql/servers/pearcecexamplesqlserver]
azurerm_mssql_database.example: Refreshing state... [id=/subscriptions/redacted/resourceGroups/example-resources/providers/Microsoft.Sql/servers/pearcecexamplesqlserver/databases/pearcecexamplesqldatabase]

Apply complete! Resources: 0 added, 0 changed, 0 destroyed.