The Blomsma Code - More on SQL Server Service Broker

Tuesday, May 29, 2007

In earlier posts I already blogged about creating some simple queuing solutions with SQL Server Service Broker. Last week I spend some time actually implementing my research in a customer project and of course ran into two 'interesting' issues.

It is possible for the Service Broker to somehow die on you, whilst appearing to be up and running and with RETENTION=ON sent message appear to be in the queue.

When using the SQL Profiler it becomes clear that messages don't get processed because the Service Broker is not enabled.

You can use the following statement to enable Service Broker:

alter database PFA_DATA set ENABLE_BROKER

I had to do it on two machines and on of them gave me the following error:

Msg 9772, Level 16, State 1, Line 2
The Service Broker in database "PFA_DATA" cannot be enabled because there is already an enabled Service Broker with the same ID.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.

So instead I used:

alter database PFA_DATA set NEW_BROKER

After this the Service Broker was up and running and message started getting delivered.

If the query takes extremely long to complete restart SQL Server and try again.

One potential cause for this is when you restore a database.
In my earlier post I just blatantly used WITH CLEANUP to avoid getting EndDialog messages.
The documentation however states:

WITH CLEANUP
Remove all messages and catalog view entries for this side of the conversation without notifying the other side of the conversation. Microsoft SQL Server drops the conversation endpoint, all messages for the conversation in the transmission queue, and all messages for the conversation in the service queue. Use this option to remove conversations which cannot complete normally. For example, if the remote service has been permanently removed, you use WITH CLEANUP to remove conversations to that service.

So use this sparingly.

I've discovered a much cleaner way to handle it.
When sending a message you can use "END CONVERSATION @dialog" to specify that as far as the sender is concerned the dialog is over. In other words: a fire and forget message.
When the receiving side of the conversation receives the messages and performs its "END CONVERSATION" statement, Service Broker will detect that no acknowledgement (EndDialog) needs to be send.

Tuesday, May 29, 2007 7:09:17 AM (Eastern Standard Time, UTC-05:00)

Wednesday, May 30, 2007 3:28:57 PM (Eastern Standard Time, UTC-05:00)

The enabled stuff is by design. I assume you were either restoring or attaching a database with a broker active in it. When you do this. there's no way for the broker code to know whether you are restoring the same database or using restore or attach to make an additional copy of the database. If you have two copies of a database with dialogs and messages present, both copies will immediately start trying to deliver the messages which causes nasty issues with message delivery. The testers used to do this fairly often to create test databases so we decided that if we didn't know whether the broker was a duplicate or not we would disable it to eliminate the problem. If you tell the broker logic what you're doing with the ENABLE_BROKER or NEW_BROKER options in restore and attach, it will leave it enabled.

The fire and forget pattern is not a good one to use. It's better than the END CONVERSATION WITH CLEANUP solution but it leaves the dialog in an incomplete state so your sys.conversation_endpoints table will fill up with half-open dialogs. There's a good explanation of this here:
http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx
and if you're looking for an architectural approach to designing your Service Broker applications, I would recommend this:
http://msdn2.microsoft.com/en-us/library/aa964144.aspx

Roger Wolter

Comments are closed.