There are many typical scenarios in which Service Broker is the ideal solution for interactions between BizTalk and SQL Server. Last week one of our solution architects point to a scenario that he was facing with a customer. The challenge was to avoid simultaneous polling of the SQL Server adapter in a topology with two BizTalk Servers (no master MessageBox) hitting the same SQL Server database. Richard Seroter described this scenario really well a year ago. At the end we implemented the classic solution using SERIALIZABLE transactions to avoid concurrent reads. It worked perfectly but is well known that in other scenarios this technique can introduce unnecessary locks.
This brought to my mind one of the most important features of Service Broker: Conversation Locks. Service Broker uses conversation group locks to guarantee that only one application can process a related set of messages at any given time. Conversation group locks to guarantee that messages are processed exactly once, in order. Locking occurs for the conversation group rather than for the conversation ID.
Using conversation locking we can implement the explained scenario with no additional effort. Just need to associate the two locations with the same conversation group and the solution is ready to go. The additional advantage on this approach is that the entire DB is abstracted through the Service Broker queue. BizTalk Server has no knowledge of the internal database schema which removes any dependencies between the BPM and DB tiers.