Azure failover groups

Terraform
1

i have managed to construct two sql servers each with their own elastic pools
i then create a failover group
as i add databases to the first server and include it in the failover group then all is well
but when i try to delete the database it can not remove it from the secondary server as it was not created by terraform but my microsoft failover.

I understand the documentation makes mention of this and that i should ‘create the resource if i want terraform to manage it’ butwithout an example I dont really understand what it wants me to do.

Has anyone else done this and could they explain what they want me to do to manage databases in failover groups through terraform alone?

thanks

2

I was using modules.

When I use resources with count it seems to work properly.

Imma put this down to modules and a pinch of me not having a clue what I’m doing.

3

I wasnt overly happy with not really understanding how I got it to work - so I tinkered around a bit more and found the solution. (not sure how to mark answers as solved here neither).

I was bamboozled by the advice presented by Terraform on their help docs:

NOTE: The failover group will create a secondary database for each database listed in databases . If the secondary databases need to be managed through Terraform, they should be defined as resources and a dependency added to the failover group to ensure the secondary databases are created first.

I believe this is wrong, It should read:

NOTE: The failover group will create a secondary database for each database listed in databases . If the secondary databases need to be managed through Terraform, they should be defined as resources and a dependency added to the secondary databases to ensure the failover group is created first.

As Microsoft creats the secondary database automatically, it makes sense to define the secondary database in the script to rely on the failover group having been built first - and therefore create the secondary databases. Terraform will then see they are built and register them in the state file without Terraform actually being responsibile for directly building them.

Tada!

4

Do you have any code snippet to show how you solved this issue?

5

I think my vanilla script should explain it:

resource "azurerm_resource_group" "my_resource_group" {
  name     = format("%s-%02d-%s", var.sql_server_prefix_name, count.index + 1, element(var.regions, count.index))
  location = element(var.regions, count.index)
  count    = length(var.regions)
  tags     = merge(local.tags)
}
resource "azurerm_sql_server" "my_sql_server" {
  name                         = format("%s-%02d-%s", var.sql_server_prefix_name, count.index + 1, element(var.regions, count.index))
  count                        = length(var.regions)
  resource_group_name          = element(azurerm_resource_group.my_resource_group.*.name, count.index)
  location                     = element(azurerm_resource_group.my_resource_group.*.location, count.index)
  version                      = "12.0"
  administrator_login          = var.administrator_login_uid
  administrator_login_password = var.administrator_login_pwd
  tags                         = merge(local.tags)
  depends_on                   = [azurerm_resource_group.my_resource_group.0, azurerm_resource_group.my_resource_group.1]
}
module "my_sql_server_1_firewall_rules" {
  source = "./tf-sql-server-firewall-rules"

  resource_group_name = azurerm_resource_group.my_resource_group.0.name
  sql_server_name     = azurerm_sql_server.my_sql_server.0.name
}
module "my_sql_server_2_firewall_rules" {
  source = "./tf-sql-server-firewall-rules"

  resource_group_name = azurerm_resource_group.my_resource_group.1.name
  sql_server_name     = azurerm_sql_server.my_sql_server.1.name
}
resource "azurerm_mssql_elasticpool" "my_elastic_pool" {
  name                = local.elastic_pool_name
  resource_group_name = element(azurerm_resource_group.my_resource_group.*.name, count.index)
  location            = element(azurerm_resource_group.my_resource_group.*.location, count.index)
  server_name         = element(azurerm_sql_server.my_sql_server.*.name, count.index)
  max_size_gb         = 10
  sku {
    name     = "GP_Gen5"
    tier     = "GeneralPurpose"
    family   = "Gen5"
    capacity = 6
  }
  per_database_settings {
    min_capacity = 0.25
    max_capacity = 6
  }
  count      = length(var.regions)
  tags       = merge(local.tags)
  depends_on = [azurerm_sql_server.my_sql_server.0, azurerm_sql_server.my_sql_server.1]
}
resource "azurerm_sql_database" "my_database" {
  name                = var.database_name
  resource_group_name = azurerm_resource_group.my_resource_group.0.name
  location            = element(var.regions, 0)
  server_name         = azurerm_sql_server.my_sql_server.0.name
  elastic_pool_name   = local.elastic_pool_name
  tags                = merge(local.tags)
  depends_on          = [azurerm_mssql_elasticpool.my_elastic_pool.0, azurerm_mssql_elasticpool.my_elastic_pool.1]
}
resource "azurerm_sql_database" "my_database2" {
  name                = var.database_name
  resource_group_name = azurerm_resource_group.my_resource_group.*.name[1]
  location            = element(var.regions, 1)
  server_name         = azurerm_sql_server.my_sql_server.*.name[1]
  elastic_pool_name   = local.elastic_pool_name
  tags                = merge(local.tags)
  depends_on          = [azurerm_mssql_elasticpool.my_elastic_pool[1], azurerm_sql_failover_group.my_failover_group]
}
resource "azurerm_sql_failover_group" "my_failover_group" {
  name                = local.failover_group_name
  resource_group_name = azurerm_resource_group.my_resource_group.*.name[0]
  server_name         = azurerm_sql_server.my_sql_server.*.name[0]
  databases           = [azurerm_sql_database.my_database.id]
  partner_servers {
    id = azurerm_sql_server.my_sql_server.*.id[1]
  }
  read_write_endpoint_failover_policy {
    mode = "Manual"
  }
  tags       = merge(local.tags)
  # depends_on = [azurerm_sql_database.my_database2]
}
1 Like
6

I tried the same and facing the same issue…

Error: sql.ElasticPoolsClient#Delete: Failure sending request: StatusCode=400 – Original Error: Code=“ElasticPoolNotEmpty” Message=“The elastic pool is not empty.”

7

not sure as it works for me.

it could be an issue with your state file ?

have you tried it in isolation ensuring the state file doesnt exist to start with?

8

This issue is fixed by adding depond_on (depends_on = [azurerm_mssql_elasticpool.secondary_elastic_pool) to the elastic_pool secondary_databases.

9

Thanks for the example, spent far too long fighting this!

10

@klagan Replying to a really old thread here but perhaps you’d be interested in this.

I had the same issue recently. I raised an issue on GitHub and it got a great response. Check the PR linked to this issue for a good worked example of how to set this up: https://github.com/terraform-providers/terraform-provider-azurerm/issues/8778#issuecomment-706062532

Specifically this file here: https://github.com/terraform-providers/terraform-provider-azurerm/blob/db1222a2144bdbb94f30c3cac19e27fbc8f373de/examples/sql-azure/failover_group/main.tf

11

many thanks on circling back to let us all know this has been dealt with and good work on raising the issue with the developers