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.

Changing the collation on an existing SQL Server 2008 instance

If you have a SQL Server 2008 installation and wish to change the collation, you can run the following:

setup.exe /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=MyDomain\MyAccount /SQLCOLLATION=SomeCollation

Where “MSSQLSERVER” is the default instance name (you can change this to a named instance) and the rest of the parameters are fairly self explanatory.

Note that user databases will not be updated, only the system databases (Master etc).

To update any user databases you need to:

• Export all data from user databases using something like the BCP utility.
• Drop all user databases
• Update collation using above command
• Create user databases
• Import the data that you exported

Note that a back-up and restore of user databases does not work. In that scenario you simply restore the previous collation. You need to create an entirely new database and import the data.