事件通知（Event Notification）实践

事件通知（EventNotification）实践 事件通知（EventNotification）实践

问题描述

作为DBA，我们经常需要在SQLServer实例或数据库级别跟踪正在发生的事件。有什么方法可以跟踪这些变化，而不会对SQLServer性能产生太大影响？

解决方案

SQLServer2005及其更高版本提供了一种事件通知机制来跟踪发生在数据库或实例级别的事件或修改。其实这也可以通过DDLtrigger或者这个SQLtrace来实现，但是事件通知具有异步对应事件和运行在事务范围之外的优点，所以可以作为数据库应用的一部分来捕获预定义的事件，而不会占用分配给事务的资源。

事件是使用DDL触发器或SQL跟踪的可编程替代方法。它执行不同的DDL语句、SQL跟踪和ServiceBroker事件(如QUEUE_ACTIVATION或BROKER_QUEUE_DISABLED)，然后以XML格式向SQLServerServiceBroker发送信息。换句话说，在创建通知时，SQLServer跟踪预定义的事件，将事件写入SSB服务，然后从SSB队列异步接收信息。

步骤一：

首先检查是否在数据库级别启用了ServiceBroker，如果没有，则启用它。然后创建一个SSB队列，SSB服务将使用它来存储发送给服务的消息。该服务使用内置的契约(该契约定义了可以发送到SSB服务的消息类型)，http://schemas.Microsoft.com/SQL/notifications/posteventnotification，专门用于事件通知。注意:要执行修改数据库命令，您需要独占访问数据。

--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

步骤二：

创建两个数据库级通知。首先，当执行createtable命令时，会发送一个通知；然后，当执行修改表格的命令时，将发送通知。您还可以创建通知事件组；例如，您可以创建一个通知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

步骤三：

创建一个服务器级通知，当在SQLServer的实例级触发错误时，将触发该通知。您可以检查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(WITHLOG子句需要被服务器级事件通知捕获)触发了SQLServer中的一个错误，这个错误会被最后一个服务器级事件通知捕获。

--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数据类型。因为我在RECEIVE命令中使用了TOP(1)子句，并且因为队列中有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.为了创建数据库级事件通知，您需要在此数据库中拥有createdatabaseDDLeventnotification权限。要删除它，您必须是事件通知的所有者，或者在数据库中拥有alteranydatabaseeventnotification权限。

2.为了创建服务器级通知，您需要拥有CREATEDDLEVENTNOTIFICATION权限。要删除它，您必须是事件通知的所有者，或者在服务器上拥有ALTERANYEVENTNOTIFICATION权限。

3.为了创建事件通知捕获SQL跟踪，您需要在此服务器中拥有CREATETRACEEVENTNOTIFICATION权限。要删除它，您必须是事件通知的所有者或拥有ALTERANYEVENTNOTIFICATION权限。

4.为了创建队列范围的事件通知，您需要对队列拥有ALTER权限。要删除它，您必须是事件通知的所有者或拥有队列的ALTER权限。

备注：

1.您可以查询sys.event_notification_event_types以获取可以创建事件通知的所有事件的列表，还可以查看DDL事件、DDL事件组、SQL跟踪事件和SQL跟踪事件组。

2.要了解事件通知和触发器之间的区别，可以访问此处；通知和SQL跟踪之间的区别可以在这里看到。

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