Page 111 - DCAP508_DATABASE_ADMINISTRATION

Unit 7: Automating Administrating Tasks

ON SERVER Notes
FOR Object_Created
TO SERVICE ‘//Adventure-Works.com/ArchiveService’, ‘current database’ ;
SQL Trace events can be executed only at the server instance scope.

Selecting a Service Broker Event to Raise an Event Notification

Event notifications can be designed to fire after a QUEUE_ACTIVATION or
BROKER_QUEUE_DISABLED Service Broker event. The QUEUE_ACTIVATION event occurs
when a queue has messages to process. The BROKER_QUEUE_DISABLED event occurs when the
status of a queue is set to OFF.

Selecting a Predefined Group of DDL Statements to Raise an Event Notification

An event notification can occur after any Transact-SQL event that belongs to a predefined grouping
of similar events is run. For example, if you want an event notification to occur after any
CREATE TABLE, ALTER TABLE, or DROP TABLE statement is executed, you can specify FOR
DDL_TABLE_EVENTS in the CREATE EVENT NOTIFICATION statement. After CREATE EVENT
NOTIFICATION executes, the events that are covered by an event group are added to the sys.events
catalog view.

For a list of the predefined groups of DDL and DML statements that are available for event
notifications, the particular statements they cover, and the scope at which these event groups can
execute, see DDL Event Groups for Use with Event Notifications.

Selecting a Predefined Group of SQL Trace Events to Raise an Event Notification

An event notification can occur after any SQL Trace event that belongs to a predefined grouping
of similar trace events is run. For example, if you want an event notification to occur after any
locking-related trace event, which includes the LOCK_DEADLOCK,
LOCK_DEADLOCK_CHAIN, LOCK_ESCALATION, and DEADLOCK_GRAPH events, you can
specify FOR TRC_LOCKS in the CREATE EVENT NOTIFICATION statement.
For a list of the predefined groups of SQL Trace events that are available for event notifications,
see Trace Event Groups for Use with Event Notifications. These groups can execute only at the
server instance level.
Important
Service Broker dialog security should be configured for event notifications that send messages
to a service broker on a remote server. Dialog security must be configured manually according
to the full security model.

Creating the Target Service

You do not have to create a Service Broker-initiating service because Service Broker includes the
following specific message type and contract for event notifications:
http://schemas.microsoft.com/SQL/Notifications/PostEventNotification
The target service that receives event notifications must honor this preexisting contract.

LOVELY PROFESSIONAL UNIVERSITY 105