Setting up Queuing on SQL Server with Service Broker

To move data from satellite servers to our core cloud database, we are using the Service Broker, included in SQL Server.  I have been using the book Professional Microsoft SQL Server 2012 Administration, by Ada, Jorgensen, et. al. as a guide as I set this up.

1. Create the database master key

Before you can send and receive messages by using the Service Broker, you have to set a database master key, if one doesn’t already exist in the database. To do this, use the CREATE MASTER KEY statement, and specify the password for the database master key.

CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyPa$$w0rd'

Thanks to SQL Magazine for this tip.  It is not in most references for  service broker.  More information can be seen at the page at  http://sqlmag.com/database-administration/configuring-sql-server-service-broker.

If you don't have this set, everything will look like it is working, but no data will flow.  No error messages appear anywhere I could find.  This hung me up for several hours.

2.  Enable the Service on your database

Right click your database, and open properties.  Click on Options and scroll to the bottom of the list.  Set Broker Enabled to True. [Note:  No other users may be connected to this database while you are making this change.]

The following query will return 1 if the broker is enabled.

select is_broker_enabled from sys.databases where name='gmgqueue'

It may have to be re-enabled following a database restore.

3. Create the Message Type

CREATE MESSAGE TYPE SampleMessageType
AUTHORIZATION dbo
VALIDATION=NONE;

The Validation could require that the message type include only well formed XML, or even XML that matches a schema.

4. Create the Contract

CREATE CONTRACT MyContract
AUTHORIZATION dbo
(
      MyMessageType SENT BY ANY
);

The contract defines who is allowed to send which message types.

5. Create the Queue

CREATE QUEUE MySourceQueue

CREATE QUEUE MyDestinationQueue

These queues can be in the same database, different databases, or in different machines.  We will be exploring different machines in a later post.

 6. Create a Service

The service attaches the contract to the queue.  Messages are sent to the service.

CREATE SERVICE MySourceService
AUTHORIZATION dbo
ON QUEUE MySourceQueue
(
      Myontract
);

CREATE SERVICE MyDestinationService
AUTHORIZATION dbo
ON QUEUE MyDestinationQueue
(
      MyContract
);

7. Start sending data

DECLARE @conversation_handle UNIQUEIDENTIFIER

BEGIN DIALOG @conversation_handle
FROM SERVICE [MySourceService]
      TO SERVICE 'MyDestinationService'
      ON CONTRACT [MyContract]

DECLARE @message_body XML

SET @message_body = (SELECT * FROM sys.objects FOR XML AUTO, ROOT('root'));

SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE [MyMessageType]
(@message_body);

END CONVERSATION @conversation_handle

What is going on here?  We create a conversation handle, then start a dialog between the two services.  The select statement stuffs a bunch of random XML into @message_body.  The @message_body is then sent across using the SEND command.  We clean up after ourselves with the end conversation.

8. Receiving the packet

We can look at the queue without removing items from it by using the select statement against the queue::

declare @messageBody xml

select top 1 @messageBody = cast (message_body as xml) 
  from myDestinationQueue


select @messagebody

But, more often than not, we want to remove work from the queue.  This is done by making the following change:

DECLARE @message_body XML

DECLARE @conversation_handle UNIQUEIDENTIFIER;

RECEIVE TOP (1) @conversation_handle = conversation_handle,

                @message_body = cast(message_body AS XML)

FROM MyDestinationQueue

select @message_body

END CONVERSATION @conversation_handle;