Coffeehouse Thread

6 posts

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

Back to Forum: Coffeehouse
  • User profile image
    mcannistra

    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.

     

  • User profile image
    Sabot

    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!

  • User profile image
    TommyCarlier

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

  • User profile image
    Sabot

    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.

  • User profile image
    TommyCarlier

    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.

  • User profile image
    mcannistra

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

    Mario

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.