Service Broker is a new feature from SQL Server 2005. Basically it is an integrated part of the database engine. The Service Broker also supports an asynchronous programming model used in single instances as well as for distributed applications. It also supports queuing and reliable direct asynchronous messaging between SQL Server instances only.
In this article we learn how to use Service Broker and triggers to capture data changes.
Service Broker is used to create conversations for exchanging messages between two ends, in other words a source (initiator) and a target. Messages are used to transmit data and trigger processing when a message is received. The target and the initiator can be in the same database or different databases on the same instance of the Database Engine or in separate instances.
The Service Broker communicates with a protocol called "Dialog" that allows us bi-directional communication between two endpoints. The Dialog Protocol specifies the logical steps required for a reliable conversation, and ensure that messages are received in the order they were sent.
How to create an asynchronous trigger
Enable the Service Broker on the database, as in:
ALTER DATABASE [Database Name] SET ENABLE_BROKER
Sometimes the query above takes a long time to execute, the problem is that it is requires special access to the database. Also there might be a connection that is using this database with a shared lock on it; even if it is idle, it can block the ALTER DATABASE from completing. To fix the problem use ROLLBACK IMMEDIATE or a NO_WAIT statement at the termination options of ALTER DATABASE, as in:
ALTER DATABASE [Database Name] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
Create an audit log table and create a procedure that helps to receive messages from a queue, as in:
CREATE TABLE auditlog
CREATE PROCEDURE [dbo].[spMessageProcTest]
DECLARE @message_type varchar(100)
DECLARE @dialog uniqueidentifier, @message_body XML;
WHILE (1 = 1)
BEGIN -- Receive the next available message from the queue
RECEIVE TOP(1) @message_type = message_type_name,
@message_body = CAST(message_body AS XML),
@dialog = conversation_handle
FROM dbo.TestQueue ), TIMEOUT 500 if (@@ROWCOUNT = 0 OR @message_body IS NULL)
--process xml message here...
INSERT INTO auditlog values(@message_body)
END CONVERSATION @dialog
The next step is to create a Message Type, as in:
-- Create Message Type
CREATE MESSAGE TYPE TestMessage
VALIDATION = WELL_FORMED_XML;
-- Create Contract
CREATE CONTRACT TestContract
(TestMessage SENT BY INITIATOR);
-- Create Queue
CREATE QUEUE dbo.TestQueue WITH STATUS=ON, ACTIVATION
(STATUS = ON, MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = spMessageProcTest, EXECUTE AS OWNER);
-- Create Service Initiator
CREATE SERVICE TestServiceInitiator
ON QUEUE dbo.TestQueue (TestContract);
-- Create target Service
CREATE SERVICE [TestServiceTarget]
ON QUEUE dbo.TestQueue (TestContract);
Now we can test our logic.
To do that we need to create a table and write a trigger on it. In the trigger we must send our message to the target. In this example I am sending updated data as XML.
CREATE TABLE [dbo].[DepartmentMaster](
[DepartmentId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Description] [varchar](50) NULL,
CONSTRAINT [PK_DepartmentMaster1] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--Insert some dummy values in tables
INSERT INTO DepartmentMaster VALUES ('Purchase','Purchase Department'),
--Create trigger for update
CREATE TRIGGER dbo.Trg_DepartmentMaster_Update
SET NOCOUNT ON;
DECLARE @MessageBody XML
DECLARE @TableId int
--get relevant information from inserted/deleted and convert to xml message
SET @MessageBody = (SELECT DepartmentId,Name,Description FROM inserted
FOR XML AUTO)
If (@MessageBody IS NOT NULL)
DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [TestServiceInitiator]
TO SERVICE 'TestServiceTarget'
ON CONTRACT [TestContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle
MESSAGE TYPE [TestMessage](@MessageBody);
When I update data the trigger will fire and the trigger creates a handle for conversion and sending data.
Current data of the DepartmentMaster table:
Update Statement for the DepartmentMaster table:
UPDATE DepartmentMaster SET Name = 'other Department' WHERE DepartmentId = 4
Audit Log table and value:
<inserted DepartmentId="4" Name="other Department" Description="testdescription" />
The Service Broker is very useful for asynchronous integration, providing secure, scalable and reliable messaging. With the help of service Broker we can create Asynchronous triggers.