事件通知（Event Notification）实践
问题描述
作为DBA，我们常常需要在SQL Server实例或数据库级别上跟踪正在发生的事件。有没有方法跟踪这些修改而不会太影响SQL Server性能呢？
解决方案
SQL Server 2005及其以后版本提供了事件通知（Event Notification）机制，来跟踪发生在数据库或实例级别上的事件或修改。这其实也可以通过DDL触发器或这SQL跟踪来实现，但是事件通知有异步相应事件和运行在事务范围之外的优点，因此能作为数据库应用程序的一部分，捕获预定义的事件，而无需占用分配给事务的资源。
事件通知是一个使用DDL触发器或SQL跟踪的可编程的替代方案，执行相应不同的DDL语句、SQL跟踪、Service Broker事件（像QUEUE_ACTIVATION或BROKER_QUEUE_DISABLED），然后以XML格式发送信息到SQL Server Service Broker服务。换句话说，当创建一个通知，SQL Server跟踪预定义的事件，并将发生的事件写入到SSB服务，然后异步地从SSB队列接收信息。
步骤一：
首先检查Service Broker是否在数据库级别被启用，如果没有，启用它。然后创建一个SSB队列，SSB服务将会用这个队列去存储消息到服务。该服务使用内置的契约（契约定义了一个能发送到SSB服务的消息类型），http://schemas.microsoft.com/SQL/Notifications/PostEventNotification，专用于事件通知（Event Notification）。注意：你需要排他访问数据去执行修改数据库命令。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
--Check if the database is enabled for Service Broker
--If not then enable it
IF EXISTS (
SELECT
*
FROM
sys.databases
WHERE
name
=
'AdventureWorks2012'
AND
is_broker_enabled = 0)
ALTER
DATABASE
AdventureWorks2012
SET
ENABLE_BROKER;
GO
USE AdventureWorks2012
GO
--Create a queue which will hold the tracked information
CREATE
QUEUE dbo.EventNotificationQueue
GO
--Check if the queue is created or not
SELECT
*
FROM
sys.service_queues
WHERE
name
=
'EventNotificationQueue'
GO
--Create a service on which tracked information will be sent
CREATE
SERVICE [//AdventureWorks2012/EventNotificationService]
ON
QUEUE dbo.EventNotificationQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
--Check if the service is created or not
SELECT
*
FROM
sys.services
WHERE
name
=
'//AdventureWorks2012/EventNotificationService'
GO
|
步骤二：
创建两个数据库级别的通知。首先，当有一个创建表命令执行时，将发出通知；然后，当有一个修改表命令执行时，将发出通知。也可以创建一个通知事件组；例如，你可以创建一个单一的通知DDL_TABLE_EVENTS去跟踪像创建、修改、删除表的所有事件。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
--Create a notification to track create table command
CREATE
EVENT NOTIFICATION NotifyCREATETABLEEvents
ON
DATABASE
FOR
CREATE_TABLE
TO
SERVICE
'//AdventureWorks2012/EventNotificationService'
,
'current database'
GO
--Create a notification to track alter table command
CREATE
EVENT NOTIFICATION NotifyALTERTABLEEvents
ON
DATABASE
FOR
ALTER_TABLE
TO
SERVICE
'//AdventureWorks2012/EventNotificationService'
,
'current database'
GO
--Check if both the above notifications created or not
SELECT
*
FROM
sys.event_notifications
WHERE
name
IN
(
'NotifyCREATETABLEEvents'
,
'NotifyALTERTABLEEvents'
)
GO
|
步骤三：
创建一个服务器级别的通知，当一个错误在SQL Server实例级别触发时，该通知即被触发。可以查看sys.server_event_notifications目录视图查看通知是否存在于该服务器上。
|
1
2
3
4
5
6
7
8
9
10
|
--Create a notification to error occuring at server level
CREATE
EVENT NOTIFICATION NotifyERROREvents
ON
SERVER
WITH
FAN_IN
FOR
ERRORLOG
TO
SERVICE
'//AdventureWorks2012/EventNotificationService'
,
'current database'
GO
--Check if the above notification was created or not
SELECT
*
FROM
sys.server_event_notifications
WHERE
name
IN
(
'NotifyERROREvents'
)
GO
|
步骤四：
验证刚才建立的事件通知是否在正常工作。在这个脚本里，先创建一个表，然后修改它，它将会被数据库级别的事件通知捕获到，并且我使用了RAISERROR（WITH LOG从句需要被服务器级别事件通知捕获）在SQL Server里触发一个错误，该错误将会被上一个服务器级别的事件通知捕获到。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
--Generate a create table event
CREATE
TABLE
ABC
(
COL1
INT
,
COL2
INT
)
GO
--Generate an alter table event
ALTER
TABLE
ABC
ADD
COL3
INT
GO
--Generate a server level event
RAISERROR (N
'Generating error for Event Notification testing...'
, 16, 1)
WITH
LOG
GO
--Review if the events were tracked in queue
SELECT
CAST
(message_body
AS
XML)
AS
message_in_xml
FROM
dbo.EventNotificationQueue
GO
|
步骤五：
事件通知以XML格式发送捕获到的信息到SSB服务；可以查询队列去看到捕获的信息，但是你需要用RECEIVE命令从队列接收消息，如下所示，处理它们并从队列中移除。使用RECEIVE命令你可以设置在一次接受的记录的数量。在这个脚本中，我使用TOP (1)命令接受第一行队列中的消息，并显示它的内容。事件通知以XML格式发送消息（注意：我们使用创建服务的内置契约，它定义了只有XML数据能被写入到服务），因此我转换消息体到XML数据类型。因为我使用了TOP (1)从句在RECEIVE命令，因为队列中有3条记录，我运行了下面的命令3次。查询的结果如下图。也可以使用一个循环的结构去从队列读取所有的记录，而不用运行这个脚本多次。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
DECLARE
@TargetDialogHandle UNIQUEIDENTIFIER;
DECLARE
@EventMessage XML;
DECLARE
@EventMessageTypeName sysname;
WAITFOR
( RECEIVE
TOP
(1)
@TargetDialogHandle = conversation_handle,
@EventMessage =
CONVERT
(XML, message_body),
@EventMessageTypeName = message_type_name
FROM
dbo.EventNotificationQueue
), TIMEOUT 1000;
SELECT
@TargetDialogHandle
AS
DialogHandle, @EventMessageTypeName
AS
MessageTypeName,
@EventMessage.value(
'(/EVENT_INSTANCE/EventType)[1]'
,
'varchar(128)'
)
as
EventType,
@EventMessage.value(
'(/EVENT_INSTANCE/ServerName)[1]'
,
'varchar(128)'
)
as
ServerName,
@EventMessage.value(
'(/EVENT_INSTANCE/DatabaseName)[1]'
,
'varchar(128)'
)
as
DatabaseName,
@EventMessage.value(
'(/EVENT_INSTANCE/LoginName)[1]'
,
'varchar(128)'
)
as
LoginName,
@EventMessage.value(
'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'
,
'nvarchar(max)'
)
AS
TSQLCommand,
@EventMessage.value(
'(/EVENT_INSTANCE/TextData)[1]'
,
'varchar(128)'
)
AS
TextData,
@EventMessage.value(
'(/EVENT_INSTANCE/Severity)[1]'
,
'varchar(128)'
)
AS
Severity,
@EventMessage.value(
'(/EVENT_INSTANCE/Error)[1]'
,
'varchar(128)'
)
AS
ErrorNumber
|
步骤六：
下面的代码可以清理并删除所有的对象，以创建的相反顺序。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
DROP
EVENT NOTIFICATION NotifyCREATETABLEEvents
ON
DATABASE
GO
DROP
EVENT NOTIFICATION NotifyALTERTABLEEvents
ON
DATABASE
GO
DROP
EVENT NOTIFICATION NotifyERROREvents
ON
SERVER
GO
DROP
TABLE
ABC
GO
DROP
SERVICE [//AdventureWorks2012/EventNotificationService]
GO
DROP
QUEUE dbo.EventNotificationQueue
GO
|
权限需求：
1. 为了创建一个数据库级别的事件通知，需要在该数据库有CREATE DATABASE DDL EVENT NOTIFICATION权限。为了删除它，你必需是该事件通知的拥有者，或者在该数据库有ALTER ANY DATABASE EVENT NOTIFICATION权限。
2. 为了创建服务器级别的通知，你需要有CREATE DDL EVENT NOTIFICATION权限。为了删除它，你必需是该事件通知的拥有者，或者在该服务器有ALTER ANY EVENT NOTIFICATION权限。
3. 为了创建事件通知捕获SQL跟踪，你需要在该服务器有CREATE TRACE EVENT NOTIFICATION权限。为了删除它，你必需是该事件通知的拥有者，或者有ALTER ANY EVENT NOTIFICATION权限。
4. 为了创建队列范围的事件通知，你需要有该队列的ALTER权限。为了删除它，你必需是该事件通知的拥有者，或者有该队列的ALTER权限。
备注：
1. 你可以查询sys.event_notification_event_types获取所有可以创建事件通知的事件的列表，也可以查看DDL事件，DDL事件组，SQL跟踪事件，SQL跟踪事件组。
2. 去查看事件通知和触发器之间的不同，可以访问这里；事件通知和SQL跟踪的不同，可以访问这里。
3. 你不能直接修改一个通知，你需要删除并重建它。
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1592504 ，如需转载请自行联系原作者
