SQL Server 2005 Service Broker Sample

By: Jeremy Kadlec | Comments | Related: > Service Broker

Problem

I have heard some buzz about Service Broker and I have seen the recent tips (Service Broker Infrastructure Objects and Service Broker Troubleshooting) that have piqued my interest. Unfortunately, I am still a little foggy on this technology. In SQL Server 2005 Management Studio, I have browsed some of the folders under my databases and seen some default objects, but not too sure if I get it.

I need to understand the basics to get started with the technology. Can you take a step back and explain what Service Broker is? How does it work? How do I access the Service Broker objects? What are the Service Broker objects? When should I use Service Broker? Can you also provide some additional resources on the topic?

Solution

Those are all valid questions and common for DBAs and Developers new to SQL Server 2005 and Service Broker. Let's take it step by step to try to work through each of the Service Broker questions to give you the foundation of information in order to move forward. Here we go...

What is Service Broker?

Service Broker is a new technology to SQL Server released with the 2005 version. At a high level, Service Broker is responsible for asynchronous data communication and storage in a set of queues. The queues are written to and read from in a similar manner as a relational table, but the commands are slightly different, we will get to that shortly.

One of the typical examples used to explain Service Broker is sending and receiving a piece of mail. You can think of Service Broker as having a sender, a set of routing commands and a receiver. Each of these components are independent and can start or complete the process when ready, meaning that the sender can start the process then go start another process and be sure that all of the processes are successfully completed.

How does Service Broker work?

From a programming perspective, the infrastructure components are created. Then to start the Service Broker application a conversation is started, messages in XML format are sent as specific message types to a particular queue, then the messages are received and processed. Once the communication is over, the conversation is ended. At any point in time, multiple Service Broker applications can be reading and writing to the same queues as well as having multiple Service Broker applications residing in same database, same SQL Server instance or SQL Servers on opposite sides of the world.

How do I access the Service Broker objects?

The answer to this question is typically one of the most surprising answers to DBAs and Developers new to Service Broker. The native programming language for Service Broker is T-SQL. All of the objects are T-SQL objects that have associated CREATE, ALTER and DROP commands. You can browse the default objects in Management Studio then create your own for your application needs.

Check out this tip as an example of the Service Broker Infrastructure Objects.

What are the Service Broker objects?

Below are the Service Broker infrastructure components:

Endpoint - Communication point in the database for Service Broker. In addition, ability to specify the authentication, encryption and message forwarding. A single Service Broker endpoint can be created for each database, however the HTTP and Database Mirroring can be created as well.
- For additional information visit - Endpoint
Message Type - Format for the message that is being sent and received. The format could be simply well formed XML or be bound to a schema.
- For additional information visit - Message Type
Contract - Which message types are sent by either the initiator or the target.
- For additional information visit - Contract
Route - For the specific Service Broker Service (see below) which SQL Server instance and database the messages will be routed to during the Conversation (see below).
- For additional information visit - Route
Queue - At a high level, this is the logical container in the Service Broker application. The queues serve as a storage mechanism in Service Broker. All data is RECEIVED (see below) from the queue for processing. Although the queues are all named differently, they are actually all of the same format with both relational and binary columns that can be converted to an XML format.
- For additional information visit - Queue
Service - The Service correlates the queue and the contract.
- For additional information visit - Service
Remote Binding Service - Defines the Service Broker user to with the Service.
- For additional information visit - Remote Binding Service

A typical Service Broker application has a database where the sending objects reside and another database where the receiving objects reside. As such, below outlines the typical commands executed in a Service Broker application:

BEGIN DIALOG CONVERSATION - Initiate a conversation from a Service Broker Service to a Service Broker Service on a particular contract.
- For additional information visit - BEGIN CONVERSATION
SEND - Sends the XML message that is built on the conversation from the BEGIN DIALOG CONVERSATION command.
- For additional information visit - SEND
RECEIVE - Removes 1 or more messages from the queue for processing.
- For additional information visit - RECEIVE
END CONVERSATION - Ends one side of the conversation. So logic needs to be written on the opposing side of the application to end the conversation based on a message type of http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog.
- For additional information visit - END CONVERSATION

When should I use Service Broker?

This is really the million dollar question because Service Broker has a number of possibilities when it comes to passing data between 2 different logical applications, physical platforms or even organizations. Further, Service Broker can facilitate building a scale out architecture via an asynchronous set of communications as long as the application can support this communication paradigm. In addition, Service Broker can be coupled with .NET and web services to further extend the capabilities.

Since we mentioned when to use Service Broker, it is worth mentioning when this technology is typically not considered the ideal solution:

High availability solution
Fault tolerance solution
A pure replication solution

Can you also provide some additional resources on the topic?

Next Steps

In some respects Service Broker meets very specific needs, so be sure to truly understand the business need and make sure that you select the appropriate technology. Moving all of your applications to use Service Broker, probably does not make sense, although it is technically feasible.
In the mean time, I encourage you setup Service Broker in one or more of your development environments to test the variety of configurations and opportunities available with the technology.
While you are testing Service Broker, be sure to get up to speed on your XML skills. Since Service Broker has some dependencies on XML in terms of the data that is being sent and received in the messages, being comfortable with XML will be a big help.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips