# Azure failover groups

**URL:** <https://discuss.hashicorp.com/t/azure-failover-groups/3539>\
**Category:** Terraform\
**Created:** [October 24, 2019, 2:57pm UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539 "2019-10-24T14:57:01Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![klagan](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/klagan/32/2775_2.png) [@klagan](https://discuss.hashicorp.com/u/klagan)\
**Post date:** [October 24, 2019, 2:57pm UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/1 "2019-10-24T14:57:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![klagan](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/klagan/32/2775_2.png) [@klagan](https://discuss.hashicorp.com/u/klagan)\
**Post date:** [October 25, 2019, 9:36pm UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/2 "2019-10-25T21:36:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![klagan](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/klagan/32/2775_2.png) [@klagan](https://discuss.hashicorp.com/u/klagan)\
**Post date:** [October 26, 2019, 7:32pm UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/3 "2019-10-26T19:32:15Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![tomjosekal](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/tomjosekal/32/4852_2.png) [@tomjosekal](https://discuss.hashicorp.com/u/tomjosekal)\
**Post date:** [February 22, 2020, 4:11am UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/4 "2020-02-22T04:11:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![klagan](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/klagan/32/2775_2.png) [@klagan](https://discuss.hashicorp.com/u/klagan)\
**Post date:** [February 22, 2020, 6:28pm UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/5 "2020-02-22T18:28:41Z")

</div>

I think my vanilla script should explain it:

```auto
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]
}

```

---

<div class="post-metadata">

**Author:** ![tomjosekal](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/tomjosekal/32/4852_2.png) [@tomjosekal](https://discuss.hashicorp.com/u/tomjosekal)\
**Post date:** [February 23, 2020, 5:58am UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/6 "2020-02-23T05:58:53Z")

</div>

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.”

---

<div class="post-metadata">

**Author:** ![klagan](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/klagan/32/2775_2.png) [@klagan](https://discuss.hashicorp.com/u/klagan)\
**Post date:** [February 23, 2020, 7:22am UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/7 "2020-02-23T07:22:20Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![tomjosekal](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/tomjosekal/32/4852_2.png) [@tomjosekal](https://discuss.hashicorp.com/u/tomjosekal)\
**Post date:** [February 27, 2020, 7:57am UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/8 "2020-02-27T07:57:29Z")

</div>

This issue is fixed by adding depond\_on (depends\_on = [azurerm\_mssql\_elasticpool.secondary\_elastic\_pool) to the elastic\_pool secondary\_databases.

---

<div class="post-metadata">

**Author:** ![hobbesuk](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/hobbesuk/32/5304_2.png) [@hobbesuk](https://discuss.hashicorp.com/u/hobbesuk)\
**Post date:** [March 13, 2020, 9:42am UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/9 "2020-03-13T09:42:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rossdotpink](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/rossdotpink/32/12836_2.png) [@rossdotpink](https://discuss.hashicorp.com/u/rossdotpink)\
**Post date:** [October 14, 2020, 8:18am UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/10 "2020-10-14T08:18:46Z")

</div>

@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](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](https://github.com/terraform-providers/terraform-provider-azurerm/blob/db1222a2144bdbb94f30c3cac19e27fbc8f373de/examples/sql-azure/failover_group/main.tf)

---

<div class="post-metadata">

**Author:** ![klagan](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/klagan/32/2775_2.png) [@klagan](https://discuss.hashicorp.com/u/klagan)\
**Post date:** [October 15, 2020, 4:11pm UTC](https://discuss.hashicorp.com/t/azure-failover-groups/3539/11 "2020-10-15T16:11:26Z")

</div>

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