ASP.NET Long Running SQL Server Procedure - How to handle? Messaging?

Question

I have a web application that needs to fire off a long-running SQL Server procedure (to rebuild a table) when certain criteria are met. What is the recommended procedure to do this?

I was thinking, when the criteria are met a record would be inserted into the database and a scheduled SQL server job would check that table at a specified interval and fire off the procedure. It seems a little hackish though.

What is the preferred way to handle this? As a related question, what is the preferred way to handle future events (i.e. send an email 10 days after a user signs up?)

Is this type of scenario what messaging is all about?

Answer 1

If you don't have to worry about the results after making a call to execute the stored procedure, it might be good to run it asynchronously.

Answer 2

Your approach is definetly viable and is one way I've seen it done. For example in a complex web which had gigabytes of inventory data, the company implemented basically what you described to allow partial updating of the inventory data throughout the day.

Some other approaches I've seen involved a scheduling service sitting on the application server which would call a piece of code that would handle the event. (Such as the second part of your question). Having this in the application tier allows you to scale out the processing of the events, and you can use the DB as a backing store to ensure you don't loose any events.

Does your user need to see the results of the action? You could also kick off an asynchronous call to process the results, and then hang on to the user and provide status information. If you don't want to hang on the user then your going to need a status table that will store the result.

The last thing I would look is the service broker. I've not used it yet and am not sure if it is appropiate but I would look into it as an option. We had started looking into it as a way to kick off long running disconnected workflows, but I left the company before we got anywhere.

Answer 3

I'd like to notify the user who made the changes that required the rebuild (a) that the rebuild was going to take place and (b) that the rebuild was complete. I'd like to do this both by a message in the UI, and an email. I'd rather the application (not the DB handle the emailing).

I'd like to be able to notify them in the UI long after the transaction is complete (if they logout then log back in later in the day).

When you say scheduling service on the App server are you referring to something like a windows service that is solely responsible for handling this type of thing?