June 14, 2009 at 7:00 pm
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
June 14, 2009 at 7:14 pm
Where will these three files reside while they are waiting to be processed?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 14, 2009 at 7:48 pm
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.
June 14, 2009 at 7:50 pm
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.
June 14, 2009 at 8:33 pm
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?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 14, 2009 at 9:01 pm
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
June 15, 2009 at 6:31 am
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...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 15, 2009 at 6:59 am
OK, as to your specific design question here is how I would do it:
A) Add a table like this:
CREATE TABLE PreProcessingLog
(
CustomerIDint,
CompletionTypeint,
--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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 15, 2009 at 5:46 pm
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
June 15, 2009 at 10:55 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply