Configuring SQL for High Availability in Terraform

Hello All, a short post this week, but as we talk about availability, chaos engineering, etc. One of the most common data elements I see out there is SQL, and Azure SQL. SQL is a prevelant and common data store, it’s everywhere you look.

Given that, many shops are implementing infrastructure-as-code to manage configuration drift and provide increased resiliency for their applications. Which is definitely a great way to do that. The one thing that I’ve had a couple of people talk to me about that isn’t clear…how can I configure geo-replication in TerraForm.

This actually built into the TerraForm azure rm provider, and can be done with the following code:

provider "azurerm" { subscription\_id = "" features { key\_vault { purge\_soft\_delete\_on\_destroy = true } }}data "azurerm\_client\_config" "current" {}resource "azurerm\_resource\_group" "rg" { name = "sqlpoc" location = "{region}"}resource "azurerm\_sql\_server" "primary" { name = "kmack-sql-primary" resource\_group\_name = azurerm\_resource\_group.rg.name location = azurerm\_resource\_group.rg.location version = "12.0" administrator\_login = "sqladmin" administrator\_login\_password = "{password}"}resource "azurerm\_sql\_server" "secondary" { name = "kmack-sql-secondary" resource\_group\_name = azurerm\_resource\_group.rg.name location = "usgovarizona" version = "12.0" administrator\_login = "sqladmin" administrator\_login\_password = "{password}"}resource "azurerm\_sql\_database" "db1" { name = "kmackdb1" resource\_group\_name = azurerm\_sql\_server.primary.resource\_group\_name location = azurerm\_sql\_server.primary.location server\_name = azurerm\_sql\_server.primary.name}resource "azurerm\_sql\_failover\_group" "example" { name = "sqlpoc-failover-group" resource\_group\_name = azurerm\_sql\_server.primary.resource\_group\_name server\_name = azurerm\_sql\_server.primary.name databases = [azurerm\_sql\_database.db1.id] partner\_servers { id = azurerm\_sql\_server.secondary.id } read\_write\_endpoint\_failover\_policy { mode = "Automatic" grace\_minutes = 60 }}

Now above TF, will deploy two database servers with geo-replication configured. The key part is the following:

resource "azurerm\_sql\_failover\_group" "example" { name = "sqlpoc-failover-group" resource\_group\_name = azurerm\_sql\_server.primary.resource\_group\_name server\_name = azurerm\_sql\_server.primary.name databases = [azurerm\_sql\_database.db1.id] partner\_servers { id = azurerm\_sql\_server.secondary.id } read\_write\_endpoint\_failover\_policy { mode = "Automatic" grace\_minutes = 60 }}

The important elements are “server_name” and “partner_servers”, this makes the connection to where the data is being replicated. And then the “read_write_endpoint_failover_policy” setups up the failover policy.