SQL Server 事件通知（Event notifications）

2023-08-05 11:38:10

一.本文所涉及的内容（Contents）

本文所涉及的内容（Contents）
背景（Contexts）
基础知识（Rudimentary Knowledge）
事件通知监控DDL（NotifyQueue_DDL）
事件通知监控SQL跟踪事件（NotifyQueue_Trace）
注意事项（Attention）
疑问（Questions）
参考文献（References）

二.背景（Contexts）

　　SQL Server事件通知有什么用呢？如果你想监控SQL Server的DDL操作，你可以通过DDL触发器（参考：SQL Server DDL触发器运用），也可以通过SQL Server 事件通知把这个事件相关的信息发送到 Service Broker 服务；他们最大的区别就是DDL触发器可以进行ROLLBACK，而事件通知不行；还有，事件通知是异步发送消息的；

　　SQL Server 事件通知还可以响应部分SQL跟踪事件，即SQL Trace （参考：SQL Server 默认跟踪（Default Trace）、SQL Server 创建跟踪）；他们最大的区别就是跟踪事件可以自定义生成哪些数据列，而事件通知是生成固定的XML；还有，每次重新启动服务器时，都必须重新启动跟踪。

三.基础知识（Rudimentary Knowledge）

　　事件通知将有关事件的信息发送给 Service Broker 服务。执行事件通知可对各种 Transact-SQL 数据定义语言 (DDL) 语句和 SQL跟踪事件做出响应，并将这些事件的相关信息发送到 Service Broker 服务。

事件通知可以用来执行以下操作：

记录和检索发生在数据库上的更改或活动。
执行操作以异步（asynchronous）方式而不是同步方式响应事件。

　　可以将事件通知用作替代 DDL 触发器和 SQL 跟踪的编程方法，因为你可以通过读取Service Broker 服务中的队列，在程序中对信息进行处理。

　　事件信息作为 xml 类型的变量传递给 Service Broker 服务，它提供了有关事件的发生时间、受影响的数据库对象、涉及的 Transact-SQL 批处理语句的信息以及其他信息。

　下图是我对事件通知逻辑关系的理解，当数据库A或者实例B产生DDL就会激发事件通知，这个通知把相应的DDL的XML信息发送给队列，你可以通过SQL获取到队列中的XML；

（Figure1：事件通知逻辑关系图）

　　创建事件通知的event_type参数可以为 Transact-SQL DDL 事件类型、SQL 跟踪事件类型或 Service Broker 事件类型有关限定 Transact-SQL DDL 事件类型的列表，请参阅 DDL事件。 Service Broker 事件类型为 QUEUE_ACTIVATION 和 BROKER_QUEUE_DISABLED。有关详细信息，请参阅事件通知。

四.事件通知监控DDL（NotifyQueue_DDL）

　　数据库的DDL操作默认会被记录到Default Trace默认跟踪中（参考：SQL Server 默认跟踪（Default Trace）），这是一个被动式的监控；而主动式的监控就可以使用DDL触发器（参考：SQL Server DDL触发器运用）；我们还可以使用事件通知的形式监控DDL，下面就着重讲讲实现过程。

下面创建一个SSB_DB数据库，捕获数据库实例的DDL语句；

--Step1:创建示例数据库

USE master

GO

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'SSB_DB')

DROP DATABASE SSB_DB

GO

CREATE DATABASE SSB_DB

GO

USE SSB_DB

GO

--Step2:创建队列,默认为开启

CREATE QUEUE NotifyQueue_DDL

--WITH STATUS=ON

GO

--Step3:创建服务

CREATE SERVICE NotifyService_DDL

ON QUEUE NotifyQueue_DDL

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

GO

--Step4:对系统目录视图sys.databases进行查询

SELECT service_broker_guid

FROM sys.databases

WHERE name = 'SSB_DB'

/*

DB19CBE8-0581-4604-B44A-812C29A565BB

*/

--Step5:创建事件通知，使用上面返回的GUID值

CREATE EVENT NOTIFICATION NotifyEvent_DDL

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

TO SERVICE 'NotifyService_DDL',

'DB19CBE8-0581-4604-B44A-812C29A565BB';

--Step6:测试

CREATE TABLE TestTable (a int)

GO

DROP TABLE TestTable;

GO

--Step7:使用Select或Recieve(其中Recieve会删除队列中的事件消息)查询队列

SELECT CAST(message_body as xml) EventInfo

FROM dbo.NotifyQueue_DDL

上面的SQL脚本，你需要注意以下几点：

1. 创建的EVENT NOTIFICATION是针对当前数据库的（ON DATABASE），只有在当前数据库发生的DDL才会被捕获；

