Single conversation in Service Broker

Question

Hi guys!

I'm going to do async auditing on my SQL Server 2008 as shown here: http://auoracle.blogspot.com/2010/02/service-broker-master-audit-database.html

What it does is:

• a trigger sends a message to a queue in the service broker
• another SP in other database receives the messages and process them

The possible problem I see is that it's using a single conversation to send all the messages in order, which is a requirement.

I'm just a little concerned about the fact it's using a single conversation, I guess it's not the common usage. Do you know if there's any problem on doing so?

Thanks!

Answer 1

There's nothing wrong with using a single conversation. Some people use conversation pooling with several pre-created conversations, but unless you're hitting a performance bottleneck, I wouldn't worry about it.

One thing that you should get right is error handling, closing the conversation and opening a new one in case of error.