Sql | Event Notifications
Event Notifications concept introduced in SQL Server 2005, offer a versatile and effective method of capturing and managing internal SQL Server events.
Instead of using DDL triggers or SQL Trace to record details about the event being fired, event notifications run for DDL Events and Trace Events inside the database engine.
Event Notifications use Service Broker to asynchronously capture event information, in contrast to DDL triggers and SQL Trace, which process synchronously.
Step1: Create Database and Enable Service Broker on It.
Step 2: Create queue to hold the messages
Event Notifications utilize a Service Broker Queue for holding messages generated by firing events.
The queue is similar to a database table that holds a row for each message that has been added to the queue.
Lets create a queue to hold the messages using CREATE QUEUE DDL command:
Step 3: Create a service broker service
A Service Broker Service manages the task of transmitting messages to a queue based on a defined contract that defines the messages being transmitted.
Service Broker has a built-in existing contract for Event Notifications that is used for defining a service to transmit messages generated by Event Notifications.
Step 4: Event Notification on the service
Event Notification will use the Service to process messages for an Event. In this example we'll use the ERRORLOG Trace Event to create our Event Notification.
Step 5: A Service Broker Application
Service Broker is a system for sending and receiving messages that are typed and contain information or data.
Enable Service Broker on the database, A Service Borker contain the broker components.
By default msdb has Service Broker enabled on it. However, creating new objects in msdb or any of the system databases is not generally considered a best practice.
Steps 6: Lets test our Event Notification functionality
intentionally raise an erorr RAISERROR. Use the WITH LOG option to send the error to the ErrorLog:
Step 7: Let check EventNotificationQueue
It contains the EventNotification message for the ErrorLog Event.
Fetch result more Precisely
Step 8: Automatically process the queue
automatically process the queue using activation. Activation requires an activation stored procedure that is executed when new messages are added to the queue. We can send the error on email as well.
To do this requires that the stored procedure be signed with a certificate following the instruction in my last article - Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail.
Step 9: Signed Procedure with a certificate
After creating the activation procedure, the procedure must be signed with a certificate and the Service Broker Queue must be updated so that the stored procedure is activated when a new message is added to the queue. If a certificate login already exists for use with Database Mail, the same certificate can be used to sign the activation stored procedure
To test the new activation stored procedure out, execute the RAISERROR that was previously used to test the Queue, and then query the ErrorLogEvents table.