SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Service Broker queue design


Service Broker queue design

Author
Message
paul_ny_murray
paul_ny_murray
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 77
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
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73384 Visits: 9519
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."
paul_ny_murray
paul_ny_murray
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 77
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.
paul_ny_murray
paul_ny_murray
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 77
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.
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73384 Visits: 9519
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."
paul_ny_murray
paul_ny_murray
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 77
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
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73384 Visits: 9519
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."
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73384 Visits: 9519
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."
paul_ny_murray
paul_ny_murray
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 77
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
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73384 Visits: 9519
Glad I could help.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search