Service Broker Application Design Question

Question

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.  The 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

Answer 1

If I'm understanding what you're asking...sounds like you need a table that has a row of "file statuses" (three columns) for each user (row per user). Either:

 

1. Update file status column for each file comes in. When all are updated, send off a message to execute the process.

or

2. Send a message for each file that comes in. Update the status columns at the receiver. When all column are updated (check during message processing) then execute the process.

 

Service broker doesn't have built-in workflow for when A,and B,and C have been received do D. You need to store the status yourself and check. You'd set up a pair of services and queues (perhaps different message types) as with any "normal" broker app.

 

Cheers,

Bob Beauchemin

SQLskills

Answer 2

Hi Bob.  So, we did set up the ssb app and it worked fine ...except.. in the activation procedure for the target queue an EXEC is issued to a procedure that processes the data. It hangs every time. Yet when I simply run the proc with the same params it executes in a couple of seconds.

Any idea why it would hang when processing the queue?

Thanks
Paul

Answer 3

Paul,

Looks like this article may help you: http://rusanu.com/2006/01/12/why-does-feature-not-work-under-activation/

Answer 4

Thanks much Pawel. Interesting article.  Since I last posted here I've implemented SSB in a batch processing scenario. All seems to be working relatively well. A couple of gotcha's however.  I thought I'd ask you guys for an opinion.

1. When an activated proc is exec'ing another procedure and that procedure hangs because of let's say a deadlock . the queue, of course, just stops processing.  Is there a way to set a time limit on how long you will allow a proc to execute before you error out on it?

2. I'm getting the message  "This message could not be delivered because the conversation endpoint has already been closed." in profiler. It appears the initiator queue continues to send a message to a closed target queue. I have 6 queues running. It is only on this pair that I get the error.  I do have SP2 installed.

Also , i was wondering, if this batch system is all internal on one box and in one db is it necessary to even send a response after processing?  I have a stored proc that is called from SSIS that initiates a message on the queue. I logs it in a table and passes the log_id to the target queue which, in turn, updates the log entry with results.  No one really reads the response messages.
Does SSB need the full loop? Or could I just put the message on the queue close the conversation and forget about it?

Thanks in Advance. And Hi Bob. Hope you are well and still around.

Answer 5

Hi Paul,

Very good questions, hopefully I'll be able to address your doubts.

1. When an activated proc is exec'ing another procedure and that procedure hangs because of let's say a deadlock . the queue, of course, just stops processing.  Is there a way to set a time limit on how long you will allow a proc to execute before you error out on it?

Shouldn't the deadlock be eventually detected and one of the deadlocked spids killed? Anyway, I'm assuming that this cross-db procedure call happens after the RECEIVE statement, so as long as you're allowing it (by specifying MAX_QUEUE_READERS = N, where N>1), Sql will spin up another activation procedure if there are still some messages to be received. Therefore, even if this deadlock takes long time to be resolved, that doesn't necessarily have to delay processing of the rest of your messages.

2. I'm getting the message  "This message could not be delivered because the conversation endpoint has already been closed." in profiler. It appears the initiator queue continues to send a message to a closed target queue. I have 6 queues running. It is only on this pair that I get the error.  I do have SP2 installed.

If this is surprising to you, I'm guessing you didn't explicitly end the conversation. In this case this is most likely result of an error sent back from target to the initiator. This may be a system error which you don't explicitly raise. Make sure there are no error messages in the initiator queue. A conversation cannot continue and is considered closed after an error is raised by either side.

Also , i was wondering, if this batch system is all internal on one box and in one db is it necessary to even send a response after processing?  I have a stored proc that is called from SSIS that initiates a message on the queue. I logs it in a table and passes the log_id to the target queue which, in turn, updates the log entry with results.  No one really reads the response messages.
Does SSB need the full loop? Or could I just put the message on the queue close the conversation and forget about it?

The approach you're tempted to take is called Fire-and-forget. Remus has a good article about it. I'm not going to get into details, but let me just say that it leads to all sorts of problems, so if you can, avoid it. Even though in your case everything happens inside single database, so technically messages should be delivered into target queues in no time and things should be much simpler, still in case a queue is disabled for example, messages will go through transmission_queue, just as if they were sent to a remote Sql instance. Besides, the beauty of Service Broker is that at any point you may scale-out by moving your data/queues/services to other dbs/instances without having to change application logic (only routes need to be updated), so unless you're absolutely sure this is going to be single-db deployment forever, I would think twice before making such assumption. Of course I don't know the details of your app and may only state the general guidelines.

