代码改变世界

SQL Server 事件通知(Event notifications)

2013-12-13 17:21  听风吹雨  阅读(10190)  评论(6编辑  收藏  举报

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 基础知识(Rudimentary Knowledge)
  4. 事件通知监控DDL(NotifyQueue_DDL)
  5. 事件通知监控SQL跟踪事件(NotifyQueue_Trace)
  6. 注意事项(Attention)
  7. 疑问(Questions)
  8. 参考文献(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;

wps_clip_image-22400

(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消息:

wps_clip_image-31372

(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的结果,这就是分解后的效果。

wps_clip_image-9459

(Figure4:结构化XML返回列表)

wps_clip_image-12939

(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脚本查看错误日志的,效果是一样的。

wps_clip_image-19720

(Figure6:测试登陆)

wps_clip_image-14005

(Figure7:SQL跟踪事件AUDIT_LOGIN)

wps_clip_image-1394

(Figure8:ERRORLOG读取)

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

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

六.注意事项(Attention)

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

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

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

wps_clip_image-4192

(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.”

七.疑问(Questions)

1. “SQL 跟踪不会对与事务关联的性能造成负面影响。打包数据很有效。创建 XML 格式的事件数据和发送事件通知会对性能造成关联的负面影响。“这句需要怎么理解?

2. 使用CREATE EVENT NOTIFICATION创建的事件通知在SSMS哪里能找到呢?

解答:只能在动态视图sys.event_notifications和sys.server_event_notifications看到相关的创建信息;(依据:只能使用 Transact-SQL 语句创建事件通知)

3. 触发器必须在本地服务器上处理,事件通知可以在远程服务器上处理?如何实现?什么场景下可以使用?

4. 事件通知的SQL跟踪只能设置SERVER级别?不能设置DATABASE级别的?如果设置了DATABASE级别会报下面的错误信息:

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

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

解答:SQL 跟踪事件只能运行于服务器级 (ON SERVER)(依据:用于事件通知的跟踪事件

5. 为什么一次登陆会造成两条登陆信息的呢?下面的SQL语句会造成一次用户登陆,因为需要读取文件?

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

wps_clip_image-31448

(Figure10:登陆信息)

6. 队列是存储在什么地方?以什么的形式存储的?存储怎么保证消息的安全性(数据库宕机或者数据丢失等情况)?

解答:从下图看来,队列是存储在主文件组中的。

wps_clip_image-1943

(Figure11:队列属性)

八.参考文献(References)

事件通知

SQL Server 2008中新增的Service Broker事件通知

SQL Server 2008中Service Broker基础应用(上)

SQL Server 2008中Service Broker基础应用(下)

SQL Server 2008中远程Service Broker实现

CREATE EVENT NOTIFICATION (Transact-SQL)中文

sys.event_notification_event_types (Transact-SQL)

DDL 事件

用于事件通知的跟踪事件

DDL 事件组

作者:听风吹雨
出处: http://www.cnblogs.com/gaizai/
邮箱:gaizai@126.com
版权:本文版权归作者和博客园共有
转载:欢迎转载,必须保留原文链接
格言:不喜欢是因为不会 && 因为会所以喜欢