SQL: Event Notifications

It is an alternative to DDL triggers and SQL Trace.


When a DDL statement runs or a SQL trace event occurs, it sends the related information to Service Broker service.

Service Broker includes a message type and contract specifically for event notifications. The Service Broker has defined the contract

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

Assuming the AdventureWorks database will receive the messages.

ALTER DATABASE AdventureWorks
SET ENABLE_BROKER

/*If you got the error message "An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 11. Could not obtain information about Windows NT group/user 'QUEEN\Administrator', error code 0x534.", run the following statement.*/
ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO [sa];

USE AdventureWorks
GO
CREATE QUEUE NotifyQueue ;
GO
CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
CREATE ROUTE NotifyRoute
WITH SERVICE_NAME = 'NotifyService',
ADDRESS = 'LOCAL';
GO

--Find out the service_broker_guid
SELECT name,service_broker_guid,is_broker_enabled FROM sys.databases WHERE database_id=DB_ID('AdventureWorks')

name:AdventureWorks

service_broker_guid: 93576EF7-2297-4F84-9842-4E6854D3FD10

is_broker_enabled: 1

--Create the server-scoped notifications
CREATE EVENT NOTIFICATION DropDatabaseNotification
ON SERVER
FOR DROP_DATABASE
TO SERVICE 'NotifyService', '93576EF7-2297-4F84-9842-4E6854D3FD10';

CREATE EVENT NOTIFICATION CreateDatabaseNotification

ON SERVER

FOR DROP_DATABASE

TO SERVICE 'NotifyService', '93576EF7-2297-4F84-9842-4E6854D3FD10';

--Create a testing database: bData

CREATE DATABASE bData;

--Create the database-scoped notification

USE bData

GO
CREATE EVENT NOTIFICATION create_table_notify
ON DATABASE
FOR CREATE_TABLE
TO SERVICE 'NotifyService',
'93576EF7-2297-4F84-9842-4E6854D3FD10';;

GO

CREATE EVENT NOTIFICATION alter_table_notify

ON DATABASE

FOR ALTER_TABLE

TO SERVICE 'NotifyService','93576EF7-2297-4F84-9842-4E6854D3FD10';

--Check the notification message

USE AdventureWorks
GO
SET NOCOUNT ON
DECLARE @id uniqueidentifier;
DECLARE @body nvarchar(max);
DECLARE @type sysname;
BEGIN TRAN
WAITFOR
(RECEIVE TOP (100)
@type=message_type_name,
@id=conversation_handle,
@body=message_body
FROM [NotifyQueue]
),TIMEOUT 60;
PRINT @body;
COMMIT