SQL Server 2008 : Service Broker Defined

1/22/2011 4:04:50 PM

The Queuing Concept Explained

The concept of message queuing has been around for many years. Microsoft has included message queuing as an available option within the operating system since Windows NT 4.0. Starting with SQL Server 2005, the SQL SERVICE BROKER (SSB) has been included within the database platform. SQL SERVICE BROKER, like all message queuing technologies, allows for asynchronous messages to be sent from one server to another with the receiving server processing that message and carrying out some predefined task.

A simple analogy is that one process within SQL Server is going to send an e-mail to another process within SQL Server. The second process will read the message and do something based on the contents of that message. The beauty of SQL SERVICE BROKER is that the something that it does is totally up to the developer of the stored procedure that is going to be run.

The beauty of message queuing in general is that message delivery is guaranteed in the order sent, as is the fact that a message can be received only once. What this means is that no matter how many people are sending messages to the receiving system the messages will always be processed in the correct order that they were sent, and the receiving system can receive them once and only once. This will prevent the system from reprocessing the same message twice and causing errors because of processing the same message twice. The only way a message can be processed twice would be if the sending system sent the same message text as two separate messages.

Whereas other messaging systems like Microsoft Message Queue (MSMQ) can be accessed directly by Windows applications or applications on other operating systems, SQL SERVICE BROKER does not currently support this. In order to send or receive a message you have to log in to the SQL Server database. Neither Microsoft nor any third party as of this writing has written a way to send or receive messages without connecting to the SQL Server database. Because of this, SQL SERVICE BROKER may not always be the correct solution for your messaging environment. If the messages are not going to be sent or received by T/SQL then SQL SERVICE BROKER may not be your best option.

Service Broker Defined

There are two main concepts within SQL SERVICE BROKER: sending and receiving messages. Any process can send a message, provided it has the required rights to do so. Messages can be received on demand—for example, through a SQL Server job performing a RECEIVE command on the queue or by a Windows application logging into the database and performing a RECEIVE command on the queue. Messages can also be received and processed automatically by using what is called an activated stored procedure. The activated stored procedure is a normal stored procedure that is started automatically by the receiving queue when a message is received. The procedure would then RECEIVE the message and process the contents of the message as needed.

Any database within the SQL Server instance can use the SQL SERVICE BROKER including the system databases. It is recommended, however, that you not make any changes to the SQL SERVICE BROKER within the system databases because SQL Server uses the SERVICE BROKER for its own tasks. In fact, even if you have never used SQL SERVICE BROKER before, if you use SQL Mail to send e-mails from the SQL Server you are using SQL SERVICE BROKER, because SQL Mail uses the SERVICE BROKER to queue the messages for sending.

The SQL SERVICE BROKER is a collection of objects within the database that when configured can be used to send messages from one process to another. These messages can be sent within the same database, to another database within the same instance of SQL Server, to another database in another SQL Server instance on the same server, or to another SQL Server instance on another server either within your company’s network or to another company’s network.

There are a few Dynamic Management Views (DMVs) that are used to gauge the health and performance of the SERVICE BROKER system. Of these the most important when troubleshooting SQL SERVER BROKER issues are the sys.conversation_endpoints and the sys.transmission_queue. The sys.conversation_endpoints is used most often to troubleshoot issues when sending messages from one queue to another within the same database; the sys.transmission_queue is used most often to troubleshoot issues when sending messages from one database to another. With regard to the SQL SERVICE BROKER it is important to remember that when we speak of sending messages from one database we are talking about sending messages within the instance of SQL Server or across servers.


Know the difference between the sys.conversation_endpoints and sys.transmission_queue catalog views; know when you should check each, and what information is in each one.

The sys_converstaion_endpoints contains information about each conversation that is in flight at the current time as well as the conversations that were closed most recently. The DMV contains a single row for each conversation regardless of how many messages are contained within the conversation. If there is a problem with a message moving from the source to the destination you can identify which end of the conversation is having the problem based on the state column of this DMV. It will also tell you the number of messages within the conversation as well as when the conversation will expire (if ever).

The sys.transmission_queue contains information about each message that is stalled for one reason or another. There will be one record in this DMV for each message that is stalled, as well as one record for each message that is in flight between databases. The most useful column of this DMV is the transmission_status column. This column tells you in plain, easy-to-read text what the cause of the problem is. Once this issue is resolved the message will either move on to its destination or another error will be displayed, which then needs to be resolved. If there is no value in the transmission_status column then the message is in flight and has not failed.

The SQL SERVICE BROKER is extremely flexible when it comes to the message body itself. You can send a single value as the message text, or a binary object. The most common way to send data, however, is as an XML document. This provides you the most flexibility when sending the message since you can send more than one piece of data within the same message. In fact, you can send several records’ worth of data if you so desire, provided that you structure your XML document correctly. You can even send a blank message and have the receiving system execute a different branch of code based on the message type of the message being sent.

When messages are sent, they are sent within conversations. A single conversation can have messages of many message types, but a conversation exists between two services (and queues) only. A single conversation can contain a single message, or several messages. When people first start building SQL SERVICE BROKER applications they usually start out with a single conversation per message. However, as you get into larger and more complex SQL SERVICE BROKER applications you will begin to see that sending more than one message per conversation will increase your system performance. However, doing so does add another layer of complexity to the configuration.

Head of the Class...: Optimize Your Service Broker Conversations

The SQL SERVICE BROKER is a very well-written system. This can be shown by the fact that the SQL SERVICE BROKER has not had to change much between the SQL Server 2005 and SQL Server 2008 versions. Although I’m sure that there have been some backend changes to the system, there have not been any real new features added to the system since the SQL Server 2005 release. However, there are ways to optimize your SQL SERVICE BROKER system.

When you are working with a high load SQL SERVICE BROKER application you can increase the performance of your application by reusing the same conversation for many messages. Doing so will increase the performance of your application in a couple of places. By not creating a new conversation for each message you will increase the performance of your SQL SERVICE BROKER application by about 4×. By doing a receive on multiple messages at a single time the performance of your RECEIVE command will increase by about 10×.

There are a couple of different techniques that can be used to do this. The first, published by Remus Rusanu (found on his blog, uses a table that allows for a separate conversation per SPID. The second, published by Denny Cherry (found on his blog, uses a table that allows for a single conversation handle per source and destination combination.

In Remus’s technique he leaves it up to you to decide how to tell the SQL SERVICE BROKER when to close the conversations. You can use length of time, or the number of messages depending on your specific situation.

In Denny’s technique a random number is used to determine whether the conversation should be closed. Although this solution takes away some control, you can increase or decrease the average number of messages per conversation by adjusting the multiplier against the RAND function. When the random value is 0 then a blank message is sent using a separate message type. Then a new conversation is created and the normal message is sent against the new message type. The receiving procedure then uses the separate message type to know when the conversation should be closed using the END CONVERSATION command.

Either technique can be used equally well to tell the SQL SERVICE BROKER how often to close the conversations. Both blogs give full T/SQL source for both solutions.

The SQL SERVICE BROKER comes ready to use within all your databases, however, the databases must be configured to allow the SQL SERVICE BROKER to run. This is done with an ALTER DATABASE command with the NEW_BROKER switch being specified. If you ever have to restore a database that is using the SQL SERVICE BROKER you will need to turn the SQL SERVICE BROKER back on using the ALTER DATABASE command, but this time using the ENABLE_BROKER switch. If you use the NEW_BROKER switch on a database that is using the SQL SERVICE BROKER already all messages that are in flight or in queue will be lost. If you use the ENABLE_BROKER switch on a database that has never been set up to use the SERVICE BROKER before, it is the same as using the NEW_BROKER switch.



Remember when to use the NEW_BROKER and ENABLE_BROKER switches. The exams like to show lots of options that look similar.

SQL SERVICE BROKER can be used to send messages from server to server. You can also configure SQL SERVICE BROKER to route messages through another server by routing the message to the second server, and having a route on that server, which then forwards the message on to a third server. This can be done with any edition of SQL Server 2008 with one requirement. Two SQL 2008 Express Edition instances cannot send messages directly to each other. In order for two SQL Server 2008 Express Edition instances to send messages to each other, the messages must route through another edition of SQL Server.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone