SQL Server 2005 - Using Event Notifications

SQL Server 2005 - Using Event Notifications

In this tutorial you will learn about SQL Server 2005 - Using Event Notifications, Creating Event Notifications, Creating an Event Notification, Creating the Target Service, Creating /Dropping / modifying the Event Notification and Using Metadata Views.

DDL statements or SQL Trace events execute Event Notifications. The information is sent to a Service Broker Service. These event notifications help in logging and reviewing changes occurring in databases. They perform action in response to an event in an asynchronous manner. They can be said to offer a programming alternative to DDL triggers and SQL Trace.

Event notifications can be used inside database applications to respond to events, operating outside the scope of the transaction. In this Event notification are different from DDL triggers.

Event notifications perform actions inside an SQL Server in response to a SQL Trace Event. In this they are different from SQL Trace.

Event notifications open one or more Service Broker conversations between the SQL Server and the target service. These conversations also continue so long as the event notification exists as an object in the server instance and are never shared between event notifications. The target service will cease to receive messages when the event notification ends and the target service will not be opened the next time the event notification is fired.

The variable type xml is used to deliver Event information to the Service Broker. The information specifies the details of the event, the database object impacted, the T-SQL statement and other required information.

Creating Event Notifications

While designing event notifications it is important for the DBA to define the scope of the notification and the type of T-SQL statement(s) that will fire the notification.

The event notification can be defined to respond to a statement made on the objects in a database or on all object on an instance of the SQL Server. Event notifications that can be scoped on individual queues are QUEUE_ACTIVATION and BROKER_QUEUE_DISABLED. CREATE _DATABASE events run only on server instance level while ALTER_TABLE event can run on all tables in the database.

An event notification can be designed to fire after a particular Transact-SQL statement is run or after a SQL Trace event belonging to a predefined grouping of similar trace events is run.

Creating an Event Notification:

An event notification involves creation of a target service to the event notification and the process of creating the event notification.

Creating the Target Service

1. Create a queue to receive messages.
2. Create a service on the queue that references the event notifications contract.
3. Create a route on the service to define the address to which Service Broker sends messages for the service. If the event notification targets a service in the same database, the DBA must specify ADDRESS = 'LOCAL'.

CREATE QUEUE ExforsysQueue ;
GO
CREATE SERVICE ExforsysService
ON QUEUE ExforsysQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO
CREATE ROUTE ExforsysRoute
WITH SERVICE_NAME = 'ExforsysService',
ADDRESS = 'LOCAL';
GO

Creating /Dropping / modifying the Event Notification

Transact-SQL CREATE EVENT NOTIFICATION statement is used for creating Event notifications. The Event notification can be dropped using the DROP EVENT NOTIFICATION STATEMENT. It can be modified by dropping and recreating it only.

Event notifications can be created on remote servers by defining routes on both the source and the target server for two way communication. It is necessary to enable the two instances of SQL Server to connect to each other by creating Service Broker end points on both instances.

Using Metadata Views

Information about views can be derived if the view is not encrypted. Views can be queried in the same way that tables are queried. However, any table hints will be ignored. Changing the name of the object referenced in a view will require a modification of the view to make the text reflect the new name. To get information about a view the following statements can be used.

sys.views (Transact-SQL)

sys.columns (Transact-SQL)

sp_helptext (Transact-SQL)

To view the data defined by a view

SELECT (Transact-SQL)

To display the dependencies of a view

sys.sql_dependencies (Transact-SQL)

Metadata of views can also be obtained by using Information Schema views. These views can be used to garner information regarding internal, system-table independent view of the SQL Server metadata. These views allow applications to work properly despite changes being made to the system tables. They conform to the SQL 92 standard definition for the INFORMATION_SCHEMA.

The SQL -92 standard supports three part naming convention just like SQL Server. However the naming convention in SQL Server 2005 is slightly different from SQL 92 and are defined in as special schema called INFORMATION_SCHEMA which contains metadata for all data objects stored in the database.

Some views contain references to different classes of data which include character data and binary data. The syntax for viewing metadata on views is as under:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM Exforsys.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Product';
GO

In this lesson we have examined in detail the monitoring tools of SQL Server 2005. In the lessons that follow we shall see how the DBA can maintain databases and indexes.