Real World DBA Episode 15 – SQL Server Features – Service Broker 


This week:

In the news, SQL Injection Attacks are on the rise. Learn what you can do to protect your data. In this week’s feature, we’ll continue the introduction to SQL Server Features and talk about the SQL Server Service Broker.

The web link is on scaling out Reporting Services, and the tip this week deals with choosing between Replication and Database Mirroring.


In the News:

Several reports have surfaced regarding SQL Injection attacks on sites around the world. SQL Injection isn’t specific to SQL Server or any other database platform – it’s a result of allowing strings to be “broken” and re-formed before the database receives the commands. There are specific steps you can take from the developer to the DBA to protect your data. Read more at    



In a previous Podcast I started an overview of the major features you can find in SQL Server 2005. I’ll cover each feature in more depth in future Podcasts, but we need a place to start the discussion for those features, so we’ll cover the basics first. This week we’ll take a look at the SQL Server Service Broker.


Most applications are based on a single server or set or set of servers providing data back to a client program. This is similar to a “general store” approach, where you drive to a single store to purchase food and other goods. But while that works for a limited set of goods and a few people at a time, using one store for everything becomes a real problem when the number of people become really large, or when the store has a lot of things to sell.


It’s like that with an application as well. At first, a single server can be the application server, the database server, the network server and so on. But after a while, there are just too many users and too many requests to serve the data quickly enough to get people in and out of the system, just like an overworked general store.


To deal with this problem, you can set up multiple servers to handle the various parts of the application. Most modern applications already do this, with a “front end” server that controls access to the database and a database server to handle the data itself. But when your organization grows out of this model as well, you might want to consider a Service Oriented Architecture, or SOA.


A Service Oriented Architecture has several systems that provide one or more services. These services can be anything from processing a complete Purchase Order to only calculating the tax for a Purchase Order. You can continue to break apart the application into as many pieces as you need.


The interesting part about SOA systems is how you can design the applications to use them. Developers can write code that enables the applications to “ask” a server what service it provides, and send a request to the least busy server. Or the code could still target a single server, which can in turn ask other servers to handle the request. Or an application might do a mix of both, sending one group of requests to a single server and a series of requests to multiple servers as well.


Another interesting design is the way a SOA system processes these requests. In a typical client-server application the application requests data from a server and immediately returns it to the client. In a SOA system, however, a message-based method is used. That means a “message” or request, is dropped off at one part of the system and then it is routed and received at another system, which processes the message, does the work (or routes it on again) and then sends the message back with the requested data.


This ability to work in stages has a great advantage – it allows the system to be very resilient. You can send the request to a server in one geographical location and have it distributed to a server far away, without worrying about the latency over the network.


The best analogy for this process is the post office. The post office takes your mail, drives it to the proper city where there is another post office.  That post office delivers it to the final destination and then follows the same reply with the message from the recipient. We order things that are delivered to us all the time, so in essence you’re already using a SOA system.


SQL Server 2005 includes everything you need to build a SOA system, using the Service Broker feature. To set up the Service Broker, you create a trusted link between your systems. You can do this with a certificate, which is a software code generated on one system and accepted by another. This code provides the basis for the encryption the system uses so that the messages sent between them are secure.


Next, you set up routes between the systems. The routes use endpoints, which are TCP/IP ports that listen for messages, all set up and controlled by SQL Server for the Service Broker.


Once those routes are set up, you can create conversation groups which are used to send the messages between systems. The most basic building block of the system is a message, which is stored in a queue on one system and received into another queue on a different system. The ability to provide data back and forth is handled by a contract between the systems that agree on the work to be done by each system.


It sounds complex, but it doesn’t have to be. For more detail, check out the link at


Web Link:

The web link for this week is on planning for scalability and performance with Reporting Services. Find it at


Tip of the Week:

If your application requires an entire database to be available at a standby server, use database mirroring rather than replication. Database mirroring is more efficient if the whole database needs to be synchronized, and there is no need to use the secondary server for queries. For more information, check out

Skip to main content