Service Broker Transactional Support in SQL Server 2005 Express Edition

In the recent installments of our series covering topics related to SQL
Server 2005 Express Edition, we have been focusing on specifics of its Service
Broker implementation. Throughout our discussions, we have been applying a
certain degree of simplification, primarily for the sake of clarity and
conciseness. It is important, however, to note that some of these temporary
shortcuts should be treated purely as such and avoided in
"real-world" deployments.
An important example in this category
is the role of transactions in processing Service Broker dialogs. In this
article, we will provide its overview and present an example illustrating its
usage.

Transactional functionality is essential in database operations, in order to
satisfy atomicity, consistency, isolation and durability requirements (which
are frequently referenced collectively using the acronym ACID). These terms
describe features critical to data processing, which ensure that each
modification has a clearly defined outcome, resulting in either success or
failure (atomicity), thus preventing potential corruption of data
(consistency), executes independently from other changes (isolation), and, once
completed, leaves underlying data in a defined state until another transaction
takes place (durability). Complying with these rules is especially important
when dealing with multiple, interdependent changes. The scope of these changes
can vary, ranging from a few database objects within the same database or
server instance, to any number of distributed systems, as is frequently the
case in online processing or messaging scenarios. Incorporating several
distinct activities into the same transaction guarantees that if any of them fails,
all of the remaining ones are rolled back, returning the system to its original
state. (In other words, all of them have to either succeed or fail – no other
outcome is permitted).

Service Broker relies on the transactional mechanism inherent to SQL Server
(which, incidentally, constitutes one of its bigger strengths). This means that
you can incorporate its operations into arbitrary transactions, in order to
ensure that a sequence of related actions (including processing of messages and
related database updates) has a determinate, consistent outcome (either all of
them succeed or fail). Enclosing such sequence into a transaction means that
rollback will effectively prevent messages from being sent out if any associated
SQL Server 2005 database-bound actions fail (and, in the equivalent manner,
commit will serve as the guarantee that all of them have completed).

Let’s consider our most recent example illustrating a secure Service Broker
dialog with internally activated stored procedure dbo.cspProcessqRecv. We will first take a look at the
T-SQL code executed on the initiator side (hosted on a SQL Server 2005 Express
Edition instance), responsible for starting the conversation. To demonstrate
transactional characteristics of Service Broker, we will incorporate into this
process an additional task, which inserts a row representing each message
targeting //databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv
service into the tbMsgSend
table. We will enclose all of these actions (including BEGIN DIALOG, SEND ON CONVERSATION, and INSERT INTO) into a single transaction, whose
boundaries are marked with BEGIN
TRANSACTION
and COMMIT
TRANSACTION
statements. We will also take advantage of BEGIN TRYEND TRY and BEGIN CATCHEND CATCH blocks, which
allow you to redirect the flow of execution whenever an error is detected (for
more information regarding their syntax and characteristics, refer to SQL
Server 2005 Books Online
). In case of such an occurrence, we invoke ROLLBACK TRANSACTION statement, which
reverts any actions that have been performed since the beginning of the current
transaction :


— on srvExp01
USE dbSBExp01
GO

BEGIN TRY

BEGIN TRANSACTION;

DECLARE @convHandle UNIQUEIDENTIFIER;
DECLARE @msgTypeName SYSNAME;
DECLARE @status TINYINT;
DECLARE @srvName NVARCHAR(512);
DECLARE @srvConName NVARCHAR(256);
DECLARE @msgTypeValidation AS NCHAR(2);
DECLARE @msgBody AS NVARCHAR(400);

SET @srvName = ‘//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv’
SET @srvConName = ‘//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV’
SET @msgTypeName = ‘//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV’
SET @msgBody = ‘Aloha bruddah’

BEGIN DIALOG CONVERSATION @convHandle
FROM SERVICE [//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend]
TO SERVICE @srvName
ON CONTRACT @srvConName
WITH ENCRYPTION = ON;
SEND ON CONVERSATION @convHandle
MESSAGE TYPE @msgTypeName (@msgBody);
INSERT INTO
tbMsgSend(convHandle, msgTypeName, srvName, srvConName, msgBody)
VALUES(@convHandle, @msgTypeName, @srvName, @srvConName, @msgbody);

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

As long as no issues are encountered (and assuming that you have configured
automatically activated stored procedure on the target side, as described in
the previous
article
of this series), you should notice a new row added to the tbMsgSend table representing the newly
sent out message, its acknowledgement in the qSend
queue (constituting a confirmation that its delivery and processing have been
successful), and a single conversation entry in the sys.conversation_endpoints table with DISCONNECTED_INBOUND state (since, in on
our example, the target service ends the conversation once a message is
extracted from qRecv queue).
You can also find out what would be the outcome of a transaction that is
interrupted by an obstacle preventing database updates or Service Broker
actions from occurring. One way to accomplish this would be to set the UNIQUE constraint on the msgBody column of tbMsgSend table and attempt sending two
identical messages, which should lead to the transaction rollback, leaving
content of the table as well as the qSend
queue and sys.transmission_queue
catalog view intact (with no impact on the target).

We could further fine tune tasks responsible for error handling (in order to
accommodate different types of issues that might surface when executing our
T-SQL code) by extending logic incorporated into the TRY ... CATCH blocks. (Review Using
TRY…CATCH in Transact-SQL
Books Online article for more information on
this subject), but the example presented above satisfies the goal we set for
ourselves in the beginning of this article. In essence, it demonstrates that it
is possible to enclose a number of statements responsible for processing
Service Broker queues and associated database actions into a single transaction
in order to facilitate their consistent outcome. In the next article of this
series, we will describe how to apply a similar approach to a target and its
internally activated stored procedure, including more elaborate error handling
functionality.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles