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

October 10, 2008

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