Feature Post


SQL Server 2005 Broker Service: MSMQ, WCF, Biztalk!

I got a chance to plumb with MSMQ way before I worked with SQL Server 2005. And recently, while reading a book and couple of articles, I played around with SQL Server Broker Service; and it was fun. And the question that jumped around my mind was how could we use Broker Service, if at all possible, with WCF; and then why would we ignore MSMQ over Broker Service.

Well, thought... I should have these thoughts documented somewhere.

First, the Service Broker is one of the powerful features that comes with Microsoft BI Product Suite. Initially, I "overlooked" service broker which gives the SQL Server power to build reliable/distributed apps - and by apps I mean, the service broker can turn SQL Server into a platform. Imagine database talking to each other, performing transactions, taking decisions, releasing notifications, all by themselves.

Microsoft says: Service Broker provides an asynchronous communication mechanism that allows servers to communicate by exchanging queued messages. Service Broker can be configured to prioritize certain messages so that they are sent and processed before other lower priority messages. Use the Service Broker Diagnostic Utility to investigate communication problems between participating Service Broker services. - MSDN

FIG1: Service Broker

FIG2: Network Level Communication Pattern

MSMQ vs Service Broker
Since service broker is built into SQL Server, I found out that service broker messaging has some significant advantages over MSMQ transactional messaging - and performance being one of them.

The maximum Service Broker message size is 2 GB; The maximum MSMQ message size is 4 MB - memory constraint.

Service Broker activates another queue reader process only when the current processes aren't keeping up with the load, while MSMQ triggers fire for every message that arrives - again process intensive.

Service Broker messages can be processed by any application that can establish a database connection to the SQL Server database. Applications that process MSMQ transactional messages must run on the same physical machine as the queue.

FIG 3: Dialog based communication

Service Broker can commit updates to the message queue, database data, and application state in a simple database transaction. MSMQ requires a two-phase commit to do the same thing.

While MSMQ may be beneficial when you would require both TCPIP binary protocol and HTTP SOAP protocol for communications. Like for instance raw processes over sockets as well as web services. Because service broker is binary TCP/IP only in SQL Server 2005. Or when you would want to communicate across different windows apps - independent of SQL Server database.

BizTalk vs Service Broker
Service Broker and BizTalk may not have a lot in common but service broker can reliably deliver a message to another SQL Server instance with exactly-once in-order(EOID) assurances.

Besides providing this functionality, BizTalk, in addition can manipulate the contents of messages, map message formats, manage message processing, manage workflows, manage states, send messages over multiple different transports. If your application doesn't use any of these features and just requires reliable delivery from one SQL Server instance to another, Service Broker is probably a better alternative.

WCF vs Service Broker
Windows Communication Foundation supports many messaging styles over a variety of standards-based protocols between Windows and any operating system that implements the standard protocols that WCF supports.Which includes, but not limited to, TCP/IP, UDP/IP protocols.

Here is how I got started. If you like reading, then this article has some food-for-thought about the technology and may help. Akram Hussein has actually provided an step-wise example.Look here for tutorials.