One of the most common deployed patterns of using Service Broker is what I would call ‘data push’, when Service Broker conversations are used to send data one way only (from initiator to target). In this pattern the target never sends any message back to the initiator. Common applications for this pattern are:
· ETL from the transactions system to the data warehouse
· audit and logging
· aggregation of data from multiple sites
Implementing this pattern is usually very trivial: modify a stored procedure to SEND a message (a datagram) or add a trigger on a table to issue the SEND. But as soon as this is done, the next question that arises is: do I start a new conversation for each message or do I try to reuse conversations? My answer would be that reusing conversations for such patterns (one way data push) is highly recommended.
Here are some of my arguments for reusing conversations:
· conversations are the only elements that are guaranteed to preserve order in transmission (even locally!). E.g. if the order of the events audited is important, they have to be sent on the same conversation
· reusing conversations has a significant performance impact (see http://rusanu.com/2007/04/03/orlando-slides-and-code/)
o the cost of setting up and tearing down a conversation for each message can influence the performance by ~4x
o the advantage on having multiple messages on the same conversation can speed up processing on the RECEIVE side by a factor of ~10x
- conversations are the ‘unit of work’ for the Service Broker background and as such resources are allocated in the system according to the number of active conversations (‘active’ = have some activity pending, like sending a message or replying with an acknowledgement). The number of pending messages has less influence on resource allocation (2 dialogs with 500 messages each will allocate far less resources than 1000 dialogs with 1 message each)
The next step is to decide just how many conversations to use. One conversation would completely preserve order of operations, but because each SEND locks the conversation exclusively, would serialize every transaction and lead to serious scalability problems. My favorite scheme is to have one conversation per user connection. This would preserve the order of actions on one connection, which is most often the desired granularity, and will scale up easily because is very unusual for connections to share transactions (just how often is it that you use sp_getbindtoken?).
Next is an example stored proc that reuses conversations based on the conversation arguments (from service, to service, contract) and current connection ID (@@SPID). It’s based on a table to store the conversation associated with each @@SPID.
-- This table associates the current connection (@@SPID) -- with a conversation. The association key also -- contains the conversation parameteres: -- from service, to service, contract used -- CREATE TABLE [SessionConversations] ( SPID INT NOT NULL, FromService SYSNAME NOT NULL, ToService SYSNAME NOT NULL, OnContract SYSNAME NOT NULL, Handle UNIQUEIDENTIFIER NOT NULL, PRIMARY KEY (SPID, FromService, ToService, OnContract), UNIQUE (Handle)); GO -- SEND procedure. Will lookup to reuse an existing conversation -- or start a new in case no conversation exists or the conversation -- cannot be used -- CREATE PROCEDURE [usp_Send] ( @fromService SYSNAME, @toService SYSNAME, @onContract SYSNAME, @messageType SYSNAME, @messageBody VARBINARY(MAX)) AS BEGIN SET NOCOUNT ON; DECLARE @handle UNIQUEIDENTIFIER; DECLARE @counter INT; DECLARE @error INT; SELECT @counter = 1; BEGIN TRANSACTION; -- Will need a loop to retry in case the conversation is -- in a state that does not allow transmission -- WHILE (1=1) BEGIN -- Seek an eligible conversation in [SessionConversations] -- SELECT @handle = Handle FROM [SessionConversations] WHERE SPID = @@SPID AND FromService = @fromService AND ToService = @toService AND OnContract = @OnContract; IF @handle IS NULL BEGIN -- Need to start a new conversation for the current @@spid -- BEGIN DIALOG CONVERSATION @handle FROM SERVICE @fromService TO SERVICE @toService ON CONTRACT @onContract WITH ENCRYPTION = OFF; INSERT INTO [SessionConversations] (SPID, FromService, ToService, OnContract, Handle) VALUES (@@SPID, @fromService, @toService, @onContract, @handle); END; -- Attempt to SEND on the associated conversation -- SEND ON CONVERSATION @handle MESSAGE TYPE @messageType (@messageBody); SELECT @error = @@ERROR; IF @error = 0 BEGIN -- Successful send, just exit the loop -- BREAK; END SELECT @counter = @counter+1; IF @counter > 10 BEGIN -- We failed 10 times in a row, something must be broken -- RAISERROR ( N'Failed to SEND on a conversation for more than 10 times. Error %i.' , 16, 1, @error) WITH LOG; BREAK; END -- Delete the associated conversation from the table and try again -- DELETE FROM [SessionConversations] WHERE Handle = @handle; SELECT @handle = NULL; END COMMIT; END GO -- Test the procedure with some dummy paramaters. -- The FromService ‘ST’ must already exist in the database -- exec usp_Send N'ST', N'Foobar', N'DEFAULT', N'DEFAULT', 0x01020304;
This shows how to reuse the conversations, but there are still plenty of details one has to think of, like:
- how long should a conversation be used for?
- who should end these reused conversations?
- how to treat conversation errors?
I’ll try to visit these topics in a future post.