My Adventures in Coding

October 23, 2007

SQL Server: Handling Failures Gracefully in Service Broker Queues

Filed under: SQL Server 2005 — Brian @ 8:23 am
Tags: ,

In my previous post I provided an example for setting up a Service Broker Queue. One of the issues you will most likely encounter soon after using Service Broker Queues is a message that fails when being processed. With Service Broker Queues it is strictly enforced that all requests in the queue be processed in order. The problem is that when an error occurs, it stops the queue and prevents any other messages from being processed until someone intervenes and fixes the the bad message (Usually a poor systems administrator who is called in the middle of the night!).

What if you are using the queue to process messages where order is not that important? If a message fails you would rather that the message be skipped and placed on a dead letter queue and not interrupt processing of the rest of the messages on the queue. Unfortunately service broker does not offer this feature, unlike many other queuing systems which do.

The following is an example of how to implement a simple dead letter queue for your Service Broker Queue. Note: Many items, such as the schema and message type will all be the same as the ones used by your main queue because both queues will be handling the same messages.

STEP 1: Create a RECEIVE sproc for clearing the queue

This sproc will be used to empty the dead letter queue when you want to reprocess any messages sitting in the queue. This may seem like a lot of SQL for this task, but it will make reprocessing entries in the queue later a simple one line of SQL.

CREATE PROCEDURE [dbo].[fooDeadLetterReceive]
AS
SET NOCOUNT ON;
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @mt SYSNAME;
DECLARE @MessageBody XML;

BEGIN TRANSACTION;
WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,
	@mt = message_type_name,
	@MessageBody = message_body
	FROM [dbo].[fooDeadLetterQueue]), TIMEOUT 1000;
WHILE (@dh IS NOT NULL)
BEGIN
	BEGIN TRY
		
		--Send message back to the main queue to be processed
		DECLARE @handle uniqueidentifier;

		IF (convert(varchar, @MessageBody.query('count(/Root/*)')) != 0)
		BEGIN
			BEGIN DIALOG CONVERSATION	@handle
			FROM SERVICE	fooService
			TO SERVICE	'fooService'
			ON CONTRACT	fooMessageContract
			WITH ENCRYPTION = OFF;		SEND
			ON CONVERSATION	@handle
			MESSAGE TYPE	fooMessage (@MessageBody);
			END CONVERSATION @handle;
		END
		
		END CONVERSATION @dh;

		COMMIT;

	END TRY
	BEGIN CATCH
			-- If there are any exceptions then end conversation,
			-- rollback the transaction and stop processing the queue
			END CONVERSATION @dh;
			ROLLBACK TRANSACTION;
			ALTER QUEUE [dbo].[fooDeadLetterQueue] WITH ACTIVATION ( STATUS = OFF );
			BEGIN TRANSACTION
			BREAK
	END CATCH	-- Try to loop once more if there are more messages
	SELECT @dh = NULL;
	BEGIN TRANSACTION;
	WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,
		@mt = message_type_name,
		@MessageBody = message_body
		FROM [dbo].[fooDeadLetterQueue]), TIMEOUT 1000;
END
COMMIT;

--Shutdown processing of the deadletter queue when we have gone through
--all existing messages in the queue
ALTER QUEUE [dbo].[fooDeadLetterQueue] WITH ACTIVATION ( STATUS = OFF );
GO

STEP 2: Create a SEND sproc for inserting messages into the queue

This sproc will be used later to insert messages into the dead letter queue when the main queue runs into a problem processing a message (Called from the CATCH block of the main queue) .

CREATE PROCEDURE [dbo].[fooDeadLetterSend]
	@body XML
AS
DECLARE @handle uniqueidentifier;

IF (convert(varchar, @body.query('count(/Root/*)')) != 0)
BEGIN
	BEGIN DIALOG CONVERSATION	@handle
	FROM SERVICE	fooDeadLetterService
	TO SERVICE	'fooDeadLetterService'
	ON CONTRACT	fooMessageContract
	WITH ENCRYPTION = OFF;		
	SEND
	ON CONVERSATION	@handle
	MESSAGE TYPE	fooMessage (@body);
	END CONVERSATION @handle;
END

STEP 3: Create a Dead Letter queue

Nothing special here, just create a new queue, called fooDeadLetterQueue. Also we attach the activation sproc that will be used for reprocessing of failed messages.

CREATE QUEUE [dbo].[fooDeadLetterQueue] WITH STATUS = ON , RETENTION = OFF , 
ACTIVATION (  STATUS = OFF , PROCEDURE_NAME = [dbo].[fooDeadLetterReceive] , 
MAX_QUEUE_READERS = 1 , EXECUTE AS OWNER  ) ON [PRIMARY]

Also note that activation on the queue (STATUS=OFF) is currently disabled. This is done on purpose. On a normal queue we always enable activation so that any time a message is received by the queue, it will be immediately processed. In the case of a dead letter queue we do not want messages processed until the messages have been checked/fixed.

STEP 4: Update the Catch block in the receive sproc of your main queue

Normally in the catch block of your TRY CATCH in a service broker queue, if an exception is thrown while processing the message, the catch block is just used as a place to log the error and then turn activation off on the queue so no more messages will be processed.

With your new dead letter queue, rather than turning off the main queue, instead we take the message that failed and send it to the dead letter queue. Now the problem message has been removed from the main queue and we can continue on with processing the rest of the messages in our queue. Now those Systems Administrators can finally relax.

BEGIN CATCH
	-- If there are any exceptions then end conversation rollback the transaction
	END CONVERSATION @dh;
	ROLLBACK TRANSACTION;
	
	--Send problem message to the dead letter queue
	exec dbo.fooDeadLetterSend @MessageBody

	BEGIN TRANSACTION
END CATCH	-- Try to loop once more if there are more messages

STEP 5: Reprocessing messages from the dead letter queue

To reprocess the queue you simply have to copy the messages in the dead letter queue back to the main queue. Now that RECEIVE sproc we wrote earlier comes in handy. Simply turn activation on for the dead letter queue. This will take all existing messages on the queue and push them back to the main queue for processing. When the dead letter queue is empty it shuts itself off again.

ALTER QUEUE dbo.fooDeadLetterQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = ON )
Advertisements

2 Comments »

  1. Appreciate the fact that you have published this information in the internet. More than the content itself, your thought of sharing with the rest of the world is very well appreciated.

    Comment by Freight Broker Training — September 3, 2010 @ 1:31 pm | Reply

    • Thank you for the comment! I hope the article was useful.

      Comment by Brian — September 7, 2010 @ 8:51 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: