SQL Server Service Broker simple example

I had a reason to use SQL Server Service Broker (SSSB) again recently. It’s a queuing mechanism built into SQL Server (from SQL Server 2005). It’s a great solution for providing messaging integration patterns with legacy databases.

It’s been a while since I used it and getting it working is a bit tricky the first time (or when you’ve forgotten the details). A very simple example to set-up SSSB is as follows:

CREATE DATABASE [YourDatabase]
GO

USE [YourDatabase]
GO

ALTER DATABASE [YourDatabase] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

CREATE MESSAGE TYPE [YourMessage] VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT [YourMessageContract] ([YourMessage] SENT BY ANY );

CREATE QUEUE [dbo].[YourMessageSendingQueue] WITH STATUS=ON, RETENTION=OFF;

CREATE QUEUE [dbo].[YourMessageReceivingQueue] WITH STATUS=ON, RETENTION=OFF;

CREATE SERVICE [YourMessageSendingService] ON QUEUE [dbo].[YourMessageSendingQueue]([YourMessageContract]);

CREATE SERVICE [YourMessageReceivingService] ON QUEUE [dbo].[YourMessageReceivingQueue]([YourMessageContract]);

DECLARE @Message xml
SET @Message= '<MyMessage>blah</MyMessage>'
DECLARE @handle uniqueidentifier
BEGIN DIALOG CONVERSATION @handle
    FROM SERVICE YourMessageSendingService
    TO SERVICE 'YourMessageReceivingService'
    ON CONTRACT YourMessageContract
    WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @handle MESSAGE TYPE YourMessage (@Message)
END CONVERSATION @handle WITH CLEANUP;

RECEIVE TOP (1) CAST([message_body] AS XML)
    FROM YourMessageReceivingQueue;

USE master
GO

DROP DATABASE [YourDatabase]
GO

Notes:

• The “SET ENABLE_BROKER” is called with “ROLLBACK IMMEDIATE” because of this.
• The validation on the Message Type is “WELL_FORMED_XML” which does as described. If you want schema validation you can do but it gets complicated.
• You can use encryption but then you need to create and manage Master Keys.
• For communication between SQL Server instances, you need to create Routes.