Microsoft Queuing Frameworks: SQL Service Broker vs. MSMQ

icrosoft offers two queuing frameworks to its ‘brand’ of developers: MSMQ and SQL Service Broker. These two frameworks offer many of the same features, but differ in some important areas that might affect which you choose for your queuing project. MSMQ is a time-tested technology, while SQL Service Broker is new to most developers. This article outlines some of the differences and may help you to make an informed choice when selecting a queuing technology.

Queuing frameworks are still among the most underutilized frameworks for building enterprise applications. Used properly, they can improve user interface interaction by making long-running resource requests asynchronous, distribute workloads across several servers or services, provide disconnected applications with asynchronous connectivity, enable loose coupling between clients and servers?and the list goes on. The real benefit to developers is that the majority of the heavy lifting to provide these services has already been written by Microsoft. You don’t need to author data schemas and stored procedures to manage hand-built queues using SQL Server. There are other queuing services available to Windows developers, such as IBM’s MQ Series and BEA’s MessageQ, but MSMQ and Service Broker are the two most likely candidates for developers who use Microsoft tools. The cost of MSMQ, the ubiquity of SQL Server availability to Microsoft developers, and the wide range of Windows client platforms supported by both make MSMQ and SQL Service Broker easy choices.

Microsoft originally provided MSMQ in the Windows NT4 Option Pack, and has since published multiple revisions. DevX has published several articles detailing MSMQ’s features and programming model?see the Related References section in the left column of this article for links. MSMQ is included free of charge with all versions of Windows Server (after Windows NT 4 with the Option Pack), Windows 2000 Professional, and Windows XP. MSMQ provides API, COM, and .NET interfaces for sending and receiving messages and for managing queues.

With the release of SQL Server 2005, Microsoft also offers Service Broker as an alternative to MSMQ. Because it’s tied closely to the database platform, Service Broker can be more efficient for data-intensive queuing operations; however, unlike MSMQ, you must obtain a license for SQL Server to use Service Broker. Service Broker functionality is available in any language you use to access data from SQL Server. Also, most queuing operations in SQL Service Broker are performed using extensions to T-SQL.

Given the popularity of both MSMQ and Service Broker and the lack of comparisons between the two tools is surprising. This article fills that void.

Viewing the two tools strictly from a high-level perspective, they seem very similar; both provide reliable first-in/first-out prioritized queuing, asynchronous message processing, and are accessible from a wide variety of languages. However, their differences will likely make one or the other a better choice for your development project.

Table 1 provides a brief comparison between SQL Service Broker and MSMQ. The list is not comprehensive, but provides some of the most important differences between the two. The main criterion for inclusion was to identify features or issues that would help in selecting one technology over the other.

Table 1. Feature Comparison: These features and issues help can you decide which technology, MSMQ or SQL Service Broker, is most compatible with your particular project or environment.
Feature/Issue MSMQ SQL Service Broker
Cost Free with all business versions of Windows Requires SQL Server 2005 license
Highest Performance Higher performance possible Lower performance when standard messaging functions are not necessary
Transaction Performance Relatively expensive transactions using the Distributed Transaction Coordinator Efficient transactions using SQL Server’s internal transactions
Code Location Queuing code cannot run in SQL Server Message processing logic can run in SQL Server
Type Enforcement Type enforcement provided via message types and contracts No type enforcement
Disconnected Queuing Support for fully disconnected mode Requires connection to SQL Server (at least SQL Server Express Edition) to send messages
Backup Non-trivial backup Backup via standard SQL Server backup procedures

While the table provides a convenient, if brief, overview, in the rest of this article, I’ll expand on each of the features and issues shown.

Cost
To operate MSMQ, you must be running Windows NT 4 (with the Option Pack installed), any version of Windows 2000, Windows XP, or Windows Server 2003. With such broad support for operating system platforms, MSMQ gives you the best option if you are looking to run on ‘legacy’ platforms. Arguably, SQL Service Broker clients can also run on nearly any of those platforms because a SQL Service Broker client requires only that it can connect to SQL Server 2005. However, many applications will be considerably easier to purchase and deploy without the additional requirement of a SQL Server 2005 instance to host the queuing components.

One common problem observed in small businesses is an overburdened database server. Perhaps the availability of database resources should not be a concern when choosing a queuing platform, but if your database is already overloaded, the only options are to either upgrade or expand the database platform, or use a different queuing technology. In some cases, the cost of adding an additional database platform could prove too costly to consider.

