Home > front end >  Terraform Azure - Move an existing Azure SQL Database into an Elastic Pool
Terraform Azure - Move an existing Azure SQL Database into an Elastic Pool

Time:11-29

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

CodePudding user response:

For the existing Azure SQL database and Elastic Pool. Directly adding the single line enter image description here

Instead of doing this, you could use local PowerShell scripts to add the existing database to the new Elastic Pool. The enter image description here

At least this is the case for MSSQL Database.

  • Related