Service Broker – SQL Server Connections

These are some notes and thoughts from sessions and my time at SQL Server Connections/DevConnections in Nov 2010.

Denny Cherry gives a nice warning at the beginning of his sessions. His parents were sailors, so beware of the

Service Broker is alien to so many people. I agree with that. The idea of sending a message, or a insert somewhere and having it process “later” in some other transaction is scary. People are worried. They don’t like the idea of asynchronous processing.

However “later” isn’t some long time in the future. You can have queues process immediately and automatically, and “later” is often milliseconds after you’ve sent the message.

There also is a guarantee of delivery in the queue, so you have essentially transactional consistentcy, just not everything on one transaction that must complete at the same time.

People use XML to send the data since it’s a flexible format. I agree. As heavy as XML can be with all the overhead of the tags, it’s as flexible as things come, and it’s much easier for me to understand and work with than some type of delimiter that gets in the way of the data you are processing. Not that XML doesn’t have issues, but I think it works well.

Denny says that you should have queries ready to check your queues when you set up Service Broker since the first time you do it, it probably won’t work. I can attest that I have had issues, which seem to be a combination of a confusing technology and a dearth of documentation that makes it easy.

Mr. Cherry’s former company was sending 2-3mm messages/hour through Service Broker. That’s a good scale of things happening in an instance. Open conversations can cause issues, but if you can process them quick enough, then you will be in good shape.

ssbdiagnose is a good tool (command line app) to help you figure out what is happening.

Denny uses multiple message types to let the receiver know that something is done. He has a second message type that is a “conversation switch” in which he sends an empty message that lets the receiver know that this set of data is done. That’s a good technique and one I hadn’t thought of. I’d send some EOM in a normal message type, but having a second type makes some sense.

Leaving retention on is a bad idea. No easy way to purge. That wasn’t something I was aware of, though I haven’t sent 2mm messages/hour.

Denny also uses two queues, one for messages and one for acknowledgements. This prevents a line of acks from blocking other messages, which you might have with one queue.

Watching Denny send messages in a basic send/receive SSSB queue, I can see why this hasn’t necessarily caught on. There’s no “wow” moment. Seeing a conversation take place and an acknowledgement are just not exciting. But there are huge possibilities here for auditing, distributed processing and more.

Seeing messages moving between instances in SSMS isn’t that impressive. I think there’s a chance here to write something like the Database Mirroring demo app from Kimberly Tripp that shows queries being run in real time. A series of messages shown on different instances, and being processed would be a simple, nice, .NET app.

No errors are thrown if you aren’t processing queues, which isn’t great, but it’s what I expected. So you need to make sure that you are properly watching and managing queues, especially if you have autogrow enabled on your databases.

Messages are delivered in order, only on a single conversation. So if you have multiple conversations, the messages may not be in chronological order. Denny recommends a single queue and then periodically end the conversation with a random value and restart it.

Service Broker is a neat technology and if you haven’t tried using it, you might want to check out its capabilities.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.