Breaking News

Friday, February 26, 2010

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.

1 comment:

  1. Biztalk server online training - 21st Century Software Solutions
    http://www.21cssindia.com/courses/biztalk-server-online-training-213.html
    ఈ పేజీని అనువదించు
    biztalk server online training, biztalk server training, biztalk server online training visakhapatnam, biztalk 2010 online training, microsoft biztalk online training, ...
    మీరు ఈ పేజీని 2 సార్లు సందర్శించారు. చివరగా సందర్శించినది: 9/2/14
    Biztalk admin online training - 21st Century Software Solutions
    http://www.21cssindia.com/courses/biztalk-admin-online-training-220.html
    ఈ పేజీని అనువదించు
    biztalk admin online training, biztalk admin training, biztalk server online training, biztalk server training, biztalk admin course contents, biztalk admin enquiry, ...Courses at 21st Century Software Solutions
    Talend Online Training -Hyperion Online Training - IBM Unica Online Training -
    Siteminder Online Training - SharePoint Online Training - Informatica Online Training
    SalesForce Online Training - Many more… | Call Us +917386622889
    Visit: http://www.21cssindia.com/courses.html

    ReplyDelete

Designed By Published.. Blogger Templates