12/09/2014

My Intro to SQL Server Service Broker

SQL Server Service Broker is a tool developed by Microsoft for exchanging messages.  It's built into every version of SQL Server including Azure.  It runs by configuring many objects and then issuing the Send and Receive methods.  Being that it's built into SQL Server, it can run stand alone or connect to external servers within or outside the network.  Messages can be encrypted.  They have an expiration date.  They are based on the smallest unit of work called the  conversation.  Conversations can be ru-used to increase performance.

Objects consist of crating a master key encrypted by password, creating EndPoints on each remote server, Routes which are the road map to guide the message to its destination, Services, Queues, Contracts and Message Types.  They are listed in highest to smallest level of magnitude and are usually created in specific order as some objects have dependencies to other objects.

You can also create and specify Keys, which get created and then swapped between machines to protect who can send and receive messages.  Message Types are used to specify the structural content of the messages.  You can actually send binary files as messages.  A contract specifies who and what can be exchanged.

Messages are sent in specific order as well.  And being an asynchronous process, when the messages arrive at the destination server, they are stored in a Queue, and processed in the order in which they were sent.  If a destination server goes down for some reason, upon returning, you can start the process manually (default is not automatically resume processing) and the system will pick up where it left off, quite a nice feature.

Once the message is received, you must do something with it.  You can activate a Stored Procedure to pull in the received message, and do something with it.  Exchange between Source and Destination can be one message in one conversation or multiple messages within a single conversation or multiple messages within multiple conversations.  Upon completion of a conversation, it must be Ended by both servers, as it's a two phase commit.

If you were to try and build this type of system, it would be very difficult and probably not as thorough or efficient, because it's built into the Database are the core level.  However, there is no real interphase, no real way of indicating a message is corrupt or error-ed out and no real way of monitoring the activity.  This is quite a few downsides, in addition to the fact that Microsoft has never really marketed the application except perhaps to the world of DBAs.

It's a bit of a challenge to set up, as well as monitor, but if done properly, it's a fully functioning messaging system built into the SQL Server database which is highly scalable, efficient, asynchronous where messages are processed in the order in which they were sent, every single time, guaranteed.

Root Cause Analysis