Event Notification practice

Event Notification (EventNotification) Practice Problem description as DBA, we often need to track ongoing events at the SQLServer instance or database level. Is there a way to track these changes without affecting SQL server performance too much? Solution SQLServer2005 and later versions provide Event Notification (EventNotification

Event Notification as a DBA, we often need to track ongoing events at the SQL Server instance or database level. Is there a way to track these changes without affecting SQL Server performance too much? Solution: SQL Server 2005 and later versions provide Event Notification

Event Notification ) Practice

Problem description

As a DBA, we often need to track ongoing events at the SQL Server instance or database level. Is there a way to track these changes without affecting SQL Server performance too much?

Solution

SQL Server 2005 and later versions provide Event Notification mechanisms to track events or modifications that occur at the database or instance level. This can also be achieved through DDL triggers or SQL tracking, but Event Notification has the advantages of asynchronous corresponding events and running beyond the scope of transactions, so it can be part of the database application, capture predefined events without occupying the resources allocated to the transaction.

Event Notification is a programmable alternative to DDL triggers or SQL tracking. It executes different DDL statements, SQL traces, and Service Broker events (such as QUEUE_ACTIVATION or BROKER_QUEUE_DISABLED ), then, information is sent to the SQL Server Service Broker Service in XML format. In other words, when a notification is created, SQL Server tracks predefined events, writes events to the SSB service, and then asynchronously receives information from the SSB queue.

Step 1:

First, check whether the Service Broker is enabled at the database level. If not, enable it. Then create an SSB queue, which will be used by the SSB service to store messages to the service. The Service uses the built-in contract (the contract defines a message type that can be sent to the SSB Service), http://schemas.microsoft.com/ SQL /icationications/posteventication, dedicated to Event Notification ). Note: You need to exclusive access to the data to execute the modify Database Command.

--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([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])GO--Check if the service is created or notSELECT * FROM sys.servicesWHERE name = '//AdventureWorks2012/EventNotificationService'GO

Step 2:

Create two database-level notifications. First, a notification will be sent when a table creation command is executed. Then, a notification will be sent when a command to modify the table is executed. You can also create a notification event group. For example, you can create a single notification DDL_TABLE_EVENTS to track all events such as creating, modifying, and deleting tables.

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

Step 3:

Create a Server-level notification. When an error is triggered at the SQL Server instance level, this notification is triggered. You can view the sys. server_event_notifications directory view to check whether the notification exists on the server.

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

Step 4:

Verify that the event notification just created is working properly. In this script, create a table and modify it. It will be captured by database-level event notifications, in addition, I used RAISERROR (with log clause needs to be captured by Server-Level Event Notifications) to trigger an error in SQL Server. This error will be captured by the previous Server-level event notification.

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

Step 5:

The Event Notification sends the captured information to the SSB service in XML format. You can query the queue to see the captured information, but you need to use the RECEIVE command to RECEIVE messages from the queue, as shown below, process them and remove them from the queue. You can use the RECEIVE command to set the number of records accepted at a time. In this script, I use the TOP (1) command to accept messages in the first line of the queue and display their contents. Event Notifications send messages in XML format (note: we use the built-in contract for creating a service, which defines that only XML data can be written to the service ), therefore, I want to convert the message body to the XML data type. Because I used the TOP (1) clause in the RECEIVE command, because there are 3 records in the queue, I ran the following command three times. The query result is as follows. You can also use a circular structure to read all records from the queue without running this script multiple times.

DECLARE @TargetDialogHandle UNIQUEIDENTIFIER;DECLARE @EventMessage XML;DECLARE @EventMessageTypeName sysname;WAITFOR( RECEIVE TOP(1)@TargetDialogHandle = conversation_handle,@EventMessage = CONVERT(XML, message_body),@EventMessageTypeName = message_type_nameFROM 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

650) this. width = 650; "title =" clip_image001 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image001 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121A34R9-0.jpg "border =" 0 "height =" 49 "/>

650) this. width = 650; "title =" clip_image002 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image002 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121A341R-1.jpg "border =" 0 "height =" 48 "/>

650) this. width = 650; "title =" clip_image003 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image003 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121A31948-2.jpg "border =" 0 "height =" 49 "/>

Step 6:

The following code clears and deletes all objects in the reverse order of creation.

DROP EVENT NOTIFICATION NotifyCREATETABLEEvents ON DATABASEGODROP EVENT NOTIFICATION NotifyALTERTABLEEvents ON DATABASEGODROP EVENT NOTIFICATION NotifyERROREvents ON SERVERGODROP TABLE ABCGODROP SERVICE [//AdventureWorks2012/EventNotificationService]GODROP QUEUE dbo.EventNotificationQueueGO

Permission requirements:

1. To CREATE a DATABASE-Level event notification, you must have the create database ddl event notification permission on the DATABASE. To delete the EVENT, you must be the owner of the event notification or have the alter any database event notification permission in the DATABASE.

2. To CREATE server-level notifications, you must have the create ddl event notification permission. To delete it, you must be the owner of the event notification or have the alter any event notification permission on the server.

3. To CREATE an event notification capture SQL TRACE, you must have the CREATE TRACE EVENT NOTIFICATION permission on the server. To delete it, you must be the owner of the event notification or have the alter any event notification permission.

4. To create event notifications within the queue range, you must have the ALTER permission for this queue. To delete it, you must be the owner of the event notification or have the ALTER permission for the queue.

Note:

1. You can query sys. event_icationication_event_types to obtain a list of all events that can create Event Notifications. You can also view DDL events, DDL event groups, SQL tracking events, and SQL tracking event groups.

2. Check the differences between Event Notifications and triggers. You can access them here. For differences between Event Notifications and SQL tracking, visit here.

3. You cannot directly modify a notification. You need to delete and recreate it.