Don’t disable your Service Broker queues (if you care about the order in which the queues process your messages)

I’m a SQL developer within a team developing a new framework that will be used to replace our multiple legacy systems.  We ETL certain data from certain legacy entites to our new system and use Service Broker in this process.  Our legacy system sends the Id of the Entity in a Service Broker conversation and then doesn’t worry about what happens after that.  This asynchronous processing is why we chose to use Service Broker.

In our legacy system we run certain processes after an entity is saved on our administration website and the final one of these processes is to send the entity to Service Broker.  Our legacy system processes any related entities in a particular order and these all get sent to Service Broker in that same order.  As the ordering is handled by the legacy system we didn’t implement any priorities on our Service Broker queues – it just receives the messages in the order we sent them with the default priority of 5.

However, yesterday we had an issue where a new user was created on our administration website.  Our users all exist within a container and the permissions for that user are set on the container allowing us to give the same permissions to multiple users by adding them to the same containers.  This new user was part of a new container and in this situation the legacy system processes the new container first and sends it to Service Broker and then processes the user and then sends that.  Service Broker receives the container entity first and we ETL over the required data.  Service Broker then receives the user entity and we ETL that.  Because of the database model in the new system the container must exist before the user can be added to it so we always have to ETL over the entities in this order.

Shortly after the new user was created we received a call saying they are unable to access the new system.  I had a look and for some reason the user hadn’t been ETL’d across.  The new container was there but not the user.  I checked the logs and saw that even though we’d sent the container before the user, Service Broker had received the user message before the container message.

I checked the logs to see if this was happening often and found several occurrences on certain days when the receive order seemed to differ from the send order.  The times these happened all seemed to occur roughly when we had a live release.

Before each go live we disable our Service Broker queues to temporarily stop the ETL process.  While the queues are disabled the messages queue up on the transmission queue (sys.transmission_queue) and then when we enable the queues they are sent from the transmission queue to the Service Broker.  However, the transmission queue does not seem to care about the order in which it received the messages so it sends them up to the Service Broker queues in a random order.  This is how we ended up processing the user entity before the container entity.

On MSDN the documentation states that “Service Broker guarantees that a program receives each message in a conversation exactly once in the order in which the message was sent, not the order in which the message entered the queue“.  (See full article here…http://msdn.microsoft.com/en-us/library/ms166049(v=sql.105).aspx).  This statement only seems to hold true if the queues are enabled.

I’ve have since seen on the MSDN site that they say “..it is rarely necessary to stop a queue once the queue is made available” so I don’t think our stopping of the queues is recommended.  (See full article here… http://msdn.microsoft.com/en-us/library/ms166046(v=sql.105).aspx).

We have a stored procedure associated with our target queue and this is the thing that runs the RECEIVE statement. In future we are just going to disable the activation on the queue so that the messages are sent to Service Broker but not received until after our go live is complete.  This will ensure the messages are picked up in the same order they are sent.

I’ve written a bit of SQL to demonstrate the ordering with the queues enabled and disabled.

1) Create a new database and set up the service broker queues.  I’ve created two priorities, 3 and 7 so I can demo how Service Broker uses these.

USE master
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'ServiceBrokerTesting')
BEGIN
 DROP DATABASE ServiceBrokerTesting;
END;
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'ServiceBrokerTesting')
BEGIN
 CREATE DATABASE ServiceBrokerTesting;
 ALTER DATABASE ServiceBrokerTesting SET ENABLE_BROKER;
 ALTER DATABASE ServiceBrokerTesting SET TRUSTWORTHY ON;
 ALTER DATABASE ServiceBrokerTesting SET HONOR_BROKER_PRIORITY ON;
END;
GO
USE ServiceBrokerTesting
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '12345ABC$'
-- Create the service broker objects
IF NOT EXISTS (SELECT * FROM sys.service_message_types WHERE name = 'TestType')
BEGIN
 CREATE MESSAGE TYPE TestType VALIDATION = NONE;
END;
IF NOT EXISTS (SELECT * FROM sys.service_contracts WHERE name = 'TestContract')
BEGIN
 CREATE CONTRACT TestContract (TestType SENT BY ANY);
END;
IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestInitQueue')
BEGIN
 CREATE QUEUE TestInitQueue WITH STATUS=ON, RETENTION=OFF;
END;
IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestTargetQueue')
BEGIN
 CREATE QUEUE TestTargetQueue WITH STATUS=ON, RETENTION=OFF;
END;
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = 'TestLowInit')
BEGIN
 CREATE SERVICE TestLowInit
 ON QUEUE dbo.TestInitQueue (TestContract);
END;
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = 'TestLowTarget')
BEGIN
 CREATE SERVICE TestLowTarget
 ON QUEUE dbo.TestTargetQueue (TestContract);
END;
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = 'TestHighInit')
BEGIN
 CREATE SERVICE TestHighInit
 ON QUEUE dbo.TestInitQueue (TestContract);
END;
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = 'TestHighTarget')
BEGIN
 CREATE SERVICE TestHighTarget
 ON QUEUE dbo.TestTargetQueue (TestContract);
END;
IF NOT EXISTS (SELECT * FROM sys.conversation_priorities WHERE name = 'TestLowPriority')
BEGIN
 CREATE BROKER PRIORITY TestLowPriority
 FOR CONVERSATION
 SET (CONTRACT_NAME = TestContract,
 LOCAL_SERVICE_NAME = TestLowTarget,
 REMOTE_SERVICE_NAME = N'TestLowInit',
 PRIORITY_LEVEL = 3);
