事件通知（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服务的消息类型），，专用于事件通知（Event Notification）。注意：你需要排他访问数据去执行修改数据库命令。

                         
--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去跟踪像创建、修改、删除表的所有事件。

                         
--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目录视图查看通知是否存在于该服务器上。

                         
--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里触发一个错误，该错误将会被上一个服务器级别的事件通知捕获到。

                         
--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次。查询的结果如下图。也可以使用一个循环的结构去从队列读取所有的记录，而不用运行这个脚本多次。

                         
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
        
      

步骤六：

下面的代码可以清理并删除所有的对象，以创建的相反顺序。

                         
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获取所有可以创建事件通知的事件的列表，也可以查看，，，。

2. 去查看事件通知和触发器之间的不同，可以访问；事件通知和SQL跟踪的不同，可以访问。

3. 你不能直接修改一个通知，你需要删除并重建它。

本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1592504 ，如需转载请自行联系原作者