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
}

}

Might be worth also adding your code that creates the resource group and sql server — user3362856, Sep 01 '21 at 19:35
"different errors depending on what i'm donig" - what errors exactly? — Marcin, Sep 01 '21 at 21:53
Error creating or updating metric alert "example-metricalert" (resource group "tempsql-rg"): insights.MetricAlertsClient#CreateOrUpdate: Failure responding to request: StatusCode=400 -- Original Error: autorest/azure: Service returned an error. Status=400 Code="ResourceNotFound" Message="{\"code\":\"BadRequest\",\"message\":\"Detect invalid value: Microsoft.Sql/servers/databases for query parameter: 'metricnamespace', the value must be: Microsoft.Sql/servers if the query parameter is provided, you can also skip this optional query parameter.\ — Mattman5000, Sep 02 '21 at 14:21
Not sure how to resolve this error.. i changed metric_name = "cpu_percent" since i found a list of the metric names... if i change metric_namespace ="Microsoft.Sql/servers" then I get a whole new set of errors .. it also doesn't have the metric cpu percent that i want to use — Mattman5000, Sep 02 '21 at 14:22

score 3 · Accepted Answer · answered Sep 08 '21 at 09:59

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

thanks for this! the difference was .id and i used .server_id — Mattman5000, Sep 10 '21 at 14:46

How to create alert rule in terraform for SQL DB

1 Answers1