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.