SQL Server 2005 Express Edition – Part 32 – Distributed Service Broker Environment – Conducting Dialogs

In the recent articles of our series covering features available in SQL
Server 2005 Express Edition, we have introduced asynchronous, queue-based
messaging functionality offered by the Service Broker component. So far, in
addition to providing a general overview of its characteristics, we have
described a simplified example of a conversation between two services residing
in the same database. We also have listed the initial steps necessary to
facilitate an equivalent setup in a distributed environment, including creation
of endpoints and routes (in addition to message types, queues, and services,
identical to the ones we have used earlier), as well as assigning required
permissions to SEND and RECEIVE statements. In this installment,
we will focus on establishing, conducting, and terminating a sample dialog,
which leverages these constructs.

As you might recall from our earlier discussion, a Service Broker
conversation starts with the BEGIN DIALOG
statement executed on the initiator. We will use our //databaseJournal.com/SQL2005EX/ServiceBroker/svcSend
service residing in the SQL Server 2005 Express Edition-based dbSBExp01 database for this purpose,
however the process would not differ if we decided to reverse its direction
(and start the conversation from the srvExp01
SQL Server 2005 Enterprise Edition instance). Note that the statements
presented here are identical to the ones we have employed in the
single-database implementation (since the intricacies of operating in a
distributed environment are handled transparently by the Adjacent Broker
Protocol mechanism using endpoints and routes defined by us), although, unlike
before, they are executed at two separate locations, as indicated by the
comments below:

-- on srvExp01
USE dbSBExp01
GO
DECLARE @convHandle uniqueidentifier
BEGIN DIALOG CONVERSATION @convHandle
 FROM SERVICE [//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend]
 TO SERVICE '//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv'
 ON CONTRACT [//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV]
 WITH ENCRYPTION=OFF;

Once the conversation is established and identifiable via its handle (whose value
you can determine by querying the content of the sys.conversation_endpoints view for an entry with status of SO, indicating STARTED_OUTBOUND),
we are ready to send a message to the target service. This is done using the SEND statement:

-- on srvExp01
USE dbSBExp01
GO
DECLARE @convHandle uniqueidentifier
SELECT  @convHandle = conversation_handle FROM sys.conversation_endpoints
 WHERE state = 'SO' AND far_service = '//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv';
SEND ON CONVERSATION @convHandle
 MESSAGE TYPE [//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV] (N'Aloha bruddah')

Assuming that you have successfully implemented all previous steps, the
message should arrive at the destination queue qRecv in the target database dbSBEnt01. (If that is not the case,
review the content of the sys.transmission_queue
in the dbSBExp01 database,
which should help you determine the reason of the failure, or launch SQL Server
Profiler on the target and monitor progress of the conversation by tracking
Service Broker related events). Once you have confirmed the outcome, switch
over to the srvEnt01
instance and invoke the RECEIVE
statement (to extract the content of qRecv
queue associated with the //databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv
service, which subsequently will be used to populate tbMsgs table), followed by the call to END CONVERSATION with a handle of the
current conversation as its sole parameter:

-- on srvEnt01
USE dbSBEnt01
GO
DECLARE @convHandle UNIQUEIDENTIFIER;
DECLARE @msgTypeName SYSNAME;
DECLARE @status TINYINT;
DECLARE @srvName NVARCHAR(512);
DECLARE @srvConName NVARCHAR(256);
DECLARE @msgTypeValidation AS NCHAR(2);
DECLARE @msgBody NVARCHAR(50);
DECLARE @cmd AS NVARCHAR(50);
WAITFOR(RECEIVE TOP(1)
 @convHandle = conversation_handle,
 @msgTypeName =  message_type_name,
 @status = status,
 @srvName = service_name,
 @srvConName = service_contract_name,
 @msgTypeValidation = validation,
 @msgBody = CAST(message_body AS NVARCHAR(50))
 FROM qRecv), 
 TIMEOUT 1000;
IF(@@rowcount != 0)
  BEGIN
 INSERT INTO 
  tbMsgs(convHandle, msgTypeName, status, srvName, srvConName, msgTypeValidation, msgBody)
  VALUES(@convHandle, @msgTypeName, @status, @srvName, @srvConName, @msgTypeValidation, @msgbody)
 END CONVERSATION @convHandle;
  END
END

If you check the content of the qSend
queue in our dbSBExp01
database at this point, you should find a system message with message_type_id of 2, which indicates
that the target will no longer process any additional messages as part of the
same conversation. (At the same rate, the value of the state column of an entry representing
our dialog in the sys.transmission_queue
view will change to DI,
which designates DISCONNECTED_INBOUND).
To terminate our dialog, we will retrieve this message from the queue (with the
RECEIVE statement) and
invoke END CONVERSATION on
the initiator.

-- back on srvExp01
USE dbSBExp01
GO
DECLARE @convHandle UNIQUEIDENTIFIER;
DECLARE @msgTypeName SYSNAME;
DECLARE @status TINYINT;
DECLARE @srvName NVARCHAR(512);
DECLARE @srvConName NVARCHAR(256);
DECLARE @msgTypeValidation AS NCHAR(2);
DECLARE @msgBody NVARCHAR(50);
DECLARE @cmd AS NVARCHAR(50);
WAITFOR(RECEIVE TOP(1)
 @convHandle = conversation_handle,
 @msgTypeName =  message_type_name,
 @status = status,
 @srvName = service_name,
 @srvConName = service_contract_name,
 @msgTypeValidation = validation,
 @msgBody = CAST(message_body AS NVARCHAR(50))
 FROM qSend), 
 TIMEOUT 1000;
IF(@@rowcount != 0)
  BEGIN
 INSERT INTO 
  tbMsgs(convHandle, msgTypeName, status, srvName, srvConName, msgTypeValidation, msgBody)
  VALUES(@convHandle, @msgTypeName, @status, @srvName, @srvConName, @msgTypeValidation, @msgbody)
    END CONVERSATION @convHandle;
END

As the result of this process, both messages should be removed from their
respective queues (and inserted into our auxiliary tables). However, you should
still be able to locate an entry associated with the conversation we just
completed in the sys.conversation_endpoints
view in dbSBEnt01 database
on the target with the state of CD
(designating CLOSED), which
will remain there until a point in time indicated by the value of security_timestamp column is reached.

In the next article of our series, we will start an overview of more
elaborate examples of Service Broker dialogs, involving such features as
certificate-based encryption and authentication.

»


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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles