Did you ever have the need of "queuing" SQL Jobs execution?

mcannistra
Mario Cannistrà

I recently met this problem and received disaccording answers from different information sources.

I have many users using an old client-server application to launch long-running procedures. They run for a minimum of 15 minutes to 60 minutes or more.

The procedures’ inner workings have already been examined from senior developers and from the DBA: no more optimizations are feasible.

Due to the high demand of processing and to the resource contention on the main database, I think that these activities should be queued.

The approach I’m suggesting is:

group all data processing routines in one or more stored procedures
modify the client application to “request” processing instead of directly executing it and receiving a job number
manage a queue of SQL jobs launched from a Windows Service
modify the client application to get the status of the user’s jobs
organize jobs in “classes” with different characteristics (maximum run time for the job, start and stop hour of day in which execution is allowed,…)

What is your opinion about this solution?

I think this approach could solve this specific problem and represent the framework to be used for projects with similar needs.

I look forward to read your comments.

Sabot
My name is Dave Oliver. I'm a Technical Architect.

With SQL Server 2005 & Windows 2003 you will be able to use a feature called the service broker.

It's basically the queuing system you want ...

http://www.microsoft.com/technet/technetmag/issues/2005/05/ServiceBroker/default.aspx

Also with ADO.Net 2.0 Async calls to SQL Server 2005 have got a whole lot easier, so another option for you there.

... it's time to upgrade!

TommyCarlier
I want my scalps!

At the company I work, we have such a mechanism, but for .NET. Whenever a long-during task has to be executed, we insert a record in a request-table in our database. One or more services take these requests, and execute them (using reflection to load the assemblies and run the requested method).

Sabot
My name is Dave Oliver. I'm a Technical Architect.

TommyCarlier wrote:
At the company I work, we have such a mechanism, but for .NET. Whenever a long-during task has to be executed, we insert a record in a request-table in our database. One or more services take these requests, and execute them (using reflection to load the assemblies and run the requested method).

Oooooooow, why do I think this is brilliant ... but tell me why I think this is a bit scary? Oh yes, my security alarm bell is going off.

TommyCarlier
I want my scalps!

Why should there be a security problem? Our security is pretty tight. We've actually had to rewrite a part of it, because our security is so tight. This is a server environment, and the only way you can access it from the outside is via a webservice with 1 method. Nobody from the outside has direct access to the database. So the services that execute the tasks can only run from inside the secured environment.

mcannistra
Mario Cannistrà

Thank you for your kind answer.

Last night i printed the article from Technet magazine. I will soon have some more time to read it with more attention. I've just read the introduction and the approach seems very close to my idea.

I'm not sure that every requirement is matched by that solution but i will post a complete feedback asap.

I've installed SQL Server 2005 and tried many new features. The Service Broker was still... queued.

Unfortunately, i've seen Technet magazine advertised on MSDN Magazine but it is not available outside US. Now that i know its available online, i'll take a look at it at least one time each week.

I've almost finished a solution based on SQL Jobs and that article could have brought me to a different approach. I'll let you know about this when i'll reach the bottom of the article.

About Async calls : i'll search info about this, but i suppose that the application must be left open to wait for the call completion.

This does not match the requirements because the users want to have more freedom (must be possible to close the app and take away the laptop for external activities).

Did you ever have the need of &quot;queuing&quot; SQL Jobs execution?