Terraform Azure - Add SQL replica to elastic pool

Question

I have a sql database that is in an elastic pool and when terraform runs it tries to add it to a replica on a secondary server and add it to an elastic pool there, but i get an error:

Error: waiting for create/update of Database: (Name "Test" / Server Name "testsql-secondary" / Resource Group "testnonprod"): Code="InvalidSku" Message="The sku 'ElasticPool' specified is invalid."

This is my terraform to create the elastic pool on the primary.

resource "azurerm_mssql_elasticpool" "elastic_pool" {
  for_each            = toset(var.elasticpool_list)
  name                = each.key
  resource_group_name = var.resource_group_name
  location            = var.location
  server_name         = azurerm_mssql_server.sql_server.name
  max_size_gb         = 100

  sku {
    name     = "StandardPool"
    tier     = "Standard"
    capacity = 100
  }

  per_database_settings {
    min_capacity = 0
    max_capacity = 10
  }

This is the code to create the secondary elasticpool.

resource "azurerm_mssql_elasticpool" "elastic_pool_secondary" {
  for_each            = toset(var.failover_group == "true" ? var.elasticpool_list : [])
  name                = each.key
  resource_group_name = var.resource_group_name
  location            = var.location
  server_name         = azurerm_mssql_server.sql_server_secondary[0].name
  max_size_gb         = 100

  sku {
    name     = "StandardPool"
    tier     = "Standard"
    capacity = 100
  }

  per_database_settings {
    min_capacity = 0
    max_capacity = 10
  }

And the code I use to create the databases.

resource "azurerm_mssql_database" "sql_db" {
  for_each                    = var.sql_db_list
  name                        = each.key
  server_id                   = azurerm_mssql_server.sql_server.id
  collation                   = "SQL_Latin1_General_CP1_CI_AS"
  read_scale                  = false
  sku_name                    = each.value["SKU"]
  zone_redundant              = false
  min_capacity                = each.value["min_capacity"]
  auto_pause_delay_in_minutes = var.auto_pause_delay
  elastic_pool_id             = each.value["elasticpool_name"] != "" ? azurerm_mssql_elasticpool.elastic_pool[each.value["elasticpool_name"]].id : null

resource "azurerm_mssql_database" "sql_db_secondary" {
  for_each  = var.failover_group == "true" ? var.sql_db_list : {}
  name      = each.key
  server_id = azurerm_mssql_server.sql_server_secondary.0.id
  collation = "SQL_Latin1_General_CP1_CI_AS"
  sku_name  = each.value["SKU"]
  #zone_redundant              = false
  min_capacity                = each.value["min_capacity"]
  create_mode                 = "Secondary"
  creation_source_database_id = azurerm_mssql_database.sql_db[each.key].id
  elastic_pool_id             = each.value["elasticpool_name"] != "" ? azurerm_mssql_elasticpool.elastic_pool_secondary[each.value["elasticpool_name"]].id : null

The database comes from a map and this is what it looks like.

 "Test" = {
      "SKU"              = "ElasticPool"
      "min_capacity"     = "0.5"
      "Namespaces"       = [""]
      "elasticpool_name" = "test-elasticpool"
    }

I have tried to change the SKU to a few different things, none have worked. When I look in the azure portal it shows the SKU as ElasticPool? Any help is much appreciated.

Justin Gunderson · Accepted Answer · 2022-07-14 14:07:45Z

2

I found the issue here. The resource that creates the secondary server does not need the SKU defined. when i commented that out it now creates the secondary server like we want and adds it to the elastic pool.

answered Jul 14, 2022 at 14:07

Justin Gunderson

614 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Terraform Azure - Add SQL replica to elastic pool

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related