Azure terraform add database to elastic pool

Question

I am trying to deploy to azure the following resource:

• mssql server
• mssql database
• mssql elastic pool

as you can see from the following terraform code:

resource "azurerm_mssql_server" "server" {
  administrator_login          = ""
  administrator_login_password = ""
  location                     = var.location
  name                         = "server"
  resource_group_name          = azurerm_resource_group.resource-group.name
  version                      = "12.0"
}

resource "azurerm_mssql_elasticpool" "sql-elasticpool" {
  location            = var.location
  name                = "sql-elasticpool"
  resource_group_name = azurerm_resource_group.resourcegroup.name
  server_name         = azurerm_mssql_server.server.name
  max_size_gb = 4.8828125

  sku {
    capacity = 50
    name     = "BasicPool"
    tier     = "Basic"
  }

  per_database_settings {
    max_capacity = 5
    min_capacity = 0
  }
}

resource "azurerm_mssql_database" "database" {
  name      = "database"
  server_id = azurerm_mssql_server.server.id
  elastic_pool_id = azurerm_mssql_elasticpool.sql-elasticpool.id

}

when i run this terraform file, i get the following error

Error: waiting for create/update of Database: (Name "database" / Server Name "server" / Resource Group "resource group"): Code="ElasticPoolSkuCombinationInvalid" Message="Elastic pool 'sql-elasticpool' and sku 'Basic' combination is invalid."

both, database and elastic pool are on a Basic Sku. Initially i thought that the issue was a conflict between database and elastic, so i omitted the Sku from the sql database, but still the same issue. Can please anyone help to understand what i am doing wrong here please?

Answer 1

Check the following code.

terraform {
  backend "azurerm" {
    resource_group_name  = "xxxx"
    storage_account_name = "remotestate"
    container_name       = "terraform"
    key                  = "terraform.tfstate"
  }
}


resource "azurerm_mssql_server" "server" {
  administrator_login          = "Adminuser"
  administrator_login_password = "Mypassw0rd@!"
  location            = data.azurerm_resource_group.example.location
  name                         = "kasqlserver"
 resource_group_name = data.azurerm_resource_group.example.name
  version                      = "12.0"
}

With same code in my region (eastus), the resources got deployed successfully:

resource "azurerm_mssql_elasticpool" "sql-elasticpool" {
  location            = data.azurerm_resource_group.example.location
  name                = "kavsql-elasticpool"
  resource_group_name = data.azurerm_resource_group.example.name
  server_name         = azurerm_mssql_server.server.name
  max_size_gb = 4.8828125

  sku {
    capacity = 1800
    name     = "BasicPool"
    tier     = "Basic"
  }

  per_database_settings {
    max_capacity = 5
    min_capacity = 0
  }
}

resource "azurerm_mssql_database" "database" {
  name      = "kavdatabase"
  server_id = azurerm_mssql_server.server.id
  elastic_pool_id = azurerm_mssql_elasticpool.sql-elasticpool.id

}

According to Microsoft.Sql/servers/elasticPools 2021-02-01-preview - Bicep, ARM template & Terraform AzAPI reference | Microsoft Learn

"The list of SKUs may vary by region and support offer. To determine the SKUs (including the SKU name, tier/edition, family, and capacity) that are available to your subscription in an Azure region, use the Capabilities_ListByLocation REST API or the following command:"

az sql elastic-pool list-editions -l <location> -o table

In eastus I have support to Below skus of basic pool

To resolve this issue, try using a different SKU for the elastic pool where the selected SKU is available in the region where the resource is deployed.

Reference : Sql Database Elastic pool and sku combination is invalid | StackOverflow