Terraform Azure - Move an existing Azure SQL Database into an Elastic Pool

Question

I have an existing Azure SQL Server and 1 database that wasn't created into an Elastic Pool initially. Terraform has deployed this and kept the state.

# Define SQL Server 1
resource "azurerm_mssql_server" "go-1" {
  name                          = "sql-sandbox-server01
  resource_group_name           = data.azurerm_resource_group.env-resourcegroup.name
  location                      = data.azurerm_resource_group.env-resourcegroup.location
  version                       = var.azsqlserver1version
  administrator_login           = var.azsqlserver1sauser
  administrator_login_password  = random_password.sql-password.result
  public_network_access_enabled = "true" # set to false with vNet integration
}
# Define SQL Database 1 - non-ElasticPool
resource "azurerm_mssql_database" "go-1" {
  name                = "sqldb-sandbox-01"
  server_id           = azurerm_mssql_server.go-1.id
  sku_name            = "Basic"
}

Since the decision now to use Elastic Pools has been reached (for this single database and others to follow) the database "sqldb-sandbox-01" now already has tables and data in it.

I've added this to my main.tf file...and it works fine, the elastic pool gets created...

resource "azurerm_sql_elasticpool" "go-1" {
  name                = "sqlep-sandbox-pool01
  resource_group_name = data.azurerm_resource_group.env-resourcegroup.name
  location            = data.azurerm_resource_group.env-resourcegroup.location
  server_name         = azurerm_mssql_server.go-1.name
  edition             = "Basic"
  dtu                 = 50
  db_dtu_min          = 0
  db_dtu_max          = 5
  pool_size           = 5000
}

My question is...How do I move the existing "sqldb-sandbox-01" into the Elastic Pool in Terraform without it destroying the database and recreating it?

I attempted this, just adding the single line elastic_pool_id, but as the documentation states it will destroy/create the database again...

# Define SQL Database 1 - non-ElasticPool
resource "azurerm_mssql_database" "go-1" {
  name                = var.azsqldb1name
  server_id           = azurerm_mssql_server.go-1.id
  sku_name            = var.azsqldb1sku
  elastic_pool_id     = azurerm_sql_elasticpool.go-1.id
}

I would be grateful to hear from anyone that has been in the same position and managed to find a way.

To move an existing same-server database into an Elastic Pool is easily achieved in the Azure Portal GUI, so I was hoping for something similar here. I did some searching around but couldn't find anything specific to this straightforward task.

Thanks in advance

Answer 1

For the existing Azure SQL database and Elastic Pool. Directly adding the single line elastic_pool_id in the block will force a new resource to be created. Even this display is not obvious in the Azure portal.

Instead of doing this, you could use local PowerShell scripts to add the existing database to the new Elastic Pool. The local-exec provisioner invokes a local executable after a resource is created.

Here is a working sample on my side.

resource "null_resource" "add_pool" {
 
  provisioner "local-exec" {

  command = <<-EOT
   Set-AzSqlDatabase `
   -ResourceGroupName "${azurerm_resource_group.example.name}" `
   -ServerName "${azurerm_mssql_server.example.name}" `
   -DatabaseName "${azurerm_mssql_database.test.name}" `
   -ElasticPoolName "${azurerm_sql_elasticpool.go-1.name}"
  EOT
    
  interpreter = ["PowerShell", "-Command"]

 }

}

Answer 2

This actually ended up easier than anticipated, after quite a lot of testing.

My original database segment looked like this...

# Define SQL Database 1 - non-ElasticPool
resource "azurerm_mssql_database" "go-1" {
  name                = var.azsqldb1name
  server_id           = azurerm_mssql_server.go-1.id
  sku_name            = "Basic"
}

It made sense to just move the database to an elastic pool manually, rather than try and do it in-code.

Once the database had moved to the elastic pool, I noticed the Azure ID behind the database did not change.

I then updated the Terraform with a change to the sku_name, and the addition of the elastic_pool_id...

resource "azurerm_mssql_database" "go-1" {
  name                = var.azsqldb1name
  server_id           = azurerm_mssql_server.go-1.id
  sku_name            = "ElasticPool"
  elastic_pool_id     = azurerm_sql_elasticpool.go-1.id
}

Run the Terraform Plan again, No infrastructure changes detected, it has worked and doesn't want to destroy anything.

In summary: Do the move of the stand-alone database to the Elastic Pool manually

Update your Terraform for the database in question with a change to...sku_name...and the addition of the elastic_pool_id

Thanks to all those that assisted me with this question

Answer 3

Switching from a General Purpose tier to ElasticPool is safe now (tested with AzureRM Provider 3.29). Terraform will do a migration to the Elasticpool without a re-creation of the database:

At least this is the case for MSSQL Database.