Service Broker in SQL Server: How and when to use it
Service Broker allows database developers to write applications that work across multiple instances in SQL Server. Contributor Serdar Yegulalp explains the benefits of this queued messaging system.
The description that Microsoft provides for SQL Server 2005's Service Broker is maddeningly vague: "Service Broker is a new technology … that helps database developers build secure, reliable, and scalable applications … [It] provides queuing and reliable messaging as part of the Database Engine. These features provide the infrastructure necessary to build high-performance applications that easily scale-up or scale-out."
What does all this mean for the average database developer? For one thing, it's suddenly much easier to write applications that work across multiple instances of SQL Server, whether on the same machine or across multiple machines. Service Broker guarantees that a message passed from one server to another will get there. If by chance it doesn't, the resulting error can be trapped and dealt with.
Service Broker's basic unit of communication between two instances of SQL Server is called a dialog; and it allows messages to be passed in both directions. It also ensures that the messages are received in the order they were sent. This way, you're not, for instance, trying to reference data that hasn't yet been inserted. Messages can also be subjected to a contract, which is, essentially, a rule enforced on both ends regulating what message types can be included in a conversation and who is authorized to send and receive them. Messages are subject to security restrictions and can be encrypted in transit, which comes in handy if you're using WAN links to pass said messages.
The really interesting stuff comes with Service Broker's queuing system. Received messages are held in a queue -- a concept that should already be familiar to SQL Server admins. Service Broker's queues are not just passive mechanisms; they can be queried with a SELECT statement like a table (for instance, to see if a given message is in the queue and waiting to be processed). This makes it possible to do some fairly elegant anticipatory programming.
Here are some examples of reasons you might use Service Broker:
In short, any operations that require queues or messages to be set up and passed consistently and reliably between instances of SQL Server, or even back to the same instance, could benefit from using Service Broker.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information from SeachSQLServer.com