Managing Azure SQL Database with Terraform

Azure SQL Database is a fully managed relational database service. This guide demonstrates how to set up and manage Azure SQL Database using Terraform.

Video Tutorial

Learn more about managing Azure SQL Database with Terraform in this comprehensive video tutorial:

Prerequisites

• Azure CLI configured with appropriate permissions
• Terraform installed (version 1.0.0 or later)
• Resource group created
• Understanding of SQL Server concepts

Project Structure

terraform-azure-sql/
├── main.tf
├── variables.tf
├── outputs.tf
├── modules/
│   └── sql/
│       ├── main.tf
│       ├── variables.tf
│       └── outputs.tf
└── policies/
    └── security.json

SQL Server Configuration

Create modules/sql/main.tf:

# SQL Server
resource "azurerm_mssql_server" "main" {
  name                         = "${var.project_name}-sql"
  resource_group_name          = var.resource_group_name
  location                     = var.location
  version                      = "12.0"
  administrator_login          = var.admin_username
  administrator_login_password = var.admin_password

  azuread_administrator {
    login_username = var.aad_admin_username
    object_id      = var.aad_admin_object_id
  }

  public_network_access_enabled = false
  minimum_tls_version          = "1.2"

  identity {
    type = "SystemAssigned"
  }

  tags = var.tags
}

# SQL Database
resource "azurerm_mssql_database" "main" {
  name                = var.database_name
  server_id           = azurerm_mssql_server.main.id
  collation          = "SQL_Latin1_General_CP1_CI_AS"
  license_type       = "LicenseIncluded"
  max_size_gb        = var.max_size_gb
  sku_name           = var.sku_name
  zone_redundant     = true

  short_term_retention_policy {
    retention_days = 7
  }

  long_term_retention_policy {
    weekly_retention  = "P1W"
    monthly_retention = "P1M"
    yearly_retention  = "P1Y"
    week_of_year     = 1
  }

  geo_backup_enabled = true

  tags = var.tags
}

# Elastic Pool (Optional)
resource "azurerm_mssql_elasticpool" "main" {
  name                = "${var.project_name}-pool"
  resource_group_name = var.resource_group_name
  location            = var.location
  server_name         = azurerm_mssql_server.main.name
  license_type        = "LicenseIncluded"
  max_size_gb        = 50

  sku {
    name     = "GP_Gen5"
    tier     = "GeneralPurpose"
    family   = "Gen5"
    capacity = 4
  }

  per_database_settings {
    min_capacity = 0.25
    max_capacity = 4
  }

  tags = var.tags
}

Network Configuration

1. Virtual Network Integration

resource "azurerm_virtual_network" "main" {
  name                = "${var.project_name}-vnet"
  location            = var.location
  resource_group_name = var.resource_group_name
  address_space       = ["10.0.0.0/16"]

  subnet {
    name           = "sql-subnet"
    address_prefix = "10.0.1.0/24"
    service_endpoints = ["Microsoft.Sql"]
  }

  tags = var.tags
}

resource "azurerm_private_endpoint" "sql" {
  name                = "${var.project_name}-pe"
  location            = var.location
  resource_group_name = var.resource_group_name
  subnet_id           = azurerm_virtual_network.main.subnet.*.id[0]

  private_service_connection {
    name                           = "${var.project_name}-psc"
    private_connection_resource_id = azurerm_mssql_server.main.id
    is_manual_connection          = false
    subresource_names            = ["sqlServer"]
  }

  private_dns_zone_group {
    name                 = "default"
    private_dns_zone_ids = [azurerm_private_dns_zone.sql.id]
  }
}

resource "azurerm_private_dns_zone" "sql" {
  name                = "privatelink.database.windows.net"
  resource_group_name = var.resource_group_name
}

resource "azurerm_private_dns_zone_virtual_network_link" "sql" {
  name                  = "${var.project_name}-vnet-link"
  resource_group_name   = var.resource_group_name
  private_dns_zone_name = azurerm_private_dns_zone.sql.name
  virtual_network_id    = azurerm_virtual_network.main.id
}

Security Configuration

1. Firewall Rules

resource "azurerm_mssql_firewall_rule" "main" {
  for_each = var.firewall_rules

  name             = each.key
  server_id        = azurerm_mssql_server.main.id
  start_ip_address = each.value.start_ip
  end_ip_address   = each.value.end_ip
}

resource "azurerm_mssql_virtual_network_rule" "main" {
  name      = "sql-vnet-rule"
  server_id = azurerm_mssql_server.main.id
  subnet_id = azurerm_virtual_network.main.subnet.*.id[0]
}

2. Transparent Data Encryption

resource "azurerm_mssql_server_transparent_data_encryption" "main" {
  server_id = azurerm_mssql_server.main.id
}

resource "azurerm_key_vault_key" "sql" {
  name         = "${var.project_name}-key"
  key_vault_id = var.key_vault_id
  key_type     = "RSA"
  key_size     = 2048

  key_opts = [
    "decrypt",
    "encrypt",
    "sign",
    "unwrapKey",
    "verify",
    "wrapKey",
  ]
}

resource "azurerm_mssql_server_transparent_data_encryption" "example" {
  server_id        = azurerm_mssql_server.main.id
  key_vault_key_id = azurerm_key_vault_key.sql.id
}

Monitoring Configuration

1. Diagnostic Settings

resource "azurerm_monitor_diagnostic_setting" "sql" {
  name                       = "${var.project_name}-diag"
  target_resource_id        = azurerm_mssql_database.main.id
  log_analytics_workspace_id = var.log_analytics_workspace_id

  log {
    category = "SQLInsights"
    enabled  = true

    retention_policy {
      enabled = true
      days    = 30
    }
  }

  log {
    category = "AutomaticTuning"
    enabled  = true

    retention_policy {
      enabled = true
      days    = 30
    }
  }

  metric {
    category = "Basic"
    enabled  = true

    retention_policy {
      enabled = true
      days    = 30
    }
  }
}

resource "azurerm_monitor_metric_alert" "sql" {
  name                = "${var.project_name}-dtu-alert"
  resource_group_name = var.resource_group_name
  scopes              = [azurerm_mssql_database.main.id]
  description         = "Alert when DTU consumption is high"

  criteria {
    metric_namespace = "Microsoft.Sql/servers/databases"
    metric_name      = "dtu_consumption_percent"
    aggregation      = "Average"
    operator         = "GreaterThan"
    threshold        = 80
  }

  action {
    action_group_id = var.action_group_id
  }
}

Backup Configuration

1. Long-term Backup

resource "azurerm_mssql_database_extended_auditing_policy" "main" {
  database_id                             = azurerm_mssql_database.main.id
  storage_endpoint                        = azurerm_storage_account.audit.primary_blob_endpoint
  storage_account_access_key              = azurerm_storage_account.audit.primary_access_key
  storage_account_access_key_is_secondary = false
  retention_in_days                       = 30
}

resource "azurerm_backup_protected_database" "main" {
  resource_group_name = var.backup_resource_group_name
  recovery_vault_name = var.recovery_vault_name
  source_database_id  = azurerm_mssql_database.main.id
  backup_policy_id    = var.backup_policy_id
}

Best Practices

1. Security

   • Enable Azure AD authentication
   • Use private endpoints
   • Implement firewall rules
   • Enable TDE

2. Performance

   • Choose appropriate tier
   • Monitor DTU/vCore usage
   • Enable automatic tuning
   • Use connection pooling

3. High Availability

   • Enable geo-replication
   • Configure backups
   • Use availability zones
   • Monitor health

4. Cost Optimization

   • Use appropriate tier
   • Implement auto-scaling

Conclusion

You’ve learned how to set up and manage Azure SQL Database using Terraform. This setup provides:

• Secure database configuration
• High availability
• Monitoring and alerts
• Backup and disaster recovery

Remember to:

• Monitor performance
• Review security settings
• Maintain backups
• Update access controls

Advanced Features

1. Geo-Replication

resource "azurerm_mssql_database" "secondary" {
  name                        = "${var.database_name}-secondary"
  server_id                   = azurerm_mssql_server.secondary.id
  create_mode                 = "Secondary"
  creation_source_database_id = azurerm_mssql_database.main.id

  tags = var.tags
}

2. Automatic Tuning

resource "azurerm_mssql_database_automatic_tuning" "main" {
  database_id = azurerm_mssql_database.main.id

  automatic_tuning_enabled = true

  tuning_options {
    create_index    = "Auto"
    drop_index     = "Auto"
    force_plan     = "Auto"
    maintain_index = "Auto"
  }
}