Service Broker: What does a DBA need to know?

Service Broker is SQL Server’s internal messaging system. It has been designed as an asynchronous, reliable and transactional messaging system for inter or intra database messaging, or even inter SQL instance messaging.

It is a powerful tool for decoupling extended transactions across time and across databases. It is designed to be reliable across databases stopping and starting, network outages, server migrations and database backups and restores.

It is also a complex configuration. I wanted to prove to myself that message reliability would persist across database restores so I set up a test which I intend to post to this blog at a later date. Meanwhile in trying to configure the test I learnt and used a number of trouble shooting tips.

So what does a DBA need to know regarding service broker?

Catalog views:

Inter SQL instance configurations use endpoints :

select * from sys.service_broker_endpoints

There is a send queue and a receive queue and they communicate using conversations. The send and receive queues are associated with services and are database scoped objects (these catalog views are also database scoped):

-- one row per endpoint of service broker conversation 
--in current database

select * from sys.conversation_endpoints 

--one row per queue in current database

select * from sys.service_queues 

--transmission queues are internal service broker 
--queues for storing messages during delivery

select * from sys.transmission_queue

Queues are implemented as internal tables that can be viewed with:

select as queuename, as internalname
from sys.service_queues as Q
	join sys.internal_tables as I
		on q.object_id = i.parent_object_id

Conversation population explosion:

Conversations need to be closed with the END CONVERSATION command. Conversation endpoints are cached in memory and backed by storage in tempdb, so incorrectly handled conversations can build up un-closed endpoints and ultimately starve SQL Server of memory and disk space. (Conversation endpoints are designed to persist at the target for half an hour or so to prevent a known exploit involving sending duplicate messages)

The fastest way to clear all conversations in a given database is with:

alter database <database name> set new_broker

If you want to be a bit more discerning you could add some filters to this base code (this needs to be run at the sender and receiver):

use <database name>
declare @h uniqueidentifier
while exists(select 1 from sys.conversation_endpoints)
	set @h = (select TOP(1)[conversation_handle]  from sys.conversation_endpoints)
	end conversation @h

Poison messages:

If a message delivery results in an error the message is popped back on the queue and the receiving transaction is rolled back. If the error is not correctly handled then message delivery will fail again, the message will be popped back on the queue, the transaction rolls back…

This can result in an infinite loop and is called the poison message problem. SQL Server service broker defaults to disabling the queue if five message ‘rollbacks’ in a row are detected.

Backup and restore:

When a database is backed up all service broker objects are backed up too, including queues.

By default, when a database is restored it is restored with service broker disabled (although the identifying service broker GUID is maintained). To handle service broker during the restore process three WITH options are provided for the T-SQL RESTORE command.

ENABLE_BROKER: Service broker is enabled after the restore completes and message delivery can resume immediately. The original GUID is retained.

ERROR_BROKER_CONVERSATIONS: All service broker conversations are ended with an error message, allowing applications to handle cleanup. The original GUID is retained.

NEW_BROKER: Service broker uses GUIDs to identify services. These GUIDs must be unique within the SQL Server instance. If you are restoring a database to a different SQL Server instance you can guarantee a unique GUID by using the NEW_BROKER option. This essentially creates a new service and all conversations are ended with no error messages.

The ssbdiagnose utility:

Service broker is tricky to configure, and equally tricky to debug. SQL Server 2008 ships with a command line tool ssbdiagnose that can scan service broker configurations and report issues. The tool can run in CONFIGURATION mode and check the service broker objects and configuration, or it can run in RUNTIME mode and trace and monitor active conversations.

Full details of all the switches and syntax for the tool are in BOL or here, but by way of example here is the command line I used to check my configuration that used two databases on a single SQL Server instance (‘sender’ and ‘receiver’ are service names unique to my configuration):

ssbdiagnose -E CONFIGURATION 
-S <machine name> 
-d <sender database> 
TO SERVICE receiver 
-S <machine name> 
-d <receiver database>

Conclusion: The intention of this post was to share some of the tips and tools that I have been using to troubleshoot service broker. Hopefully they are of use.

This entry was posted in Database options, Service Broker, SQL Server, T-SQL. Bookmark the permalink.

7 Responses to Service Broker: What does a DBA need to know?

  1. dazpoc says:

    Looking forward to the Demo šŸ™‚

  2. Tony says:

    It’s also handy sometimes to be able to end a particular conversation
    END CONVERSATION ‘conversation handle’ WITH CLEANUP;

    Tony S.

  3. alzdba says:

    Nice blogpost. šŸ™‚
    – A critical thing any DBA needs to know, is that SSB objects aren’t included in the regular
    SSMS / SMO produced db script ! ( The Script all objects fools you here :-$ )
    If you want to script the SSB objects, you’ll have to do so writing your own SMO app that scripts
    the db.ServiceBroker tree.

    – Make sure you have SSB monitoring in place. Things can go wrong very fast.

  4. Tony says:

    I think the most intriguing thing about SSB is that after it was released in 2005, some 7 years on, MS has yet to write a proper visual front-end for it.It cannot be difficult as Quest seems to have done it.

  5. SDC says:

    Don’t diss Quest, let’s say ‘it cannot be impossible’.

  6. joe says:

    I thought the broker was a good idea until I used it. It shuts itself down and then will not restart (mirroring prevents it from starting). Giving up and going to use our own home grown broker.

  7. seecoolguy says:

    best of luck to all, I used this tutorial, one warning is that you need .net 3.5 installed on the machine that you intend to run external activator.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s