事件通知（Event Notification）实践

事件通知（EventNotification）实践问题描述作为DBA，我们常常需要在SQLServer实例或数据库级别上跟踪正在发生的事件。有没有方法跟踪这些修改而不会太影响SQLSe

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