Hello all,
I've been digging through this forum for an hour or two, and not found anyone having the same problem we are having.
Basically, we have a Windows service that uses sqldependency to get a notification on the existence of records to be processed. When the service receives the notification, it spawns multiple threads which each work on one of the records in the table, moving the records to a "completed" table. When the table is empty, the service sets up the sqldependency again and waits to do the whole thing over.
Our problem is, occasionally it stops being triggered. We don't know if the service stops listening, or the notification stops being sent, or if there is some other situation where the network connection between the db and the service is severed and the pieces can't resume their conversation.
In order to "fix" the problem, we restart the service. It does it's normal run of cleaning out the table, then sets the notification up again and works for another few hours before it flakes out again.
At the moment, we can't figure out how to determine whether it is the app, or the database, or network (or some combination of any of these parts) that is failing. If I could get some guidance as to how to start narrowing it down, I would be very grateful!
Let me know if you need more specific info, and thanks for any help you guys can offer!
Mike
Rather that answer with technical details about SqlDependency inner workings (with questionable success since I myself don't know how the client CLR part works exactly), I'll tell you something else: you're using the wrong tool for the job. The pattern you describe fits perfectly a workflow based on Service Broker queues. Rather than insert into a table and have SqlDependency react to the changes, send messages to a Broker service and have the application waitfor messages in a queue.|||Thanks for a quick response Remus!
Here are some specifics about our system. I'm trying to figure out if your solution will fix our problem.
The system is designed to have web services, or in the case of applications more closely connected to the database, stored procedures that populate a messagequeue. It is absolutely necessary that any message that gets into this queue is logged, and processed. These messages could define: an email that goes to a customer with an attached file, an emailed error log from a website, the description of an ftp to be sent to an outside server, or even the description of a fax that will ultimately be packaged and sent to an offsite fax delivery service. The source of these messages vary from internal systems, to website "contact us" pages, to external Windows apps consuming one of the queuing web services.
The Windows service does the heavy lifting, determining that this an email, this is a fax, this is an FTP, etc... then using the appropriate method to send the message. Of course, along the way it is popping messages off the queue, and logging success or failure to a completed message log. (This is where we are failing... the service stops being notified, or stops listening to the changes to the message queue.)
The final piece of this is a management app that allows a customer service rep to see in realtime the condition of the queue with statistics like: how many emails were sent? did any of them fail? how many items are sitting in the queue? It also allows messages to be paused or requeued for any reason.
I think I understand what you are saying... put the messages directly in a Broker Service queue instead of a table. But does this insure that every message will get sent, even in the case of failure of the hardware hosting the broker service, and can (and should) the Broke Service queue be queried by multiple paranoid customer service reps using a client application? (The transparency of the queue is one of the major design goals of the system, as the state of the last delivery system was completely closed off from the average business side user.)
Let me know what you think, and thanks again for your response!
Mike
|||I'd say this fits Service Broker like a glove.
Instead of inserting into a table, send messages to a queue. Queues are queriable (with SELECT ... FROM [<queuename>]) so you can always monitor the state of messages. Queues are normal database objects, so they benefit from all the availability/reliability of a database: they are backuped/restored with the database, they failover with clustering, they failover with mirroring. All queue operations are transacted, so they follow the normal pattern of COMMIT/ROLLBACK. For the kind of jobs you describe Service Broker services/queues have many advantages:
- dedicated queues for each type of application (fax, email, ftp). You can send the message to the appropiate queue and have each queue serviced by a dedicated application (service). This way you will not block fast operations (email) behind slower ones (fax) and you can govern the resources allocated for each type of operation (e.g. more queue readers to service the fax vs. ftp)
- reliable retry logic. This is a huge freebie you get. You can use the conversation timers to post a message to yourself for a retry, like this:
BEGIN TRANSACTION
RECEIVE MESSAGE
BEGIN TIMER
COMMIT
DO lengthy processing with possible failure (i.e. fax)
IF SUCCESS
RESET TIMER
If the timer ever pops, you simply follow the same logic again (set a new timer, retry the operation). No need for fragile application based retry timers: conversation timers are durable, so they will pop and send you a message to retry even after a server restart or a clustering/mirroring failover. Rushi Desai has a sample showing how to do this for a processing that is a Web service call, you can easily follow the same pattern for any other kind of processing (fax, ftp etc), see http://blogs.msdn.com/rushidesai/archive/2006/04/19/746827.aspx
- easy scale out. Service Broker applications are agnostic to the location of the queue, so you can easily scale out specific services (e.g. fax) to a separate machine. In addition Service Broker comes with built in load-balancing logic so you can scale out to a farm of dedicated server w/o any change in the application
- messages are queriable, you can SELECT across application queues and as well acros the system outbound queue (sys.transmission_queue)
- you can easily add intermediate worflow steps, like for fan out: send one message to one service, it gets fanned out to manny services to copy the same message to your ftp/email/fax based on whatever business rules you have. The logic can be implemented as an activated procedure, no need for an external process to service this part.
- optional built-in XML schema validation for you messages, so the application is guaranteed to receive only conforming XML payloads
- message correlation lock-out. If you have correlated messages (e.g. you must first send this email, and only then that email) Service Broker dequeue semantics can lock-out other concurent readers from even seeing that message, see http://msdn2.microsoft.com/en-us/library/ms171615.aspx
HTH,
~ Remus
No comments:
Post a Comment