How to create custom Azure SQL alerts on T-SQL for bulk updates in Azure SQL DB and Azure SQL MI

Question

How to create custom alerts on T-SQL for bulk updates in azure SQL db and Azure SQL MI.

Currently we can create Alerts in Azure Databade only on Metrics.

My requirement is to create alert on bulk data inserts/updates/deletes on any table in my db as part of audit needs.

Is there any way to add an alert on T-SQL command in Azure SQL or Managed Instance

I have to setup alerts on bulk DML operations on Azure SQL and Azure SQL MI , don't want to use Triggers for performance degradation reasons. I am using Azure Storage account as the place to store logs.

Answer 1

To my knowledge you cannot use Transact-SQL for this task. The only way I know to do this is using Kusto Query Language.

First, you need to enable auditing for your Azure SQL Database and configure it to send the audit logs to Azure Log Analytics.

Second, you need to write a Kusto query that filters the audit logs for the UPDATE statements on the table you want to monitor. For example, if you want to monitor the updates on the Employees table, you can use this query:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where action_name_s == "UPDATE"
| where statement_s contains "Employees"
| project TimeGenerated, database_name_s, server_principal_name_s, statement_s

This query returns the time, database name, user name, and statement of the update events on the Employees table.

Third, you need to create an alert rule based on the Kusto query. You can do all this by following the instructions in this URL. You can specify the alert criteria, such as the threshold, frequency, and severity of the alert. You can also specify the alert action, such as sending an email or calling a webhook.