Event notification (Notification) Introduction

Incident Notification (event Notification ) Introduction

Understanding Event Notifications

Event notifications are special types of database objects that are used to send information about server and database events to service Broker services.

Performing event notifications responds to various Transact-SQL data Definition Language (DDL) statements and SQL trace events by sending information about these events to the service Broker services.

Event notifications can be used to perform the following actions:

Records and retrieves changes or activities that occur on the database.

Performs an action that responds asynchronously rather than synchronously to an event.

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://s3.51cto.com/wyfs02/M02/57/26/wKiom1SSkzXiGezYAANBdoBneQ4494.jpg "border=" 0 "height=" 725 "/>

Event notifications can be used as a programmatic way to override DDL triggers and SQL traces.

Event notifications run asynchronously outside the scope of a transaction. Therefore, unlike DDL triggers, event notifications can be used in database applications to respond to events without using any resources defined by an intermediary transaction.

Unlike SQL tracing, event notifications can be used to perform operations inside an instance of SQL Server in response to SQL Trace events.

When you create an event notification, one or more service Broker sessions are opened between the SQL Server instance and the specified target service. Normally the session remains open as long as the event notification exists as an object in the server instance. In some cases of error, the session can be closed before the event notification is deleted. These sessions are never shared between event notifications. Each event notification has its own exclusive session. An explicit end session will prevent the target service from receiving more messages, and the next time the event notification fires, the session will not reopen.

Event information is passed to Service Broker as an XML type variable, providing information about when the event occurred, the affected database object, the Transact-SQL batch statement involved, and other information.

Applications that run with SQL Server can use event data to track progress and make decisions.

Design Event Notifications

To design event notifications, you must determine the following:

The scope of the notification.

The Transact-SQL statement or statement group that raised the event notification.

Defining notification Scopes

You can specify event notifications to respond to statements that are executed against all objects in the current database or in an instance of SQL Server. The scope of the event notification specified for the queue_activation and broker_queue_disabled events is scoped to a single queue. Not all events can be performed at any scope level. For example, the Create_database event can only be performed at the server instance level. Instead, event notifications created for the Alter_table event can be programmed to execute against all tables in the database or in the server instance.

Specify a Transact-SQL statement or statement group

You can create event notifications in response to the following:

Specific DDL statements, SQL trace events, or Service Broker events

Pre-defined DDL statement group or SQL trace Event Group

To select a specific DDL statement to raise an event notification

Event notifications can be designed to execute after a specific Transact-SQL statement or stored procedure is run. As shown in the example above, event notifications are executed after the Alter_table event.

For a list of the individual Transact-SQL statements that can be specified to raise event notifications, and the scope in which these notifications can be executed, see DDL events. You can also get these events by querying the Sys.event_notification_event_types catalog view.

To select a specific SQL trace event to raise an event notification

Event notifications can be designed to fire after a SQL trace event occurs.

You can get a list of these events by querying the Sys.event_notification_event_types catalog view. SQL Trace events can only be performed at the server instance scope level.

To select a specific 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 the queue has a message to process. The Broker_queue_disabled event occurs when the queue status is set to OFF.

Select a predefined set of DDL statements to raise event notifications

Event notifications can be executed after any Transact-SQL events that are part of a predefined group of similar events are run. For example, if you want the event notification to execute after the CREATE TABLE, ALTER table, or DROP table statement executes, you can specify for ddl_table_events in the Create Event NOTIFICATION statement. After the CREATE event NOTIFICATION is executed, the event group is added to the Sys.events catalog view.

Select predefined SQL trace event groups to raise event notifications

Event notifications can be executed after any SQL trace events that are part of a predefined similar tracking event group are run. For example, if you want event notifications to be executed after any lock-related trace events, including the Lock_deadlock, Lock_deadlock_chain, lock_escalation, and deadlock_graph events, you can do so in the CREATE The for trc_locks is specified in the EVENT NOTIFICATION statement.

Implementing event Notifications

To implement event notifications, you must first create a target service to receive event notifications, and then create event notifications.

Create a target service

There is no need to create Service Broker startup services because Service Broker contains the following specific event notification message types and conventions:

Http://schemas.microsoft.com/SQL/Notifications/PostEventNotification

The target service that receives the event notification must use this pre-existing convention.

To create a target service:

Create a queue to receive messages.

Create a service on the queue that references the event notification contract.

Create a service route to define the address to which service Broker sends the services message. For event notifications that point to services in the same database, specify ADDRESS = ' LOCAL '.

Creating event Notifications

Event notifications are created using the Transact-SQL CREATE Event NOTIFICATION statement, which is deleted using the drop event NOTIFICATION STATEMENT. To modify event notifications, you must first delete the event notification and then recreate it.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1591452

Event notification (Notification) Introduction