Highest Performance
As a component of the database platform, the reliability of the messaging services provided by SQL Service Broker is unrivaled by MSMQ. Even if you decide to use transactional messaging with MSMQ, you incur the burdensome overhead of using the DTC to manage the transactions. However, if your application is fault tolerant (meaning it is acceptable to lose an occasional message) then MSMQ might provide a better option. It is capable of providing better message throughput by allowing the developer/administrator to turn off security and message loss protection. This is especially true when the sending and receiving applications are not data-driven applications. MSMQ provides finer granularity of control that can be useful when messaging performance is the priority.

Obviously, intentionally removing the security built into any application is not advisable. Further, I can conceive of only a handful of applications where message loss could be tolerated. Nonetheless, MSMQ gives the developer those options for cases where they may be useful.

Transaction Performance
In many instances, queuing applications are inherently data-driven applications. The message processing service could be looking up data from a data warehouse, adding items to an order, etc. In these cases, the queuing application using SQL Service Broker will likely realize a tremendous benefit over the same application built using MSMQ because SQL Service Broker takes advantage of transaction contexts managed internally by SQL Server, while MSMQ must rely on the Distributed Transaction Coordinator (DTC) to coordinate transaction contexts between MSMQ and SQL Server. Especially when MSMQ and the database run on separate servers, the overhead of using the DTC can become prohibitive.

In most cases, when the queuing application supports a database-driven application, SQL Service Broker is the clear choice.

Code Location
Both MSMQ and SQL Service Broker provide a great deal of flexibility in authoring queuing applications. Developers may author queuing applications using any popular language. However, one huge benefit of using SQL Service Broker is that you can write senders and receivers using T-SQL hosted by SQL Server?SQL Service Broker provides the capability of automatically activating stored procedures to process messages. MSMQ would require an external application to receive and process the messages. For data-driven applications, therefore, using SQL Service Broker provides a significant benefit.

Type Enforcement
SQL Service Broker offers a critical advantage over MSMQ by enforcing the content of messages using SQL Service Broker’s Message Types (named data types that optionally can be validated with XML schemas) and Contracts (enforcement of which Message Types are sent by which senders). MSMQ does not provide services to enforce the types of messages being deposited into queues.

Disconnected Queuing
Both MSMQ and SQL Service Broker provide a way to send messages when the receiving queue is not available. MSMQ requires a local instance of MSMQ, and SQL Service Broker requires a connection to (at minimum) SQL Server Express to accept the messages. Because both MSMQ and SQL Server Express are free, the differentiating question for most scenarios is whether the deployment and configuration of SQL Server Express is worth the effort. A massive deployment of SQL Server Express, along with the requisite configuration to enable queuing for your application could be a substantial investment. MSMQ can generally handle disconnected message with no additional local configuration.

If your application requires the senders to send messages during times when the target queue may be unavailable (think about mobile applications), this will be a consideration for your application.

All in all, MSMQ is probably a simpler choice when designing solutions for disconnected applications.

Backup
On average, backing up a SQL Service Broker queue is a more natural process for most development groups than backing up MSMQ message stores. I hope that any company that depends heavily on SQL Server has the SQL Server backup process finely tuned. Because SQL Service Broker queues are database structures, they are backed up during normal database backup processes. Further, they gain all the benefits of incremental and differential backup and restore processes provided by SQL Server.

By contrast, when MSMQ is optimized for the fastest possible messaging, it is possible that messages exist only in memory?they do not even survive restarts of the MSMQ service. Backing up persistent MSMQ messages is easier, but it has been shown to be dangerous to attempt to restore messages to an operating instance of MSMQ. In almost all circumstances, backing up messages from a SQL Service Broker queue is an easier process.

Other Factors
In this article, I have tried to highlight only what I consider to be some of the most likely factors to consider when making the choice between MSMQ and SQL Service Broker. It’s likely that you will consider factors other than those mentioned here. In general, if you have database-driven applications in an environment where a SQL Server 2005 instance is already available, you should seriously consider using SQL Service Broker. But you should consider MSMQ when both the sender and receiver applications are external to the database server and when the required deployment of SQL Server 2005 is not a feasible option.

Finally, because SQL Service Broker has a wide range of supported features that I could not cover in this article, I suspect that SQL Service Broker will slowly overtake MSMQ as the dominant queuing technology used by MS developers. This migration will only accelerate as SQL Server 2005 adoption progresses, causing the cost barrier to fall. If you are interested in developing applications using SQL Service Broker, the most complete source of information is Roger Wolter’s book “The Rational Guide to SQL Server 2005 Service Broker.”

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a