Sql Server

Sql | Event Notifications

by Deepak Talwar
September 5, 2023
0 Comments
10 minute read
149 Views
0 Likes

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.

-- Create a new Database
CREATE DATABASE [EventNotifications]

-- Enable Broder
ALTER DATABASE [EventNotifications]
SET ENABLE_BROKER;
GO
USE [EventNotifications]
GO

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:

CREATE QUEUE EventNotificationQueue

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.

CREATE SERVICE EventNotificationService
 ON QUEUE EventNotificationQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

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.

-- Create the event notification for ERRORLOG trace events on the service
CREATE EVENT NOTIFICATION CaptureErrorLogEvents
 ON SERVER
 WITH FAN_IN
 FOR ERRORLOG
 TO SERVICE 'EventNotificationService', 'current database';
GO

Step 5: A Service Broker Application
Service Broker is a system for sending and receiving messages that are typed and contain information or data.
Pre-quisites
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.

-- Query the catalog to see the queue
SELECT *
FROM sys.service_queues
WHERE name = 'EventNotificationQueue';
GO
-- Query the catalog to see the service
SELECT *
FROM sys.services
WHERE name = 'EventNotificationService';
GO
-- Query the catalog to see the notification
SELECT * 
FROM sys.server_event_notifications 
WHERE name = 'CaptureErrorLogEvents';
GO

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:

RAISERROR (N'Test ERRORLOG Event Notifications', 10, 1) WITH LOG;

Step 7: Let check EventNotificationQueue
It contains the EventNotification message for the ErrorLog Event.

SELECT *
FROM EventNotificationQueue


-- Cast message_body to XML
SELECT CAST(message_body AS XML) AS message_body_xml
FROM EventNotificationQueue



-- Can use inside our Sproc
DECLARE @message_body xml;
-- Receive the next available message FROM the queue
RECEIVE TOP(1) -- just handle one message at a time
 @message_body=message_body
 FROM EventNotificationQueue;

SELECT @message_body;

Fetch result more Precisely

-- Declare the table variable to hold the XML messages
DECLARE @messages TABLE
( message_data xml );

-- Receive all the messages for the next conversation_handle from the queue into the table variable
RECEIVE cast(message_body as xml)
FROM EventNotificationQueue
INTO @messages;

-- Parse the XML from the table variable
SELECT 
 message_data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)' ) as EventType,
 message_data.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)') AS PostTime,
 message_data.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(128)' ) AS TextData,
 message_data.value('(/EVENT_INSTANCE/Severity)[1]', 'varchar(128)' ) AS Severity,
 message_data.value('(/EVENT_INSTANCE/Error)[1]', 'varchar(128)' ) AS Error
FROM @messages;

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.

CREATE PROCEDURE [dbo].[ProcessEventNotifications]
WITH EXECUTE AS OWNER
AS 
    SET NOCOUNT ON
    DECLARE @message_body xml 
    DECLARE @email_message nvarchar(MAX)
    
    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION
        -- Receive the next available message FROM the queue
        WAITFOR (
            RECEIVE TOP(1) -- just handle one message at a time
            @message_body=message_body
            FROM dbo.EventNotificationQueue
        ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away

        -- If we didn't get anything, bail out
        IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            BREAK
        END 

        IF (@message_body.value('(/EVENT_INSTANCE/Severity)[1]', 'int' ) > 10) -- Error is not Informational
        BEGIN
            -- Generate formatted email message
            SELECT @email_message = 'The following event was logged in the SQL Server ErrorLog:' + CHAR(10) +
            'PostTime: ' + @message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)') + CHAR(10) +
            'Error: ' + @message_body.value('(/EVENT_INSTANCE/Error)[1]', 'varchar(20)' )  + CHAR(10) +
            'Severity: ' + @message_body.value('(/EVENT_INSTANCE/Severity)[1]', 'varchar(20)' ) + CHAR(10) +
            'TextData: ' + @message_body.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(4000)' );

            -- Send email using Database Mail
            EXEC msdb.dbo.sp_send_dbmail
                @profile_name = 'SQL Monitor', -- your defined email profile 
                @recipients = 'info@thetechfoyer.com', -- your email
                @subject = 'SQL Server Error Log Event',
                @body = @email_message;

        END

        --  Commit the transaction.  At any point before this, we could roll 
        --  back - the received message would be back on the queue AND the response
        --  wouldn't be sent.
        COMMIT TRANSACTION
    END
GO

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

ALTER QUEUE EventNotificationQueue
WITH 
 ACTIVATION -- Setup Activation Procedure
(STATUS=ON,
 PROCEDURE_NAME = [ProcessEventNotifications],  -- Procedure to execute
 MAX_QUEUE_READERS = 1, -- maximum concurrent executions of the procedure
 EXECUTE AS OWNER) -- account to execute procedure under
GO

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.

-- Test the Event Notification by raising an Error
RAISERROR (N'Test ERRORLOG Event Notifications', 10, 1)WITH LOG;
-- View Queue Contents
SELECT *
FROM EventNotificationQueue;

Deepak Talwar

Technical Architect and Full Stack Developer with 18+ years of Professional Experience in Microsoft Technologies & PHP Platform. Hands on experience with C#, VB, ASP.NET, ASP.NET MVC, ASP.NET Core, ASP.NET Web API, Linq, ADO.NET, Entity Framework, Entity Framework Core, Sql Server, MYSQL, NoSql, Javascript, Angular, jQuery, AWS, Azure, React, Angular, Laravel, Codeingiter, Serenity, VBA, Cloud Computing, Microservices, Design Patterns, Software Architecture, Web Design and Development.