Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

SQL Server 2005 - Service Broker

Last updated Mar 28, 2003.

I really like products that say what they do in the name. When I lived in Florida there was a moving company called "Two Men In A Truck," and then of course my personal favorite (if not lengthy) company name: "Aarons Rents and Sells Furniture." Nice and simple, straight to the point.

The feature I'm describing in this article is named for exactly what it does, albeit in technical language. The Service Broker, available only in SQL Server 2005, acts as a broker between computer services. To understand what that means, we need a little more background.

Applications are often set up between a computer that a user enters data on (a client workstation) and a computer that receives, processes and stores data (the server system). This is called a "two-tier" or "client/server" model. This has the advantage of being easy to program. It does not, however, allow you to scale the application out to other types of database servers, or provide very much object re-use, since the client machines can't coordinate data requests between each other. Adding another server between a client workstation and the database server allows more flexibility for using multiple database servers, and the middle server can also process business logic rules. This type of arrangement is called a "three-tier" model. You can add other servers that perform even more specialized processing between the client and the database. This is called an "N-tier" model, because you can have as few or as many of these servers as you wish.

If you think about the data path between these systems, it's most often sequential. The client sends a data set to the first server, which adds some business logic and processing, and sends it on to the next, perhaps for more numerical algorithms, which sends it on to the database server. The database server adds more processing and returns a result to the client system. Each of the systems in this progression is servicing the data request. Each has a specific function in the processing chain, similar to an assembly line in manufacturing.

But there are other ways of processing data. If you think about how you go through your day, you'll find that you don't always stand in a line (or a queue, for my non-American readers) for everything you do. You get into your car; stop at a gas (petrol) station, which has been open all morning. That station has serviced many cars, without knowing who would come in first or last. You then drive to a coffee shop to buy a latte, again open since morning and again servicing many customers in multiple orders. You may decide not to stop at the coffee shop, or choose another. You choose one road over another to get to work, park in different parking spaces each day, and so on. Each of the "objects" you're interacting with in your world on a day-to-day basis provides you a service, and you often have multiple choices of providers of those services.

You can set up your infrastructure to work the same way. Rather than having a single server process a purchase order for your company, you could break up the "services" in that process and have multiple servers process the request. Your client application can ask the system which server provides a needed service and route the request to a server based on location, load, speed or security. This arrangement is called a "Service-Oriented Architecture" or SOA, and while it takes a lot of planning and forethought to set up, it's very powerful.

The paradigm shift you'll need to make when you design your applications around a SOA is that your programs will work asynchronously; that is, without a direct connection between the application and the end server. You'll see this behavior as I describe the system.

The core of a SOA is the idea of a message. A message is a request for a service to do something. You can think of a request as an e-mail message you send to someone. You type the message; it is stored on your server, sent out to other servers, and stored at the recipient's server. Eventually the recipient opens his or her e-mail program, downloads the message and reads it. In a certain sense, e-mail is a SOA.

Within SQL Server 2005, the Service Broker handles all of the messaging and processing you need to create an entire SOA system. As I mentioned earlier, it brokers the requests between services.

I'll show an example of setting up a simple Service Broker application in a tutorial here at Informit, but before we write code, let's examine the main concepts and requirements in the feature.

The first requirement is that the servers have a trusted link between them. This is done using certificates, which are software codes shared between the computers for encryption. Using this security, you'll next set up routes to describe the network path the servers use to send a message back and forth. The routes are connected by endpoints, which are TCP/IP ports set up and controlled by SQL Server for the Service Broker.

After the connection parameters are set up, you'll create conversation groups that allow the computers to send a message between each other. You're now able to create messages, of various types, that hold data. The final piece of the puzzle is the services your system provides. Services accept and process messages, using contracts, queues and other mechanisms to ensure that the message is processed correctly.

Using Service Broker and a little .Net code, you can create a complete SOA system with nothing else to buy.

Informit Articles and Sample Chapters

I have a completely free chapter from my book on SQL Server Administration you can read here. The example isn't complete, so wait for the tutorial on this site.

Online Resources

Microsoft's description of Service Broker is here.