How to create alert rule in terraform for SQL DB

Question

I can't seem to find any examples and I am running into different errors depending on what I'm doing.

I'm trying to get this to work and it's just not happening... any thoughts?

resource "azurerm_monitor_metric_alert" "example" {
  name                = "example-metricalert"
  resource_group_name = azurerm_resource_group.example.name
  scopes              = [azurerm_mssql_database.test.server_id]
  description         = "Action will be triggered when cpu is greater than 80%."

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

}

Answer 1

You can use the below code to create an metrics alert for SQL DB. I have tested it for an existing SQL DB, so used data blocks.

Main.tf

   provider "azurerm" {
  features {}
}


data "azurerm_mssql_server" "example" {
  name                = "ztestansumanserver"
  resource_group_name = "yourresourcegroup"
}

data "azurerm_mssql_database" "dbtomonitor" {
  name      = "testansumandb"
  server_id = data.azurerm_mssql_server.example.id
}

resource "azurerm_monitor_action_group" "example" {
  name                = "CriticalAlertsAction"
  resource_group_name = data.azurerm_mssql_server.example.resource_group_name
  short_name          = "p0action"

  email_receiver {
    name                    = "sendtoadmin"
    email_address           = "youremailid"
    use_common_alert_schema = true
  }
}

resource "azurerm_monitor_metric_alert" "example" {
  name                = "example-metricalert"
  resource_group_name = data.azurerm_mssql_server.example.resource_group_name
  scopes              = [data.azurerm_mssql_database.dbtomonitor.id]
  description         = "Action will be triggered when cpu percent is greater than 80."

  criteria {
    metric_namespace = "Microsoft.Sql/servers/databases"
    metric_name      = "cpu_percent"
    aggregation      = "Average"
    operator         = "GreaterThan"
    threshold        = 80
  }
 action {
    action_group_id = azurerm_monitor_action_group.example.id
  }
}

output:

Note: As per the above script alert is created successfully and it will also trigger a mail to you when the cpu_percent > 80 .

Reference:

Azure Monitor supported metrics by resource type - Azure Monitor | Microsoft Docs