END;
IF NOT EXISTS (SELECT * FROM sys.conversation_priorities WHERE name = 'TestHighPriority')
BEGIN
 CREATE BROKER PRIORITY TestHighPriority
 FOR CONVERSATION
 SET (CONTRACT_NAME = TestContract,
 LOCAL_SERVICE_NAME = TestHighTarget,
 REMOTE_SERVICE_NAME = N'TestHighInit',
 PRIORITY_LEVEL = 7);
END;

2) Now we’re going to send 50 messages.  The messages will contain an integer 1 to 50 and we’ll send them in order.  All messages will sent using priority 3 except for every 10th one which will use priority 7.

-- Create conversations
-- Use message priority 3 except for every 10th one use priority 7
DECLARE @dialog_handle UNIQUEIDENTIFIER ;
DECLARE @Counter INT = 1;
WHILE @Counter <= 50
BEGIN
IF @Counter % 10 = 0
 BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
 FROM SERVICE [TestHighInit]
 TO SERVICE 'TestHighTarget'
 ON CONTRACT [TestContract];
SEND ON CONVERSATION @dialog_handle
 MESSAGE TYPE [TestType] (@Counter);
END
ELSE
 BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
 FROM SERVICE [TestLowInit]
 TO SERVICE 'TestLowTarget'
 ON CONTRACT [TestContract];
SEND ON CONVERSATION @dialog_handle
 MESSAGE TYPE [TestType] (@Counter);
END;
END CONVERSATION @dialog_handle WITH CLEANUP;
SET @Counter += 1;

END;

3) Now create a table to write the messages as we receive them.

CREATE TABLE dbo.Receiver
(
 Id INT IDENTITY(1,1) NOT NULL
 ,priority INT NOT NULL
 ,queuing_order INT NOT NULL
 ,service_name VARCHAR(20) NOT NULL
 ,message INT NOT NULL
);

4) Run the RECEIVE statement to read off the messages.

DECLARE @priority INT
DECLARE @queuing_order INT
DECLARE @service_name VARCHAR(20)
DECLARE @message INT
WHILE (1=1)
BEGIN
WAITFOR (
 RECEIVE TOP (1)
 @priority = priority,
 @queuing_order = queuing_order,
 @service_name = service_name,
 @message = CAST(message_body AS INT)
 FROM TestTargetQueue 
 ), TIMEOUT 6000;
IF @@ROWCOUNT = 0
 BEGIN
 BREAK;
 END;
INSERT INTO dbo.Receiver (priority, queuing_order, service_name, message)
 VALUES (@priority, @queuing_order, @service_name, @message);
END;

5) Check the contents of the table to prove that the messages were received firstly in priority order and then in the order they were sent.  In this case messages were received in the order 10, 20, 30, 40, 50, 1, 2, 3, etc, as the messages that were a multiple of 10 were sent with a higher priority.

SELECT *
FROM dbo.Receiver;

6) Truncate the table, disable the queues and resend the messages.

TRUNCATE TABLE dbo.Receiver
GO
-- Disable the queues
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestInitQueue')
BEGIN
 ALTER QUEUE TestInitQueue WITH STATUS=OFF, RETENTION=OFF;
END;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestTargetQueue')
BEGIN
 ALTER QUEUE TestTargetQueue WITH STATUS=OFF, RETENTION=OFF;
END;
-- Resend the messages but this time don't close the conversations
DECLARE @dialog_handle UNIQUEIDENTIFIER ;
DECLARE @Counter INT = 1;
WHILE @Counter <= 50
BEGIN
IF @Counter % 10 = 0
 BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
 FROM SERVICE [TestHighInit]
 TO SERVICE 'TestHighTarget'
 ON CONTRACT [TestContract];
SEND ON CONVERSATION @dialog_handle
 MESSAGE TYPE [TestType] (@Counter);
END
 ELSE
 BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
 FROM SERVICE [TestLowInit]
 TO SERVICE 'TestLowTarget'
 ON CONTRACT [TestContract];
SEND ON CONVERSATION @dialog_handle
 MESSAGE TYPE [TestType] (@Counter);
END;
SET @Counter += 1;

END;

7) As you can see the messages are all just sitting in sys.transmission_queue.

SELECT *
FROM sys.transmission_queue;

8) Enable the queues.

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestInitQueue')
BEGIN
 ALTER QUEUE TestInitQueue WITH STATUS=ON, RETENTION=OFF;
END;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestTargetQueue')
BEGIN
 ALTER QUEUE TestTargetQueue WITH STATUS=ON, RETENTION=OFF;
END;
GO

9) Run the RECEIVE statement again and log to the table.

DECLARE @priority INT
DECLARE @queuing_order INT
DECLARE @service_name VARCHAR(20)
DECLARE @message INT
WHILE (1=1)
BEGIN

 WAITFOR (
 RECEIVE TOP (1)
 @priority = priority,
 @queuing_order = queuing_order,
 @service_name = service_name,
 @message = CAST(message_body AS INT)
 FROM TestTargetQueue 
 ), TIMEOUT 6000;

 IF @@ROWCOUNT = 0
 BEGIN
 BREAK;
 END;
INSERT INTO dbo.Receiver (priority, queuing_order, service_name, message)
 VALUES (@priority, @queuing_order, @service_name, @message);
END;

10) Check the contents of the table.  As you can see Service Broker has honoured the priority but the messages within each priority are not ordered correctly.

SELECT *
FROM dbo.Receiver;

In summary if you need to ensure that the messages are received in the order they were sent then you should not disable your Service Broker queues.  Either disable the activation on the target queue or disable the thing that does the initial send.