SQL Server™ 2005 Bible

Chapter 28. Queueing Data with Service Broker

In This Chapter

• Defining a work queue

• Queuing messages

• Managing dialogs

Service Broker is one of my favorite features of SQL Server 2005. This powerful yet simple work queue system can be used to add asynchronous messaging and work queues to a database abstraction layer to provide high scalability, and is essential in any SOA data store architecture.

If you've ever built a table to hold work to be done, such as orders to be processed by an MRP system, then you've built a work queue. In one application Service Broker is just that—a high-performance, wide-payload work queue integrated into SQL Server with DDL and monitoring capabilities.

Service Broker can also be used to pass messages with guaranteed secure delivery between work queues, which opens up a world of possibilities.

Because Service Broker is essentially just a SQL Server table, it includes all the cool transactional and back-up capabilities inherent to SQL Server. This is what sets Service Broker apart from other queuing technologies such as MSMQ.

The queue contains a single wide column for the message body, which is OK because the message will typically contain a single XML file or fragment or SOAP message as the payload.

Note

The Service Broker technology was added with SQL Server 2005, so this entire chapter is new to SQL Server.

Service Broker is not enabled by default so the first specific step to working with Service Broker is to turn it on using the alter database command:

ALTER ...