Service Brokers : Lots of conversations or just the one?

Question

Good afternoon,

I've just started to play around with Service Brokers and am having a bit of a dilemma.  The aim of my task is to allow backups to be copied from the live systems onto a backup storage system (where they will then be restored).  We dont backup directly to this backup storage system, instead we backup locally, and copy the backup afterwards.

I'm having a go at this using service brokers (mostly because I think it might be a fun thing to learn).

I've written 2 stored procedures at the moment.  The first starts a conversation, and then loops through writing messages after each successful backup.
The second should be picking up completed backups and then copying them to their destination.

The problem I'm having is that if I run the stored procedures simultaneously, I end up ending my conversation at the wrong point.  I'm wondering therefore if it might be easier to simply have potentially 20 conversations happening at the same time, and ending each one after each backup is copied over.

I've included the stored procedures just so you can get an idea as to what's happening, but I'm really just trying to determine if it's OK to have lots of conversations happening at the same time, or if the whole point is to just have one conversation happening and working around that.

These are both only in testing at the moment (so excuse the hard coding) while i figure out the best way to do it.

ALTER PROCEDURE sp_BackupDatabase
as

declare @BackupFolder varchar(100)
declare @BackupDestination varchar(100)
-- Begin Dialog using service on contract
DECLARE @DBCopyDialog AS UNIQUEIDENTIFIER;
DECLARE @Message AS NVARCHAR (128);



BEGIN DIALOG CONVERSATION @DBCopyDialog
    FROM SERVICE DBCopySendService
    TO SERVICE 'DBCopyReceiveService'
    ON CONTRACT DBCopyContract
    WITH ENCRYPTION = OFF;

DECLARE MY_CURSOR CURSOR
    FOR SELECT NAME
        FROM   SYS.DATABASES
        WHERE NAME NOT IN ('TempDB');

OPEN My_Cursor;
DECLARE @DatabaseName AS VARCHAR (40);
FETCH NEXT FROM MY_Cursor INTO @DatabaseName;

WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        IF (@@FETCH_STATUS <> -2)
            SELECT @DatabaseName;
			set @BackupFolder = 'C:\Software\'
			set @BackupDestination = @BackupFolder + @DatabaseName + '.bak'
			
			exec ('BACKUP DATABASE ' + @DatabaseName  + ' to disk = ''' + @BackupDestination + '''')

			-- Send messages on Dialog
			SET @Message = N'xcopy ' + @BackupDestination +' \\11.14.1.207\Software\';
			SEND ON CONVERSATION @DBCopyDialog MESSAGE TYPE DBCopyCmd (@Message);


        FETCH NEXT FROM MY_CURSOR INTO @DatabaseName;
    END

CLOSE MY_CURSOR;
DEALLOCATE MY_CURSOR;

ALTER PROCEDURE sp_ProcessDBCopyQueue

AS
BEGIN
    DECLARE @message_type AS VARCHAR (100);
    DECLARE @dialog AS UNIQUEIDENTIFIER, 
@message_body AS VARCHAR (MAX);
    WHILE (1 = 1)
        BEGIN -- Receive the next available message from the queue 
            BEGIN TRANSACTION;
            WAITFOR (RECEIVE TOP (1) @message_type = message_type_name, @message_body = CAST (message_body AS VARCHAR (MAX)), @dialog = conversation_handle FROM dbo.DBCopyReceiveQueue),  TIMEOUT 5000;
            IF (@@ROWCOUNT = 0
                OR @message_body IS NULL)
                BEGIN
                    COMMIT TRANSACTION;
                    BREAK;
                END
            ELSE
                BEGIN
                    -- BEGIN THE PROCESSING
                    BEGIN TRY
                        EXECUTE xp_cmdshell @message_body;
                        COMMIT TRANSACTION;
                    END TRY
                    BEGIN CATCH
                        ROLLBACK;
                    END CATCH
                END
            END CONVERSATION @dialog;
        END
END

Answer 1

Hi,

you have done an excellent job so far. you present an issue that is commonly the point of decision making between 2 types of service broker set up. I use the "phone call" analogy to explain this better.

1. The set up that you have right now, is analogous to making 1 phone call ( 1 BEGIN Dialog) and passing multiple messages ( multiple SEND) on that same phone call. In this set up, you want to disconnect that phone cann (fire off the END CONVERSATION) only after you have read all the messages from the Queue. This architecture is very well suited for high throughout scenario

2. for your situation, i would recommend the set up where you pass 1 message ( SEND ) on one phone call ( 1 BEGIN Dialog) and disconnect that call (END CONVERSATION) after receiving each message. This architecture is well suited when you need good deal of isolation between messages and it allows you to multi thread the reads 

With that said, the only change you would need to make to your sp_backupdatabase proc would be to move the BEGIN DIALOG inside the CURSOR WHILE LOOP. hope this helps

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 2

Hi Sanil,

I took your advice and the solution is now 99% complete.  I just need to perform some tidying up and it'll be good to go.  I do have another question tho, which I hope is relatively straight forward to answer.

The above procedures will have ServerA and ServerB, where ServerA is a central location, and ServerB will be where the backups take place (the queue will be used to copy the backups from ServerB to ServerA and ultimately restore the databases).

Now, if we expand the model to being ServerA, ServerB and ServerC, and then copy the backups from ServerB and ServerC onto ServerA, then what new objects would I need to create on ServerA?

I assume I can reuse the Message Types (keep the names the same among all servers), however do I need a new ReceiverService?  I assume I need a new route creating on ServerA to reach ServerC?

I'm trying to keep it as simple as possible and where possible, reuse objects/services/contracts to keep it as simple as possible.

Regards

Andy

Answer 3

Hi Andy,

What version of sql server are you on ? If you are on 2012, service broker has a new multicast feature, where you can send the same message to multiple targets. See this blog for an example - http://sqlwithsanil.com/2012/05/18/sql-server-2012-whats-new-in-service-broker/

Do note that that event with the new multicast feature, you need to create multiple conversation handles ( 3 BEGIN Dialogs in that example) and use these 3 conversation handles in SEND.

If you don't have 2012, you can emulate the same in 2008 by using 3 separate SENDs. You can reuse the message type and contract. You will need new target service and target queue (on server c), and set up a new route as well. You can update your stored proc to have 2 BEGIN Dialogs ( 1 for server A to B and other for server A to C) and consequently 2 SENDs ( 1 for server A to B and other for server A to C).

You might find it quite interesting to read this article about using service broker for database maintenance tasks - http://www.sqlservercentral.com/articles/Service+Broker/76715/

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 4

Hi Sanil,

We're using a mixture of SQL 2005 and SQL 2008 at the moment.  I dont think that sending 1 command to multiple destinations would be relevant for what I'm aiming for at the moment, tho I could definitely see a use of that in the future.  Thanks for the heads up.

I think I've actually got my solution sorted out now, and have hooked my "process" stored procedure so it is automatically triggered when the queue is activated.  This works brilliantly, however I'm not certain, but I THINK I change the procedure to no longer require the "waitfor".

My queue is now created like this :

Create Queue MyQueue WITH status= ON,
ACTIVATION (
	STATUS=ON,
	PROCEDURE_NAME = sp_PopulateRestoreList,
	MAX_QUEUE_READERS = 1,
	EXECUTE AS 'dbo' );

So, in theory, this should be called so long as there are values in the queue, and thus negating the need for me to include a "WAITFOR" in my initial "Processing" stored procedure.

Regards

Answer 5

Hi Andy,

You are right, a WAIT FOR is not needed in the RECEIVE proc used in automatic activation. you can think of automatic activation like a trigger on a Queue, which will fire off your stored proc only when there is a message on the queue.

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 6

Service broker will not re-active the procedure for 5 seconds after one was activated. So if you decide not to enclose the receive in a waitfor() call with a timeout of at least 5 seconds in your auto activated procedure, you risk a delay of at most 5 seconds for every message. For this reason I advise to still enclose the receive call within a waitfor() with a timeout of 10000 milliseconds. Doing so avoids the -on average- 2,5 second delay that would occur if the next message arrives milliseconds after your routine came to the receive call and thus makes your process more responsive.

---

SQL expert for JF Hillebrand IT BV - The Netherlands.