Event Notification in sql server to track the change with example

Similar to DDL trigger, event notification also execute in response to transact sql ddl statements as well as sql trace events by sending information about these events to a service broker service.

Event notification

Event notification can be used to log and review changes or activity occurring on the database, and they respond to the event in an asynchronous style.

Due to the asynchronous nature of event notifications, they run outside the scope of a transaction. This allows the event notification to be used within the database allocation and to respond to an event without the use of resources specifically designated by that immediate transaction.

Event notifications can be processed on a remote server, and the consumer of an event notification is not coupled with the event that causes it to fire. In contrast, DDL triggers responding to DDL events have to be processed on the local server and are tightly coupled with the transaction that caused them to fire.

When an event notification is created, a dialogue is opened between an instance of sql server, the target service you have specified, and any number of service brokers.  These conversations are not shared with other event notifications because they have their own exclusive conversations. As long as the event notification exists as an object on the server instance, these conversations will continue to remain open.

Event information is delivered to the service broker as a variable of type xml.

It contains information about the event that occurred, the database objects affected, and the t-sql ddl statements involved.

To create an event notification, you must first create the target service that will receive the event notifications. It is not necessary to create the service broker initiating service because the service broker includes the specific message type and construct for event notifications.

The target service must honor this re-existing contract.

http://schemas.microsoft.com/sql/notifications/posteventnotification

when creating target service, you must create a queue to receive the event notification messages creates service and attach it to the message queue that references the event notification contract

Create route on the service to specify the address to which the service broker sends messages

Example

USE Sales

CREATE QUEUE EventNotificationQueue

— create a service and specifies the queue that will receive messages and honors the re-existing contract

Create service EventNotiifcationService On queue EventNotificationQueue(

[http://schemas.microsoft.com/sql/notification/postEventNotification]

)

— create a route to a local service in the sales database.

Use sales

Create route EventNotificationRoute WITH SERVICE_NAME = ‘EventNotiifcationService ‘, ADDRESS=’LOCAL’

CREATE EVENT Notification DroptableNotification

ON DATABASE

FOR DROP_TABLE

TO SERVICE ‘EventNotiifcationService ‘,’TESTDB’

Leave a Reply

  

  

  


8 + = 10