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

SQL Server 2008中SQL应用系列--目录索引

 

Service Broker的事件通知

　　事件通知是集成到Service Broker的功能，使用它可以在SQL Server实例中异步捕捉SQL事件，将事件消息路由到特定的队列中。只需最小的系统开销，就可以跟踪发生在SQL Server实例中的事件，比如用户登录、存储过程编译、权限修改、对象处理（例如对数据库、程序集、角色或表的Create/Alter/Drop事件）。

　　不像创建自己的Service Broker应用程序，因为可以控制发起方组件，使用事件通知后只需创建队列和Service Broker组件。用来捕捉和发送事件通知的发起方组件（消息类型和约定）已经内建在SQL Server中。

　　使用CREATE EVENT NOTIFICATION（http://msdn.microsoft.com/en-us/library/ms189453.aspx）

　　关于SQL Trace，相信很多人都用过Profiler,即SQL Server提供的查询跟踪器。关于SQL Trace及profiler（即事件跟踪器）的更多用法，请查看MSDN:http://msdn.microsoft.com/en-us/library/ms187929.aspx

　　[重要说明]：事件通知功能与SQL Trace相似，不同之处在于，它是异步的，并且对SQL Server实例整体性能影响最小。

　　下面是一个示例,演示了如何通过事件通知捕捉所有在SQL Server实例中执行的Create Login、Alter Login、和Drop Login命令。

/**************************downmoon 3w@live.cn***********************/

-- Capturing Login Commands
--创建示例数据库
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'EventTracking')
drop database EventTracking
GO
CREATE DATABASE EventTracking
GO

USE EventTracking
GO
--创建队列
Create QUEUE SQLEventQueue
WITH STATUS=ON;
GO

--创建服务,并关联到内建的事件通知约定
CREATE SERVICE [//AP4/TrackLoginModificationService]
ON QUEUE SQLEventQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

--对系统目录视图sys.databases进行查询
select service_broker_guid
from sys.databases
WHERE name = 'EventTracking'
/*
service_broker_guid
18FD2712-E551-4B6A-BC88-58E16D8D5BCD
*/

--在Server范围内创建事件通知来跟踪所有登录名的创建、修改和删除操作

----DROP EVENT NOTIFICATION EN_LoginEvents
----ON Server;

Create EVENT NOTIFICATION EN_LoginEvents
ON SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
TO SERVICE '//AP4/TrackLoginModificationService',
'18FD2712-E551-4B6A-BC88-58E16D8D5BCD';

--测试新的事件通知，创建一个登录名
----DROP login TrishelleN
----go

Create LOGIN TrishelleN WITH PASSWORD = 'AR!3i2ou4'
GO

--使用Select或Recieve(其中Recieve会删除队列中的事件消息)查询队列
SELECT CAST(message_body as xml) EventInfo
FROM dbo.SQLEventQueue

/*
<EVENT_INSTANCE>
<EventType>DROP_LOGIN</EventType>
<PostTime>2011-05-05T17:10:50.610</PostTime>
<SPID>52</SPID>
<ServerName>AP4\AGRONET09</ServerName>
<LoginName>AP4\Administrator</LoginName>
<ObjectName>TrishelleN</ObjectName>
<ObjectType>LOGIN</ObjectType>
<DefaultLanguage>us_english</DefaultLanguage>
<DefaultDatabase>master</DefaultDatabase>
<LoginType>SQL Login</LoginType>
<SID>ukcT55ooZECq0+bpBuvi1A==</SID>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>DROP login TrishelleN
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
*/ 

小结

　　本例演示了通过执行如下步骤来设置事件通知：

　　1、在已有数据库中创建新队列

　　2、创建绑定到新队列和内建事件通知约定的新服务

　　3、使用CREATE EVENT NOTIFICATION命令跟踪一个或多个事件或事件组

　　事件通知功能提供了跟踪SQL Server实例、数据库、或Service Broker应用程序级别的活动的低开销的方法。正如示例中所见，跟踪事件只需要极少量的代码。这个新功能对于IT安全或业务级别审计需求特别有用。例如，当捕获到登录名创建事件时，也会捕获到创建它的用户，以及登录名的类型（ＳＱＬ登录名）、默认数据库、语言和新登录名的安全标识符。