Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Service Broker queue design Expand / Collapse
Author
Message
Posted Sunday, June 14, 2009 7:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 4, 2010 11:03 AM
Points: 5, Visits: 19
Hi. We're thinking of implementing Service Broker to expedite incoming traffic and user requested processing. The short of it is we have a process that needs to kick off when three associated files hit the database. Every time customer 1's files come in they are reviewed by the user and he/she requests the process to execute. There are n number of customers each having their own set of 3 files. At this point its about 300 files. The final process should not be executed before all three files are present. We have to cover the fact that the files for a given customer can come in at any time and in any order. It maybe the case that a user has sent a message to execute the final process for a given customer before all the files for that customer are in. In which case we'd like to wait until all files are in and then process the user's request.

We're stumped about how to set up queues and conversations to manage these scenarios.

Anyone have any ideas about how to design server broker for this?

Thanks in Advance
Post #734736
Posted Sunday, June 14, 2009 7:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 9,294, Visits: 9,484
Where will these three files reside while they are waiting to be processed?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #734739
Posted Sunday, June 14, 2009 7:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 4, 2010 11:03 AM
Points: 5, Visits: 19
The plan is to process the files into staging tables and then put a message in a queue announcing their arrival with a batch id.
Post #734750
Posted Sunday, June 14, 2009 7:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 4, 2010 11:03 AM
Points: 5, Visits: 19
I might mention that that will be done through SSIS. Another issue we're trying to mitigate is that all these files are hitting our server at about the same time causing deadlocks and general havoc.
Post #734752
Posted Sunday, June 14, 2009 8:33 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 9,294, Visits: 9,484
OK, if I understand this, then this is the kind of flow that you are looking for:
              Wait For All
------------
|
[DocType1]------>|
|
[DocType2]------>|
|----->{PostProcessing}
[DocType3]------>|
|
{UserResuest}---->|
|


Is this right? Also, do you already know how to use Service Broker?


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #734765
Posted Sunday, June 14, 2009 9:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 4, 2010 11:03 AM
Points: 5, Visits: 19
yes very good that's it.

I am familiar with Server Broker to the extent that I have taken the virtual lab, researched the internet, and am now reading Pro Sql Server Service Broker. I am a professional SQL Server/.Net developer but haven't used Broker in an application yet.
Thanks
Post #734771
Posted Monday, June 15, 2009 6:31 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 9,294, Visits: 9,484
That's Klaus's book, right? That's the best book by far, however two words of warning:

1) If you haven't actually setup your first working demo or app yet (even a toy one), then don't follow Klaus's book just yet. You need to get through at least one (and only in SQL) first before you go any further because it turns out that there's a dozen little things that can derail you before you can get even a demo app to work. There is a ZIP file of a technical presentation with (working!) code examples at my blog site (www.MovingSQL,.com) called "The Top 10 Reasons You Aren't Already Using Service Broker". Download it and take a look, it's basically a troubleshooting guide for the initial steps of putting a working app or example together.

2) Klaus's book is very gung-ho on SQLCLR, but you definitely want to get a working example in SQL before going to CLR, because CLR has a raft of it's own limitations. If you're already familiar with them, thats fine, but IMHO, you don't want to be working out the problems of (1) and (2) at the same time.

more in a minute...


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #734936
Posted Monday, June 15, 2009 6:59 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 9,294, Visits: 9,484
OK, as to your specific design question here is how I would do it:

A) Add a table like this:
CREATE TABLE PreProcessingLog
(
CustomerID int,
CompletionType int,
--Types are 1=UserRequest, 2=DocType1, 4=DocType2, 8=DocType3
Primary Key Clustered (CustomerID, CompletionType)
)

B) When the User request is issued or at the completion of each Document Type's pre-processing, INSERT the appropriate record into this table, then send the corresponding message to your service broker service.

C) The queue activation procedure should do the following:
-- 1. Begin a transaction
-- 2. Read the message from the queue
-- 3. Execute the following check:
IF (
SELECT SUM(CompletionType)
FROM PreProcessingLog
WHERE CustomerID = @RecvdCustID
) <> 15
BEGIN
COMMIT TRANSACTION
RETURN 0
END

-- 4. Otherwise, continue to do the post-processing
-- 5. Delete all of the CustomerID=@RecvdCustID rows from PreProcessingLog
-- 6. Commit the transaction & return.

Now you can also throttle the intensity of the post-processing by limiting the number of concurrent queue activations.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #734954
Posted Monday, June 15, 2009 5:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 4, 2010 11:03 AM
Points: 5, Visits: 19
Thanks so much Barry. That logic basically works for me. It also answers a fundamental question I had. Do I have to write code to do this.. ie sum = 15 or is there some way I can configure Broker to do it natively in conversation groups. I guess I would need a message priority that Broker doesn't really do right now. Klaus gets into that a little in his book but I'd rather get the basic logic down first.


I will check out your site. I can use all the advise I can get.

Appreciate it.

-Paul
Post #735386
Posted Monday, June 15, 2009 10:55 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 9,294, Visits: 9,484
Glad I could help.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #735449
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse