簡述Service Broker事件通知功能

事件通知是整合到Service Broker的功能，使用它可以在SQL Server執行個體中非同步捕捉SQL事件，將事件訊息路由到特定的隊列中。只需最小的系統開銷，就可以跟蹤發生在SQL Server執行個體中的事件，比如使用者登入、預存程序編譯、許可權修改、對象處理例如對資料庫、程式集、角色或表的Create/Alter/Drop事件）。

不像建立自己的Service Broker應用程式，因為可以控制發起方組件，使用事件通知後只需建立隊列和Service Broker組件。用來捕捉和發送事件通知的發起方組件訊息類型和約定）已經內建在SQL Server中。

使用CREATE EVENT NOTIFICATIONhttp://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安全或業務層級審計需求特別有用。例如，當捕獲到登入名稱建立事件時，也會捕獲到建立它的使用者，以及登入名稱的類型ＳＱＬ登入名稱）、預設資料庫、語言和新登入名稱的安全性識別碼。