Last night I had the privilege of presenting to the Atlanta MDF SQL Server User group (www.atlantamdf.com) on the topic of SQL Server Service Broker. Service Broker is one of the most powerful features in SQL Server 2005, but it is rarely mentioned in discussions of new features and enhancements.

Why is there a need for Service Broker? We often need to build loosely coupled systems that scale. We may also need to retain state between the sender and receiver of messages. In addition to state, we may also be concerned about the order that message are sent. Consider an example of an online e-commerce system. The vendor might have an order entry system, a fulfillment service, and various shippers. Typically an order is entered into the system in real time, but it might be a matter of days until the order is filled and shipped. We wouldn’t want to design a system where the initial transaction had to wait until the order was shipped, because it would tie up resources.

SQL Server Service Broker is a platform for building asynchronous, scaleable database applications. Its main characteristics are reliable, ordered, and asynchronous delivery of messaging. Reliable messaging means the sender or receiver can go down, the network can go down, or either of the nodes can failover and the message stream will delivered exactly once in order. The messages are checked through checksums to make sure they have not changed. Messages are processed in the order they are sent. A Service Broker solution can scale up and handle bursts of traffic in one SQL Server instance or scale out across many instances. Think about the infrastructure required to build this product from scratch. Right out of the box, we get multithreading, locking, and ordered delivery of messages for free. This is all accomplished by system of queues to hold the incoming messages. Once a message is received from a queue, it can also activate (launch)  a stored procedure, or other external program.

I have discussed what Service Broker is – here is what Service Broker is not. Service Broker is not a general purpose messaging system – it only works on SQL Server. It only uses transactional messaging – if this is not required, perhaps there is too much overhead. Finally, it is not just a messaging system; we can use the queuing features on a single instance of multiple instances.

Here are some possible uses of Service Broker:

• Asynchronous triggers: A trigger queues a message that requests work from a Service Broker service. The trigger creates a message that contains information about the work to be done and sends this message to a service that performs the work. When the original transaction commits, Service Broker delivers the message to the destination service. The program that implements the service performs the work in a separate transaction.

• Reliable Data Processing: Some applications must reliably process queries, without regard to computer failures, power outages, or similar problems. An application that needs reliable query processing can submit queries by sending messages to a Service Broker service. The application that implements the service reads the message, runs the query, and returns the results.

• Distributed Order Processing: In this scenario where an order does not need to be fulfilled immediately but does need to be delivered. For example, a local order system receives order and routes to remote warehouse for fulfillment. Fulfillment systems do not have to fill order immediately.

• Data Consolidation for Client Apps: Consider a customer service application that consolidates data from multiple locations. Requests are executed in parallel using several services. The customer service app collects the responses and displays the results.

In summary SQL Server Service Broker is a powerful feature of SQL Server 2005.  It provides loose coupling to provide workload flexibility. It also provides reliable ordered messaging. Automatic activation (invocation of stored procedures or external applications) allows applications to scale with message volume. Related message locking allows more than one instance of an application to process messages from the same queue without explicit synchronization. My presentation can be downloaded here.