SQL Server 事件通知（Event notifications）
一.本文所涉及的内容（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