Sql Server Service Broker

Question

We are currently using a service broker to send messages back and forth, which works fine. But we wanted to group these messages using RELATED_CONVERSATION_GROUP. We wanted to use our own database with saved uuid as RELATED_CONVERSATION_GROUP = @uuid from our database, but even though we use the same uuid every time the conversion_group_id variable is different every time we get the queue.

You guys know that it’s not that I am creating a broker or receiving call, I have provided the code for creating the broker and the code for receiving the call below. thanks

Below is the code "Service Broker Creation Code"

CREATE PROCEDURE dbo.OnDataInserted @EntityType NVARCHAR(100), @MessageID BIGINT, @uuid uniqueidentifier, @message_body nvarchar(max) AS BEGIN SET NOCOUNT ON; DECLARE @conversation UNIQUEIDENTIFIER BEGIN DIALOG CONVERSATION @conversation FROM SERVICE DataInsertSndService TO SERVICE 'DataInsertRcvService' ON CONTRACT DataInsertContract WITH RELATED_CONVERSATION_GROUP = @uuid; SEND ON CONVERSATION @conversation MESSAGE TYPE DataInserted (CAST(@message_body)) 

Below is the code "Get code"

 WHILE 0 < @@TRANCOUNT ROLLBACK; SET NOCOUNT ON BEGIN TRANSACTION; DECLARE @cID as uniqueidentifier, @conversationHandle as uniqueidentifier, @conversationGroupId as uniqueidentifier, @tempConversationGroupId as uniqueidentifier, @message_body VARBINARY(MAX) RAISERROR ('Awaiting Message ...', 16, 1) WITH NOWAIT ;WAITFOR (RECEIVE TOP (1) @cID = Substring(CAST(message_body as nvarchar(max)),4,36), @conversationHandle = [conversation_handle], @conversationGroupId = [conversation_group_id], @message_body = message_body FROM DataInsertRcvQueue) RAISERROR ('Message Received', 16, 1) WITH NOWAIT Select @tempConversationGroupId = conversationGroupID from ConversationGroupMapper where cID = @cID; declare @temp as nvarchar(max); Set @temp = CAST(@tempConversationGroupId as nvarchar(max)); if @temp <> '' BEGIN MOVE CONVERSATION @conversationHandle TO @tempConversationGroupId; RAISERROR ('Moved to Existing Conversation Group' , 16, 1) WITH NOWAIT END else BEGIN insert into ConversationGroupMapper values (@cID,@conversationGroupId); RAISERROR ('New Conversation Group' , 16, 1) WITH NOWAIT END WAITFOR DELAY '000:00:10' COMMIT RAISERROR ('Committed' , 16, 1) WITH NOWAIT 

Development

Our situation is that we need to get elements from this Service Broker queue in a loop, blocking WAITFOR and transfer them to another system over an untrusted network. Items received from the queue are for one of many connections to this remote system. If the goods are not delivered to another system, the transaction for this individual item must be discarded and the item will be returned to the queue. We complete the transaction upon successful delivery, unlocking the sequence of messages that will be picked up by the subsequent iteration of the cycle.

Delays in a sequence of related elements should not affect the delivery of unbound sequences. Individual items are sent to the queue as soon as they are available, and immediately sent. Elements should be redirected to a single file, although the order of delivery even within the sequence is not strictly important.

From the loop that receives one message at a time, a new or existing TcpClient is selected from our list of open connections, and the message and open connection are passed through the chain of asynchronous I / O callbacks until the transfer is completed. Then we complete the DB transaction in which we got an item from the Service Broker queue.

How do I use Service Broker and conversation groups to help with this scenario?

Answer 1

Conversation groups is only a local concept, used exclusively for blocking: correlated conversations belong to a group, so when you process a message in one conversation, another thread cannot process the correlated message. There is no information about conversation groups exchanged between two endpoints, so in your example, all initiator endpoints ultimately belong to one conversation group, but endpoints are a separate conversation group (each group has only one conversation). The reason the system behaves this way is because the conversation groups are designed to solve a problem, for example, a travel booking service: when he receives the message "book a trip", he must reserve a flight, hotel and car rental. He must send three messages, one for each of these services ("flights", "hotels", "cars"), and then the responses will be returned asynchronously. When they return, processing must ensure that they are not processed simultaneously by separate threads, each of which will try to update the status of the trip record. In messages, this problem is known as the "message correlation problem."

Often, however, conversation groups are deployed to SSB solely for performance reasons: they provide larger RECEIVE results. The endpoints of the target can be moved together into a group using MOVE CONVERSATION , but in practice there is a much simpler trick: change the direction of the conversation. Ask your boss to start conversations (grouped), and the source will send its “updates” to conversations started by the addressee.

Some notes:

• Do not use the BEGIN / SEND / END template for recording and saving. You cannot diagnose any problems at any time, see Fire and Forget: Good for the military, but not for Service Broker conversations .
• Never use WITH CLEANUP in production code. It is intended for administrative activities of the latter resort, such as disaster recovery. If you abuse it, you deny the SSB the ability to properly track the message for the correct delivery of retries (if the message bounces off the target, for some reason it will be lost forever).
• SSB does not guarantee the order of conversations, only for one conversation. Starting a new session for each INSERT event does not guarantee that the order of insert operations is preserved in the job.