Service Broker Transactional Support in SQL Server 2005 Express Edition
February 19, 2009
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
-- 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
article of this series), you should notice a new row added to the
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