事件通知（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 itIF EXISTS (SELECT * FROM sys.databases WHERE name = ‘AdventureWorks2012’AND is_broker_enabled = 0)ALTER DATABASE AdventureWorks2012 SET ENABLE_BROKER;GOUSE AdventureWorks2012GO–Create a queue which will hold the tracked informationCREATE QUEUE dbo.EventNotificationQueueGO–Check if the queue is created or notSELECT * FROM sys.service_queuesWHERE name = ‘EventNotificationQueue’GO–Create a service on which tracked information will be sentCREATE SERVICE [//AdventureWorks2012/EventNotificationService]ON QUEUE dbo.EventNotificationQueue([])GO–Check if the service is created or notSELECT * FROM sys.servicesWHERE name = ‘//AdventureWorks2012/EventNotificationService’GO步骤二：

创建两个数据库级别的通知。首先，，当有一个创建表命令执行时，将发出通知；然后，当有一个修改表命令执行时，将发出通知。也可以创建一个通知事件组；例如，你可以创建一个单一的通知DDL_TABLE_EVENTS去跟踪像创建、修改、删

除表的所有事件。

–Create a notification to track create table commandCREATE EVENT NOTIFICATION NotifyCREATETABLEEventsON DATABASEFOR CREATE_TABLETO SERVICE ‘//AdventureWorks2012/EventNotificationService’ , ‘current database’GO–Create a notification to track alter table commandCREATE EVENT NOTIFICATION NotifyALTERTABLEEventsON DATABASEFOR ALTER_TABLETO SERVICE ‘//AdventureWorks2012/EventNotificationService’ , ‘current database’GO–Check if both the above notifications created or notSELECT * FROM sys.event_notificationsWHERE name IN (‘NotifyCREATETABLEEvents’,’NotifyALTERTABLEEvents’)GO步骤三：

创建一个服务器级别的通知，当一个错误在SQL Server实例级别触发时，该通知即被触发。可以查看sys.server_event_notifications目录视图查看通知是否存在于该服务器上。

–Create a notification to error occuring at server levelCREATE EVENT NOTIFICATION NotifyERROREventsON SERVER WITH FAN_INFOR ERRORLOGTO SERVICE ‘//AdventureWorks2012/EventNotificationService’, ‘current database’GO–Check if the above notification was created or notSELECT * FROM sys.server_event_notificationsWHERE name IN (‘NotifyERROREvents’)GO步骤四：

验证刚才建立的事件通知是否在正常工作。在这个脚本里，先创建一个表，然后修改它，它将会被数据库级别的事件通知捕获到，并且我使用了RAISERROR（WITH LOG从句需要被服务器级别事件通知捕获）在SQL Server里触发一个错误，该错误将会被上一个服务器级别的事件通知捕获到。

–Generate a create table eventCREATE TABLE ABC(COL1 INT,COL2 INT)GO–Generate an alter table eventALTER TABLE ABCADD COL3 INTGO–Generate a server level eventRAISERROR (N’Generating error for Event Notification testing…’, 16, 1)WITH LOGGO–Review if the events were tracked in queueSELECT CAST(message_body AS XML) AS message_in_xmlFROM dbo.EventNotificationQueueGO步骤五：

事件通知以XML格式发送捕获到的信息到SSB服务；可以查询队列去看到捕获的信息，但是你需要用RECEIVE命令从队列接收消息，如下所示，处理它们并从队列中移除。使用RECEIVE命令你可以设置在一次接受的记录的数量。在这个脚本中，我使用TOP (1)命令接受第一行队列中的消息，并显示它的内容。事件通知以XML格式发送消息（注意：我们使用创建服务的内置契约，它定义了只有XML数据能被写入到服务），因此我转换消息体到XML数据类型。因为我使用了TOP (1)从句在RECEIVE命令，因为队列中有3条记录，我运行了下面的命令3次。查询的结果如下图。也可以使用一个循环的结构去从队列读取所有的记录，而不用运行这个脚本多次。

喜欢 (0)赏

[搞代码]

分享 (0)