2. 使用DDL_DATABASE_LEVEL_EVENTS将会监控当前数据库所有DDL事件，你可以使用DDL_SERVER_LEVEL_EVENTS监控数据库实例的DDL操作，更多的DDL事件可以参考：DDL 事件组；

3. 关于service_broker_guid，你应该通过查询确认这个值，它的作用是指定解析 broker_service 所依据的 Service Broker 实例。

执行Step7返回下图的结果，这是执行Step6脚本产生的两条DDL消息：

（Figure3：DDL事件在队列中的XML信息）

通过下面的SQL脚本可以对Figure4所示的XML进行解释，保存到表中：

执行Step10将返回Figu

--Step8:创建表

CREATE TABLE [dbo].[EventInfo](

    [EventInfoID] [int] IDENTITY(1,1) NOT NULL,

    [PostTime] [datetime] NOT NULL,

    [ServerName] [sysname] NOT NULL,

    [LoginName] [sysname] NOT NULL,

    [DatabaseUser] [sysname] NOT NULL,

    [DatabaseName] [sysname] NOT NULL,

    [Schema] [sysname] NULL,

    [Object] [sysname] NULL,

    [TSQL] [nvarchar](max) NOT NULL,

    [Event] [sysname] NOT NULL,

    [XmlEvent] [xml] NOT NULL,

 CONSTRAINT [PK_EventInfo_EventInfoID] PRIMARY KEY NONCLUSTERED

(

    [EventInfoID] ASC

) ON [PRIMARY]

) ON [PRIMARY]

--Step9:创建分离XML的存储过程

-- =============================================

-- Author:        <听风吹雨>

-- Create date:    <2013.06.19>

-- Description:    <分离XML>

-- Blog:        <http://www.cnblogs.com/gaizai/>

-- =============================================

CREATE PROCEDURE sp_SeparateXML

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @data XML

    DECLARE @itemCur CURSOR

    SET @itemCur = CURSOR FOR

        SELECT CAST(message_body as xml) EventInfo

            FROM [SSB_DB].[dbo].NotifyQueue_DDL

    OPEN @itemCur

    FETCH NEXT FROM @itemCur INTO @data

    WHILE @@FETCH_STATUS=0

    BEGIN

    --逻辑处理

    INSERT [SSB_DB].[dbo].[EventInfo](

        [PostTime],

        [ServerName],

        [LoginName],

        [DatabaseUser],

        [DatabaseName],

        [Schema],

        [Object],

        [TSQL],

        [Event],

        [XmlEvent])

    VALUES(

        @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'sysname'),

        @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),

        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),

        @data.value('(/EVENT_INSTANCE/UserName)[1]', 'sysname'),

        @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname'),

        @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),

        @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),

        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),

        @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),

        @data

        );

    FETCH NEXT FROM @itemCur INTO @data

    END 

    CLOSE @itemCur

    DEALLOCATE @itemCur

END

GO

--Step10:解析XML

TRUNCATE TABLE [EventInfo]

EXEC dbo.sp_SeparateXML

SELECT * FROM [EventInfo]

re4和Figure5的结果，这就是分解后的效果。

（Figure4：结构化XML返回列表）

（Figure5：结构化XML返回列表补充）

上面Step9是使用游标的形式获取XML事件信息，下面Step11以RECEIVE方式获取XML事件信息，这种形式会把消息从队列中删除。

--Step11:创建分离XML的存储过程

-- =============================================

-- Author:        <听风吹雨>

-- Create date:    <2013.06.20>

-- Description:    <RECEIVE方式分离XML>

-- Blog:        <http://www.cnblogs.com/gaizai/>

-- =============================================

CREATE PROCEDURE [dbo].[sp_SeparateXML_RECEIVE]

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @data XML;

    DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

    BEGIN TRANSACTION;

    WAITFOR

    ( RECEIVE TOP(1)

        @RecvReplyDlgHandle = conversation_handle,

        @data = CAST(message_body as xml)

      FROM dbo.NotifyQueue_DDL

    ), TIMEOUT 1000;

    --END CONVERSATION @RecvReplyDlgHandle;

    IF (@data IS NOT NULL)

    BEGIN

        --逻辑处理

        INSERT [SSB_DB].[dbo].[EventInfo](

            [PostTime],

            [ServerName],

            [LoginName],

            [DatabaseUser],

            [DatabaseName],

            [Schema],

            [Object],

            [TSQL],

            [Event],

            [XmlEvent])

        VALUES(

            @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'sysname'),

            @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),

            @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),

            @data.value('(/EVENT_INSTANCE/UserName)[1]', 'sysname'),

            @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname'),

            @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),

            @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),

            @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),

            @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),

            @data

            );

    END

    COMMIT TRANSACTION;