Regards,
Pawel

Answer 6

Pawel, thank you so much for your advice.  I'm still having problems with item 2. The undeliverable message is on SPID 22  It continues to fail.  
The message is This message could not be delivered because the conversation ID could not be associated with an active conversation. The message origin is: 'Local'. I know I'm explicitly ending conversations.
Also I see this message continuously in profiler even when I've turned off my queues and ended all conversations. I have no open conversations and nothing in the queues.

Any thoughts about what I might look at?

Thanks
Paul

Answer 7

Most likely this undeliverable message was created when you were playing with setting up the deployment and changing various settings. This should not happen when the deployment is stable. Can you share the full profiler entry for this error? Also, if this is not a production environment, can you try if restarting the server helps to get rid of the error messages?

Answer 8

Pawel sorry it took me so long to respond. I was attempting to record a trace that would be suitable for your review but I got swamped by other work. Anyway, this is not a prod server, so we did try restarting the server and this seemed to rid us of that errant undeliverable message. Further, you were right again in your suspicion that I was not properly ending conversations.  I was not. Given the fact that this is basically a batch processor I didn't think it was necessary to expicitly end the conversation after putting a message on the queue. I thought since the message was queued via a stored proc and the  connection ended afterward, this was enough to end the conversation. 

As I write this message I went back to read Remus's Fire and Forget article.  Yes, I am guilty. Looks like I will be going back to  write some initiator queue activation procedures.  I think I have, indeed, encountered the phenomena of lost messages he describes.

Also, in terms of supporting the application I'm attempting to come up with a course of action for support staff to follow to get rid of a problem message without doing too much damage to the work flow.  In my scenario, the worst situation would be to have a message hang up in a queue due to deadlocks, timeouts etc.  thus hanging the queue itself.

What would you suggest in terms of aborting a message that has hung the queue? When this happens if I do a select off of the queue, it just hangs. To date, my only recourse has been to identify the spid, kill it and then end the conversation.


Thanks much
Paul

Answer 9

Hi Paul,

I'm glad you found the culprit.

As to your last question - well... it depends. Not sure what you mean by "handing a queue". Do you mean that your procedure keeps rolling back the offending message and then the queue becomes unusable? Are you familiar with poison message handling? If not, I would suggest reading the following for starters:
http://msdn.microsoft.com/en-us/library/ms171592.aspx
http://msdn.microsoft.com/en-us/library/ms166137.aspx
http://blogs.msdn.com/sql_service_broker/archive/2008/06/30/poison-message-handling.aspx

Please let us know if after reading those you're still in trouble.

Thanks,
Pawel

Answer 10

Hi Pawel.  The issue to which I was referring had to do with a queue handler that  hung because the task it executed hung. This seems to occur because of, perhaps, an indexing issue, amount of processing etc.

Regarding the fire and forget scenario, I did attach activation procedures to my initiate queues per the article you pointed out to me, thanks.


We go into production with our SSB app next Monday. It seems to be working fine except for two issues:

1.  Once in a while I get a message that  appears to process, ie. nothing in the initiate or target queues after, but doesn't seem to do any processing.  Nothing in the Event viewer either.  I run the same request again, and it processes fine.
2.  One particular target queue seems to always end up in a Disconnected_Inbound state. My processing logic is the same for all my queues in terms of ending conversations.  Just can't seem to close these conversations.

Thanks
Paul

Answer 11

Paul,

I'm sorry but I don't understand what you mean in the first issue. Could you elaborate?

As to the second one, a conversation (not queue) switches to DISCONNECTED_INBOUND state when it receives END CONVERSATION message (including errors) from the remote side. In this state it may still receive remaining messages from the queue (but not send) and once it issues END CONVERSATION on its end, the state will change to CLOSED. The fact that you're getting DI state on the target side may indicate that the initiator endpoint ends the conversation with error unexpectedly, or you're ending the conversation explicitly from the initiator side first (which, I think, you decided to avoid).

Answer 12

Hi Pawel. We moved to production last Friday. Everything looks like it's working fine other than a couple of DI's left out there. The first question I had concerned a target queue that was taking forever to process. My question had, perhaps, nothing to do with SSB. It was a procedure that was taking forever to process via a call from the target activation procedure.  I'm wondering what would be the best way of curtailing such a call in order to let the rest of the queue process.  I would like to give my support staff some direction about dealing with such a case, so I don't get the call myself.

Thanks again!
Paul