Service Broker is SQL Servers 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?
Inter SQL instance configurations use 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 nversation_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:
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:
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 go declare @h uniqueidentifier while exists(select 1 from sys.conversation_endpoints) begin set @h = (select TOP(1)[conversation_handle] from sys.conversation_endpoints) end conversation @h end
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.
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.
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 orhere, 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 FROM SERVICE sender -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.
Its also handy sometimes to be able to end a particular conversation
END CONVERSATION conversation handle WITH CLEANUP;
A critical thing any DBA needs to know, is that SSB objects arent included in the regular
SSMS / SMO produced db script ! ( The Script all objects fools you here :-$ )
If you want to script the SSB objects, youll have to do so writing your own SMO app that scripts
Make sure you have SSB monitoring in place. Things can go wrong very fast.
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.
Dont diss Quest, lets say it cannot be impossible.
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.
best of luck to all, I used this tutorial, warning is that you need 3.5 installed on the machine that you intend to run external activator.
Fill in your details below or click an icon to log in:
You are commenting using your account.(LogOutChange)
You are commenting using your Google account.(LogOutChange)
You are commenting using your Twitter account.(LogOutChange)
You are commenting using your Facebook account.(LogOutChange)
Drop down boxes not working in SQL Server 2012 ConfigurationManager
Behaviour change for delete output into in SQL Server2014
Another great reason to enable remote adminconnections
Tracking down CPU spikes using process explorer andDMVs
The full text error and why I was happy to seeit.
Rolling back transactions with cursors and localvariables.
Enter your email address to subscribe to this blog and receive notifications of new posts by email.
Post was not sent – check your email addresses!
Sorry, your blog cannot share posts by email.