END

GO

--Step12:解析XML

TRUNCATE TABLE [EventInfo]

DECLARE @counts INT

SELECT @counts = COUNT(1) FROM dbo.NotifyQueue_DDL

WHILE(@counts > 0)

BEGIN

    EXEC dbo.sp_SeparateXML_RECEIVE

    SET @counts = @counts - 1

END

SELECT * FROM [EventInfo]

五.事件通知监控SQL跟踪事件（NotifyQueue_Trace）

　　关于使用事件通知监控SQL跟踪事件的文档我基本没有看到过，而且CSDN也没有相关的T-SQL示例，下面就演示实现过程：

USE SSB_DB

GO

--Step13:创建队列

CREATE QUEUE NotifyQueue_Trace

GO

--Step14:创建服务

CREATE SERVICE NotifyService_Trace

ON QUEUE NotifyQueue_Trace

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

GO

--Step15:对系统目录视图sys.databases进行查询

SELECT service_broker_guid

FROM sys.databases

WHERE name = 'SSB_DB'

/*

DB19CBE8-0581-4604-B44A-812C29A565BB

*/

--Step16:创建用户登陆事件通知

CREATE EVENT NOTIFICATION NotifyEvent_Trace

ON SERVER

FOR AUDIT_LOGIN

TO SERVICE 'NotifyService_Trace',

'DB19CBE8-0581-4604-B44A-812C29A565BB';

--Step17:测试登陆，如Figure6所示

--Step18:使用Select或Recieve(其中Recieve会删除队列中的事件消息)查询队列

SELECT CAST(message_body as xml) EventInfo

FROM dbo.NotifyQueue_Trace

ORDER BY message_body DESC

--Step19:查询错误日志

EXEC xp_readerrorlog 0,1,NULL,NULL,NULL,NULL,'DESC'

Step17可以通过Figure6所示的方式进行测试，执行Step18的SQL脚本返回Figure7信息，从内容看的确是监控到了用户登录的信息，同样我们可以通过Step19的SQL脚本查看错误日志的，效果是一样的。

（Figure6：测试登陆）

（Figure7：SQL跟踪事件AUDIT_LOGIN）

（Figure8：ERRORLOG读取）

　　对比Figure7和Figure8的事件时间，可以看到事件先写入了错误日志中，再异步写入到事件通知的消息队列中；

　　其它可以监控的SQL跟踪事件可以通过用于事件通知的跟踪事件进行查看，只有部分的SQL跟踪事件可用于事件通知，注意，不是所有。

六.注意事项（Attention）

1. SERVER：将事件通知的作用域应用于 SQL Server 的当前实例。如果已指定，则只要 FOR 子句中的指定事件在 SQL Server 的实例中发生，便会激发通知。

2. DATABASE：将事件通知的作用域应用于当前数据库。如果已指定，则只要 FOR 子句中的指定事件在当前数据库中发生，便会激发通知。

3. 如果你创建了是SERVER级别的事件通知，那么在每个数据库的[server_events] 和[server_event_notifications]视图中都能看到相同的事件通知信息；

（Figure9：服务器级别事件通知）

4. 在获取队列数据的时候有两种方式，Select或Recieve(其中Recieve会删除队列中的事件消息)；

5. 在ON DATABASE的时候，不能使用FOR DDL_SERVER_LEVEL_EVENTS，不然会报下面的错误信息：

消息1098，级别15，状态1，第2 行

指定的事件类型对指定的目标对象无效。

6. 无论是否回滚 DDL 语句，都将始终生成 DDL 生成的跟踪事件。如果回滚相应 DDL 语句中的事件，则事件通知不会触发。

7. 创建事件通知的时候指定解析 broker_service 所依据的 Service Broker 实例。特定 Service Broker 的值可通过查询 sys.databases 目录视图的 service_broker_guid 列来获取。使用 'current database' 在当前数据库中指定 Service Broker 实例。 'current database' 是不区分大小写的文字字符串。

8. 只能使用 Transact-SQL 语句创建事件通知。出处：CREATE EVENT NOTIFICATION

9. 创建MESSAGE TYPE和CONTRACT的时候，名称的规范建议使用类似URL格式：[//AdventureWorks.com/Helpdesk/SupportTicket]

“The message_type_namevalue must be unique within the database and commonly uses a URL (or URL-like) convention that allows you to create a hierarchical namespace for cre-ating multiple message types for different services.”