Page 109 - DCAP508_DATABASE_ADMINISTRATION

Unit 7: Automating Administrating Tasks

7.2.1 Understanding Event Notifications Notes

Event notifications execute in response to a variety of Transact-SQL data definition language
(DDL) statements and SQL Trace events by sending information about these events to a Service
Broker service.
Event notifications can be used to do the following:
Log and review changes or activity occurring on the database.

Perform an action in response to an event in an asynchronous instead of synchronous
manner.
Event notifications can offer a programming alternative to DDL triggers and SQL Trace.
Event notifications run asynchronously, outside the scope of a transaction. Therefore, unlike
DDL triggers, event notifications can be used inside a database application to respond to events
without using any resources defined by the immediate transaction.
Unlike SQL Trace, event notifications can be used to perform an action inside an instance of SQL
Server in response to a SQL Trace event.

When an event notification is created, one or more Service Broker conversations between an
instance of SQL Server and the target service you specify are opened. The conversations typically
remain open as long as the event notification exists as an object on the server instance. In some
error cases the conversations can close before the event notification is dropped. These
conversations are never shared between event notifications. Every event notification has its
own exclusive conversations. Ending a conversation explicitly prevents the target service from
receiving more messages, and the conversation will not reopen the next time the event notification
fires.
Event information is delivered to the Service Broker as a variable of type xml that provides
information about when an event occurs, about the database object affected, the Transact-SQL
batch statement involved, and other information.

Event data can be used by applications that are running together with SQL Server to track
progress and make decisions. For example, the following event notification sends a notice to a
certain service every time an ALTER TABLE statement is issued in the AdventureWorks sample
database.
USE AdventureWorks
GO

CREATE EVENT NOTIFICATION NotifyALTER_T1
ON DATABASE
FOR ALTER_TABLE

TO SERVICE ‘//Adventure-Works.com/ArchiveService’ ,
‘8140a771-3c4b-4479-8ac0-81008ab17984’;

7.2.2 Designing Event Notifications

To design an event notification, you must determine the following:
The scope of the notification.

The Transact-SQL statement, or group of statements, that raises the event notification.

LOVELY PROFESSIONAL UNIVERSITY 103