一.本文所涉及的内容(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]