Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Sql-Server Jobs/ASP.NET Challenge

    Hi All, Anyone up for a challenge. Here goes:

    I want to create a front end in ASP.NET to sql server job in the back end.
    Requirements are:
    1, The Frontend interface must allow a user to interact with sql jobs i.e
    User must be able to view job name, job history, messages, schedules etc from this frontend

    2, The User must be able to create a new job from the frontend i.e
    She must be able to add job name, add Steps and schedule the job etc

    Any pointer or advice will be greatly appreciated thanks.

    PS; I'm new to SQL-Server...

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ...why?

    The interface to create jobs and steps already exist within Management Studio.

    Have you considered the security implications? Remember that SQL Jobs run in the context of the SQL Agent account which undoubtedly has higher permissions than you want your users to have. What if a user created a job that dropped all of your tables?
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2012
    Posts
    3

    Re: Sql-Server Jobs/ASP.NET Challenge

    Quote Originally Posted by gvee View Post
    ...why?

    The interface to create jobs and steps already exist within Management Studio.

    Have you considered the security implications? Remember that SQL Jobs run in the context of the SQL Agent account which undoubtedly has higher permissions than you want your users to have. What if a user created a job that dropped all of your tables?
    I already have considered your last point, we have an application to which we wish to integrate this solution with. All the security issues will be ironed out from the interface leaving only stripped down commands getting into the database. Yet you raise a good point to which i will pay close attention, undoubtedly.
    Yet i still want a procedure on how to go about it. and no i do not want to use SSMS to create jobs and schedule.
    Thanks in advance.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Investigate the use of System Management Objects (SMO). This assembly has all the methods and events you inquired about.

    Imports Microsoft.SqlServer.Management.Smo

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In Management Studio try setting up a dummy job but before you save it look for the "Script..." option at the top of the dialog box; this will give you some insight in to how SSMS does it
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by dev.olawale View Post
    i do not want to use SSMS to create jobs and schedule.
    Why? Any solution we give you is simply reinventing portions of SSMS. If you don't tell us why SSMS doesn't work for you we're probably going to suggest something you can't use anyway.

    There are web-based SSMS alternatives out there already too. Why are you building one from scratch? What's the business case?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2012
    Posts
    3
    @gvee, thanks once again. I'm currently looking in this direction (job scripts..) for the solution.

    @Teddy, it's not that SSMS don't work, it's that we don't want our users to have access to the database. we currently have a solution in the market called HTSFinancials, an end-to-end web-based accounting package, and we'd like to integrate job creation into the package for the generation of reports and other stuffs.
    We've never implemented such before and the responsibility of doing research and coming with a good solution rest on me( a newbie ).

    I've research the SMO assembly, but i find that too complex as of now maybe later.
    I'd like to learn of web-based SSMS alternatives to create jobs though. I don't want to reinvent the wheel.
    @all Thanks for the help so far...(appreciated...)
    Last edited by dev.olawale; 03-19-12 at 07:57.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yikes. User-created, scheduled database jobs in an environment where users can't be trusted with db access? Sounds burly...

    What kind of jobs are these? What do they do, functionally? Maybe straight up scheduled jobs aren't the way to go... I would sooner create my own light weight service to handle checking for and executing scheduled tasks than allow questionable users to create database objects.

    Doesn't even need to be THAT complex. You could just maintain your own "scheduled job" table and have a job scheduled with the sql agent that would check your table at short intervals, executing anything that is currently scheduled.

    Given only the requirements you have revealed so far, I would avoid automatic job creation like the plague. Nothing but bad will come of that...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You know, while I'm thinking about it... You might be better off taking it out of the database entirely, aside from scheduling information.

    Assuming a SaaS platform where you have full control over the architecture, I would MUCH prefer to have my own scheduling facilities packaged as a stand alone service as opposed to relying on some mix of SQL agent, custom jobs and potentially application logic.

    From an architecture perspective that would give me MUCH more flexibility and potentially durability than involving SQL Server as something more than storage. Particularly if you're in the .NET world.

    What happens if/when you have to scale? What happens if the SQL Agent dies? What happens if the user creates a bad job? How are you handling exceptions/errors? How will notification take place? What if you have to accommodate tasks that SQL Server cannot accomplish due to platform limitations?

    Highly recommend answering those types of questions before you start building. I am concerned you're choosing architecture based on familiarity as opposed to best match for current and future requirements with lowest maintenance. Consider some more options. Save your weekends.




    PS: The baked-in SMTP facilities in SSIS are atrocious. Don't even bother...
    Last edited by Teddy; 03-19-12 at 20:53.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •