SQL Server 2005’s Service Broker uses a new concept of a dialog conversation. Service Broker conversations are conceptually similar to T-SQL connections, but there are a few important differences that could affect performance, and even have the potential for data loss. I’ll discuss a few common pitfalls related to Service Broker conversation management and provide some simple solutions and workarounds.
Before you start sending Service Broker messages, you have to start a conversation between Service Broker services. You can do so by using the new BEGIN DIALOG syntax. Once you’re done sending Service Broker messages, you can end the conversation. This is similar to what you do with T-SQL connections. For example, you could connect to the server, execute a stored procedure, and then disconnect from the server. However, if you use Service Broker in this way, not only will performance suffer because there’s no connection pooling, but you’ll also be exposing yourself to potential data loss.
The way Service Broker is implemented, sent messages first land on the sys.transmission_queue. This system queue stores messages before they’re routed to the target queue. If Service Broker can’t route the message because of invalid XML in the message, incorrect routing configuration, or a disabled queue, the messages will remain in the transmission queue until the problem is resolved. Under some conditions (e.g., an XML validation problem) the messages will be dropped once the conversation is closed. So if you send messages and end the conversation before they’re processed, you could lose them. ServiceBrokerDataLossDemo.sql demonstrates this scenario. (You can download ServiceBrokerDataLossDemo.sql, and the other executable files, by clicking the 103131.zip hotlink under Download the Code at the top of the page.)
Here’s one simple way to prevent data loss without having to write a lot of code to check if your messages have arrived. The trick is to never end a conversation on the initiator end first. Instead, define an additional user message type that’s used to communicate that the sender “wants” to end the conversation, as shown in the following command:
CREATE MESSAGE TYPE [END_CONVERSATION]
VALIDATION = EMPTY;
Although the initiator signals the conversation end, the target queue is the one to actually end it. The initiator queue should then complete the process by also ending the conversation. Because Service Broker is a FIFO queue, the “end conversation” message is processed after the data messages. ServiceBrokerEndDialogDemo.sql illustrates this approach.
This approach will prevent data loss, but it’s still not doing much for performance. If you begin a conversation, send a single message, and then end the conversation, you’ll be creating significant conversation management overhead. You’ll also never be able to receive more than a single message at a time. This is because the RECEIVE statement can only get messages from a single conversation. That means you wouldn’t be able to use the RECEIVE TOP(X) syntax to get multiple messages and process them as a batch, a strategy that can significantly improve performance in a busy system. ServiceBrokerReceiveDemo.sql shows the interaction between conversations and RECEIVE statement batching.
The best way to get around this problem is to reuse conversations. Instead of sending a single message, you could send several messages before closing the conversation. Because Service Broker is most useful in real-time and near real-time scenarios, you typically don’t have the luxury of processing multiple messages inside a loop. It’s more likely that your send code sits inside a trigger that fires every time a record is inserted or modified. This presents the problem of persisting the conversation handle GUID between executions. One solution would be to store the GUID in a table. The problem with this approach is that at the time of processing the next event, the conversation might have been closed, so the handle would be invalid. An alternative approach is to interrogate the sys.conversation_endpoints system view to find a valid open conversation, as shown in the following code:
SELECT TOP(1)
@dialog_handle = [conversation_handle]
FROM sys.conversation_endpoints with(nolock)
WHERE [state] = 'co'
AND far_service = 'TargetService'
ORDER BY lifetime DESC;
A new conversation is started only if a valid conversation isn’t found. The advantage of using this approach is that you don’t have to worry about storing GUIDs; SQL Server will do that for you. Also, the conversation handle you obtain will be valid even if the one used in the previous run no longer is. ServiceBrokerConversationReuseDemo.sql demonstrates this approach.