Subscribing a broker service to SQL events

  • Article

One of the cool uses of SQL Service Broker is event notification. SQL Server 2005 introduces a mechanism for notifying events such as execution of a DDL statement or occurence of a SQL Trace event to a Service Broker service. Notifications are delivered as messages to the subscribing service via dialogs using a simple contract. Multiple services can subscribe to the same event and a service can get notifications from multiple events. Event notifications can be used both for monitoring some activity (eg> logging server memory changes) as well as taking action asynchronously (eg> sending a message to a remote service whenever table is created). In a previous post, I explained how to use the QUEUE_ACTIVATION event. There are over a hundred different events that can be subscribed to. Here are just a few examples:

  • CREATE_TABLE
  • DROP DATABASE
  • Audit_Login

You are free to implement the subscribing service in anyway you want -- activated T-SQL stored proc, CLR stored proc, external program, etc. Since the events are handled asynchronously (and in a separate transaction), notifications differ fundamentally from triggers. When you want to handle the event outside the scope of the transaction, it may make sense to use event notifications. Similarly, event notifications may be a better alternative to external SQL Trace when the action to be performed in response to a trace event is SQL Server based.

Event information is encoded in the message body using XML and can be extracted simply by casting the message body as XML datatype. There is a well-defined schema for the event information. When an event notification is CREATEd, the creator begins a dialog from a special system service called 'ServiceBrokerService' to the subscribing service. If the event is server wide, the initiating service is in the 'msdb' database; for database events the initiating service is in the database where the event occurs. If the receiving service ends the dialog, it will no longer receive notifications. Similarly, if the event notification is explicitly dropped, the dialog is ended.

Here's a sample for creating an event notification for CREATE_TABLE:

use eventsnotifdb
go

create queue ClientEventNotificationQueue
go

create service ProcessEventNotifications
on queue ClientEventNotificationQueue
([https://schemas.microsoft.com/SQL/\
Notifications/PostEventNotification])
go

create event notification testnotif
on database with fan_in for CREATE_TABLE
to service 'ProcessEventNotifications', 'current database'
go

create table A (a int)
go

receive message_type_name, convert(xml, message_body) from ClientEventNotificationQueue
go