SQL Server Service Broker (SSBS), introduced with SQL Server 2005 and enhanced in SQL Server 2008, allows you to write queuing/message based applications within the database itself. This article discusses SSBS' important features and how to use it in different scenarios.
SQL Server Service Broker
(SSBS) is a new architecture (introduced with SQL Server 2005 and enhanced
further in SQL Server 2008) which allows you to write asynchronous, decoupled,
distributed, persistent, reliable, scalable and secure queuing/message based
applications within the database itself. This integration of queuing and
message based infrastructure into the database provides several advantages from
other message/queuing based applications out of the database. For example, SSBS
provides simplified administration, inbuilt security, improved performance,
transaction support without making it distributed (more details later),
simplified recovery process as queued data are backed up as part of the normal
database back up etc. SSBS in itself is a very big topic, requiring several
hundred pages to explain. Therefore, I will be talking about its important
features; how to use it in different scenarios and what are the improvements in
SQL Server 2008 for SSBS across several articles.
This introductory article
on SSBS discusses on a very basic level, how it differs from MSMQ, looks at SSBS
architecture and its different components.
How SSBS differs from MSMQ/Other message based technology
Similar to SSBS, Microsoft
Message Queuing (MSMQ) is another technology that enables applications to
send and read messages from queues; it comes as a part of the Windows OS. However,
SSBS outweighs the features and benefits that MSMQ provides. For example,
in comparison with MSMQ, SSBS provides the following:
Faster transactional message processing
Reliability and enhanced recoverability
Optimize the performance
Reduced management and maintenance overhead
MSMQ has been in use a
long time and is a well-tested technology. Even though MSMQ is the preferred
technology for writing queuing and message based applications and provides
several advantages over other alternatives, in comparison to SSBS, SSBS
outweighs MSMQ in several ways in an environment where a SQL Server instance is
Advantages of SSBS over MSMQ/other queuing and message based technology
SSBS is a part of the database engine itself and is more efficient
for data-intensive queuing operations. It has improved performance, efficient
and faster transactional message processing
because transaction contexts are managed by SQL Server internally, whereas MSMQ
requires Distributed Transaction Coordinator (DTC) to coordinate transaction
context between MSMQ and SQL Server. Remember DTC uses two-phase commit
Service Broker queues are maintained in the database as database
objects and are backed up during normal database backup processes. This
provides reliability and enhanced
recoverability. MSMQ stores messages in the memory when you optimize it for
faster performance. This is fine as long as there is no failure, but if there
is any failure or you restart MSMQ service, the stored messages will be gone.
SSBS optimizes the performance by
directly writing messages to the receiver queue (bypassing the sender queue) if
the receiver queue is in same database or another database on the same server.
SSBS handles sending and receiving messages internally by database
and SSBS commands are executed over the same connection to the database (no
need of separate connection and therefore no need of DTC as would be required
when it is external to database). This ensures the access permissions of the
senders and receivers are checked by the database engine itself. Having a
single identity check for messaging and database it provides the inbuilt security,
which is easier to configure and maintain in one place.
SSBS achieves parallel processing by
letting us define the number of simultaneous queue readers. Each reader can
process the messages of a particular conversation group. To ensure messages of
a particular conversation group are being processed by a single reader it uses
a new kind of lock, called conversation group lock.
With SSBS, you store both logic and data in the database itself,
so chances of getting both out of sync is minimized to a great extent when you
restore your system after any failures.
MSMQ uses TCP/IP or HTTP
transport protocols whereas SSBS supports only TCP/IP so if you want your
messages to communicate over HTTP, MSMQ would be your choice.
Service Broker - Architecture
In SSBS terminology, the
Sender of the message is called Initiator and the Receiver of the message is
called Target. Depending on the placement of Initiator and Target the SSBS
application's architectures can be categorized in three different scenarios.
and Target in the same database in the same SQL Server instance.
and Target in different databases but still at the same SQL Server
in a database at one SQL Server instance whereas Target in another
database at another SQL Server instance.
The behavior in scenario A
and B above is almost same. In these cases, SSBS optimizes performance by
writing messages directly to the Target Queue. If, while writing message to the
Target Queue, it encounters any problem (for example the Target service is not
available, Target Queue is disabled, etc.) it keeps that message in the sys.transmission_queue
table temporarily so that it can push the message to Target Queue once it is
available. In the image below, you can see there are two databases, Initiator
and Target, on a SQL Server instance. When Initiator sends (by using SEND T-SQL
command) a message, the message is directly written to Target Queue from where
the Target reads (by using RECEIVE T-SQL command) it. If the Target sends a
response back to the Initiator, the message is directly written to Initiator
The behavior in the third
scenario, scenario C, becomes a bit complex and interesting too. In this case,
messages travel from one server to another and the network is involved, hence
SSBS ensures successful delivery of the message by storing messages temporarily
in the sys.transmission_queue temporary queue at Initiator
and removing it from there only if it receives receipt acknowledgement from the
Target. This means as long as messages are in transit they will reside in the sys.transmission_queue
queue. This is what has been depicted in the image below. You can see there is
a queue at database level and there is sys.transmission_queue
temporary queue at instance wide.
When an application issues
the SEND command to send a message, that message is stored in sys.transmission_queue
queue at Initiator, then SSBS sends that message over the network to the Target
and at same time, it marks the status of this message as waiting for
acknowledgement from the Target. On the Target, when the message is received
successfully in the Target queue, it sends the acknowledgement back to
Initiator. At this point only, the message is deleted from sys.transmission_queue
queue at the Initiator. If the Target issues a SEND command to send a response
message back to the sender, the response message goes into the sys.transmission_queue
queue at the Target, again SSBS sends that response message over the network to
the Initiator and at same time, it marks the status of this response message as
waiting for acknowledgement from the Initiator. On the Initiator, when the
response message is received successfully in the Initiator queue, it sends the
acknowledgement back to the Target. At this point only, the message is deleted
from the sys.transmission_queue queue at the Target.
Please note, the sys.transmission_queue
temporary queue is not specific to a database but rather is only for each
In this first article, I've
introduced SQL Server Service Broker (SSBS) to write message based asynchronous
applications. I briefly talked about SSBS and its capabilities. Then I showed
how it differs from other message-based technologies, for example MSMQ, in what
scenarios SSBS can be considered over other technologies and what benefits it
provides. Then I discussed the different SSBS architectural setups and how they
worked in each scenario.
My next article on this topic
discusses the different components of SSBS, their importance, how they work,
how they are related to each other and how you can create them while writing
your SSBS application, so stay tuned.
MSDN: SQL Server Service
Getting Started with SQL Server Service Broker
See All Articles by Columnist