Confused on how to script out an Azure Sql Database

Terraform
1

Folks,

I am attempting to script out the creation of an Azure sql database using the DTU pricing model. I see azure resources for sql_database and mssql_databse and elasticpools. Basically I’ve got a azure pricing calculator “schema” I’m trying to mimic in terraform but I can’t find any information on how to use azurerm_sql_database to create a DTU-based sql database. I see an “edition” option, but that doesn’t really coincide with anything on the sql database help pages and nowhere on the azurerm_sql_database page does “DTU” exist.

What should I be using to create a standard, single database using azurerm_sql_database for a DTU pricing model?

1 Like
2

@klainn probably this should help you - terraform-azurerm-db-sql/r-sql.tf at master · claranet/terraform-azurerm-db-sql · GitHub

3

hi @klainn
did you succeed in deploying the sql db single instance with DTU pricing model?
if yes, can you please share how did you do?

4

@daniloercolano

If I remember correctly, this took an issue on the provider github page and it may have been resolved. I think one of the keys was using this super specific max_size_gb parameter.

I think there were 2 issues that I had opened, azurerm_mssql_elasticpool - service tier ‘Basic’ must have whole numbers as their ‘minCapacity’
and 500 error when creating a mssql database

Here’s the last bit of code that I used, I can’t verify it worked because it’s been about a year and that project died at the end of 2020.

resource "azurerm_mssql_server" "sql-server_1" {
  name                         = "#{agency-name}#-#{department}#-#{application-name}#-#{environment}#-sqlsvr-1"
  resource_group_name          = "zus1-#{agency-name}#-#{application-name}#-#{environment}#-v1-rg"
  location                     = "#{resource-location}#"
  version                      = "12.0"
  administrator_login          = "sqlsvr-1-${local.sql-1-rando_username}"
  administrator_login_password = local.sql-1-rando_password

  lifecycle {
    ignore_changes = [administrator_login, administrator_login_password]
  }

  tags = merge(local.common_tags, map("type", "mssql-server"))
  depends_on = [azurerm_key_vault.key_vault]
}

# Create the SQL Elastic Pool
resource "azurerm_mssql_elasticpool" "sql-elastic_pool" {
  name = "#{agency-name}#-#{department}#-#{application-name}#-#{environment}#-epool"
  location = "#{resource-location}#"
  resource_group_name = "zus1-#{agency-name}#-#{application-name}#-#{environment}#-v1-rg"
  server_name = azurerm_mssql_server.sql-server_1.name
  max_size_gb = 4.8828125
  tags = merge(local.common_tags, map("type", "mssql-elastic-pool"))

  per_database_settings {
    min_capacity = 0
    max_capacity = 5
  }

  sku {
    capacity = 50
    name = "BasicPool"
    tier = "Basic"
  }
  depends_on = [azurerm_mssql_server.sql-server_1]
}

# Create a Database
resource "azurerm_mssql_database" "sql-database" {
  name                = "#{agency-name}#-#{department}#-#{application-name}#-#{environment}#-db"
  server_id           = azurerm_mssql_server.sql-server_1.id
  collation           = "SQL_Latin1_General_CP1_CI_AS"
  read_scale          = false
  sku_name            = "ElasticPool"
  zone_redundant      = false
  tags = merge(local.common_tags, map("type", "mssql-database"))
  elastic_pool_id     = azurerm_mssql_elasticpool.sql-elastic_pool.id
  depends_on = [azurerm_mssql_elasticpool.sql-elastic_pool, azurerm_storage_account.sql-storage_account]
}

resource "azurerm_mssql_database_extended_auditing_policy" "sql-database_policy" {
  database_id                             = azurerm_mssql_database.sql-database.id
  storage_endpoint                        = azurerm_storage_account.sql-storage_account.primary_blob_endpoint
  storage_account_access_key              = azurerm_storage_account.sql-storage_account.primary_access_key
  storage_account_access_key_is_secondary = false
  retention_in_days                       = 6
}