Suggest a new feature or enhancement; Ask a question...

← SQL Source Control

Agent jobs

Would be great if SQL Server Agent jobs could be incorporated into SVN

125 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Forgot password?
    Create a password
    I agree to the terms of service
    Signed in as (Sign out)
    Close
    Close
    You have left! (?) (thinking…)
    jonesjw444jonesjw444 shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    20 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Forgot password?
      Create a password
      I agree to the terms of service
      Signed in as (Sign out)
      Close
      Close
      Submitting...
      • MarbryMarbry commented  ·   ·  Flag as inappropriate

        This would bevery useful, jobs (searchable would be great) in TFS.

        It's currently a real pain keep track of all that once you accumulate any number of production jobs.

      • Phil HelmerPhil Helmer commented  ·   ·  Flag as inappropriate

        We do the same as what Pete Cousins described. It works pretty well for us in terms of saving the job, but it doesn't help us with deployment. It would seem that controlling the jobs more tightly to the databases in SVN would make it more likely that the tools (compare or deployment manager) could deploy those more easily than a series of manual script executions.

      • Pete CousinsPete Cousins commented  ·   ·  Flag as inappropriate

        This can already be done. Script the SQL Agent Job from SSMS (amend script so it drops existing by name, and not job_id), save the file, and add it into to SVN. I have a seperate folder under Trunk called SQLAgentJobs.
        We also use VisualSVN (http://www.visualsvn.com/) to version control SSIS packages & SSRS reports using SVN too.

      • AleksandarAleksandar commented  ·   ·  Flag as inappropriate

        Lakeland's idea makes sense. much easier to keep track of changes that way.

        If you wanted to keep jobs like they are though, theres a few options. I wrote the idea of putting MSDB into source control before i tried it, only to find that i cant actually do it.

        Im quite new to sql so am constantly learning about useful things... like triggers! As such ive created a trigger that automatically updates a table when a change is made to a job :)

        Hopefully this turns out okay on this form:

        CREATE TRIGGER AFTER_JOBS_CHANGE
        ON msdb.dbo.sysjobsteps
        AFTER INSERT, UPDATE, DELETE
        AS

        MERGE DBMan.dbo.JOBS
        USING
        (
        Select
        msdb.dbo.sysjobs.name AS [job_name],
        msdb.dbo.sysjobsteps.step_id AS [step_no],
        msdb.dbo.sysjobsteps.step_name AS [step_name],
        msdb.dbo.sysjobsteps.command AS [step_details],
        msdb.dbo.sysjobsteps.step_uid AS [step_uid]
        From
        msdb.dbo.sysjobs
        JOIN msdb.dbo.sysjobsteps
        ON msdb.dbo.sysjobs.job_id=msdb.dbo.sysjobsteps.job_id
        ) AS TEMP

        ON
        ( JOBS.STEP_UID = TEMP.step_uid
        )

        WHEN MATCHED AND -- If it exists AND has changes
        (
        JOBS.JOB_NAME <> TEMP.job_name OR
        JOBS.STEP_NO <> TEMP.step_no OR
        JOBS.STEP_NAME <> TEMP.step_name OR
        JOBS.STEP_DETAILS <> TEMP.step_details
        )
        THEN
        UPDATE Set
        JOBS.JOB_NAME = TEMP.job_name,
        JOBS.STEP_NO = TEMP.step_no,
        JOBS.STEP_NAME = TEMP.step_name,
        JOBS.STEP_DETAILS = TEMP.step_details,
        JOBS.STEP_UID = TEMP.step_uid

        WHEN NOT MATCHED AND TEMP.step_uid IS NOT NULL THEN -- New files
        INSERT (JOB_NAME, STEP_NO, STEP_NAME, STEP_DETAILS, STEP_UID)
        VALUES (TEMP.job_name, TEMP.step_no,TEMP.step_name,TEMP.step_details,TEMP.step_uid)

        WHEN NOT MATCHED BY SOURCE THEN
        DELETE

        ;

        After youve done this, Just link the 'JOBS' table as static data and it works well :)

      • lakelandlakeland commented  ·   ·  Flag as inappropriate

        I currently work around this by making my jobs short and trivial - all calling a SP which is under source control. e.g. sp_daily_load. That way the only bits I miss out on are things like scheduling and notifications but I get the upside of tightly tying the version of the job to the version of the database.

      • Leslie PLeslie P commented  ·   ·  Flag as inappropriate

        I like your idea Aleksandar. Looks like SQL Source Control can't be used to put system tables under static data control. Boo.

      • AleksandarAleksandar commented  ·   ·  Flag as inappropriate

        I know this might be a bit late for some, but a workaround for this would be to to put the msdb database in source control and filter out everything other than the table below:

        msdb.dbo.sysjobsteps

        It stores all the steps for all jobs, any updates to jobs will update this table. only issue is that you'd have to get people to remember to commit the change when they change a job.

        Might even be useful for the redgate team to use this table and integrate jobs into version control... ;)

      • TiagoTiago commented  ·   ·  Flag as inappropriate

        I think in this tool is a must to have this control.

      • Daniele BarloccoDaniele Barlocco commented  ·   ·  Flag as inappropriate

        It would be greater if you could control in TFS not only the jobs, but also the DTSs and the maintenance plans.

      • Leslie PLeslie P commented  ·   ·  Flag as inappropriate

        I would like SQL Server jobs controlled under TFS. Ideally integrated into SSMS.

      SQL Source Control

      Feedback and Knowledge Base

      (thinking…)