SQL Server Service Broker: The Subsystem You Never Knew You Needed

SQL Server is famous for its relational database capabilities and, with Microsoft’s SQL Server 2008 R2 push for managed self-service BI, everyone knows SQL Server’s BI capabilities. However, SQL Server has another valuable subsystem that many people don’t even know exists. Or, if they’ve heard of it, they don’t know what it does. This unheralded subsystem is the SQL Server Service Broker.

What Service Broker Does

SQL Server Service Broker has actually been around for a while. Microsoft introduced it in SQL Server 2005. The Service Broker subsystem provides guaranteed asynchronous messaging support. Asynchronous messaging adds a new dimension of flexibility and scalability to SQL Server. Asynchronous queuing is found in many other highly scalable applications, such as the operating system’s I/O subsystems. Airline reservation systems are built with a similar architecture. Service Broker’s messaging capability is fully transactional. Transactions can incorporate queued events, and they can be both committed and rolled back. Service Broker supports very large messages—up to 2GB, and it supports reliable delivery of messages to remote systems. This means that messaging applications built with Service Broker can span multiple SQL Server systems and still provide guaranteed message delivery—even to the remote systems. Service Broker splits the messages’ data into small chunks and sends them across the network. Then Service Broker reassembles them at the other end.

Why You Don’t Know About Service Broker

So why hasn’t the Service Broker subsystem caught on? I can think of a few reasons. First, and probably foremost, it’s the invisible subsystem; there’s no visible management for the Service Broker inside of SQL Server Management Studio (SSMS). In addition, building a Service Broker application is a rather arcane process that involves the creation of objects like messages, contracts, queues, and routes that aren’t familiar to the relational database guy. On top of that there’s no graphical application builder. Instead, it’s a code-only affair. And then, there is the problem of awareness: Many people who don’t use Service Broker don’t understand its practical value. These things keep the SQL Server Service Broker subsystem out of sight and out of mind for most organizations.

A Cool Implementation

The ability to incorporate asynchronous messaging into your applications provides a lot of flexibility, and it can be used to extend the capabilities of SQL Server in a number of ways. I’m impressed by the way the database team at MySpace used Service Broker to extend SQL Server’s built-in capabilities. MySpace has a tremendously high number of users, counted in the millions, and the social media company needed to deal with massive scalability issues. In particular, it had difficulty replicating data between its more than 450 servers. MySpace solved its replication problem with an innovative use of Service Broker to transport data between the systems. You can read more about the MySpace implementation in the SQL Mag article “MySpace Answers the Question: Why SQL Server?” at InstantDoc ID 103082.

Where to Get Started

Service Broker is one of SQL Server’s best kept secrets, but it shouldn’t stay that way. I hope this editorial has put SQL Server Service Broker on your radar and has started you thinking about ways your organization can use it. Here’s how to find it: SQL Server Service Broker is part of the SQL Server Workgroup, Standard, Enterprise, and Datacenter editions of SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2. It isn’t part of the Express edition. Technically, the Express edition can be a Service Broker endpoint but the other endpoint must be one of the paid-for versions of SQL Server. Look for an article in an upcoming issue of SQLMag on how to get started with Service Broker, by Denny Cherry. In the meantime let me know if you have interesting uses for SQL Server Broker. Drop me a line at [email protected] or [email protected].