RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Microsoft Queuing Frameworks: SQL Service Broker vs. MSMQ : Page 2

Not quite a knockout, SQL Service Broker beats MSMQ at its own game.

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.

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."

Michael S. Jones is the Director of Systems Architecture for Passport Health Communications, Inc., a national healthcare technology provider connecting hospitals, physician clinics and outpatient centers with payer and patient information to facilitate and improve their revenue cycle process. Michael, his wife, and three children live in Franklin, Tennessee where he spends his spare time reading and enjoying time with his family outdoors. .
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date