如何在Azure SQL DB与SQL MI中基于T-SQL创建批量DML自定义警报

可以通过T-SQL结合Azure Monitor的自定义警报机制实现需求，核心思路是利用存储账户中的审计日志（或Extended Events数据），通过T-SQL查询识别批量操作事件，再基于该查询创建Azure警报规则。以下是具体实现步骤：

一、通过T-SQL访问存储账户中的审计日志 #

Azure SQL的审计日志以JSON格式存储在Blob存储中，可通过创建外部数据源和外部表，用T-SQL直接查询这些日志：

1. 创建存储账户凭据（用于访问Blob存储）

CREATE DATABASE SCOPED CREDENTIAL StorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'your-sas-token';

2. 创建外部数据源指向存储账户的审计日志容器

CREATE EXTERNAL DATA SOURCE AuditLogsStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<your-storage-account>.blob.core.windows.net/<your-audit-container>',
    CREDENTIAL = StorageCredential
);

3. 创建JSON文件格式对象

CREATE EXTERNAL FILE FORMAT JsonFileFormat
WITH (
    FORMAT_TYPE = JSON,
    DATA_COMPRESSION = 'NONE'
);

4. 创建外部表映射审计日志结构

CREATE EXTERNAL TABLE AuditLogs (
    event_time DATETIME2,
    action_id NVARCHAR(4),
    statement NVARCHAR(MAX),
    database_name NVARCHAR(128),
    schema_name NVARCHAR(128),
    object_name NVARCHAR(128),
    server_principal_name NVARCHAR(128),
    session_id INT
)
WITH (
    LOCATION = 'sqlauditlogs/<your-sql-server>/<your-db>/*',
    DATA_SOURCE = AuditLogsStorage,
    FILE_FORMAT = JsonFileFormat
);

二、编写T-SQL查询识别批量操作 #

根据业务需求编写查询，筛选出批量INSERT/UPDATE/DELETE操作。示例如下：

SELECT
    event_time,
    server_principal_name,
    database_name,
    object_name,
    statement
FROM AuditLogs
WHERE
    -- 匹配增删改操作的action_id
    action_id IN ('INS', 'UPD', 'DEL')
    -- 识别批量操作逻辑，可根据业务调整
    AND (
        CHARINDEX('BULK INSERT', statement) > 0
        OR CHARINDEX('INSERT INTO ... SELECT', statement) > 0
        OR (CHARINDEX('UPDATE', statement) > 0 AND CHARINDEX('TOP', statement) = 0)
        OR (CHARINDEX('DELETE', statement) > 0 AND CHARINDEX('TOP', statement) = 0)
    )
    -- 仅查询最近1小时内的事件，适配警报的时间窗口
    AND event_time >= DATEADD(HOUR, -1, GETUTCDATE())

三、创建Azure Monitor自定义警报规则 #

1. 进入Azure Portal的Monitor服务，选择警报 > 创建 > 警报规则
2. 选择目标资源：你的Azure SQL DB/MI实例
3. 在条件中选择自定义日志搜索，将上述T-SQL查询粘贴到搜索框
4. 设置触发条件：比如当查询返回的行数大于0时触发警报
5. 配置通知方式（邮件、短信、Webhook等）和警报详情，完成创建

备选方案：利用Extended Events捕获批量操作 #

如果审计日志无法满足需求（比如需要获取操作影响行数），可通过Extended Events捕获批量操作事件：

1. 创建Extended Events会话，捕获sql_statement_completed事件，筛选影响行数较多的增删改操作：

CREATE EVENT SESSION BulkOperationsCapture ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    WHERE (
        (statement LIKE '%INSERT%' OR statement LIKE '%UPDATE%' OR statement LIKE '%DELETE%')
        AND row_count > 100 -- 定义批量操作的行数阈值
    )
)
ADD TARGET package0.event_file(
    SET filename = 'https://<your-storage-account>.blob.core.windows.net/<ee-container>/BulkOperations.xel'
)
WITH (STARTUP_STATE = ON);

2. 同样通过外部表访问存储账户中的XEL文件，编写查询识别事件，再创建Azure警报规则。

内容的提问来源于stack exchange，提问作者richa agarwal