How can we improve SQL Source Control?

Agent jobs

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

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

    24 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Paul E. KahlPaul E. Kahl commented  ·   ·  Flag as inappropriate

        @jonesjw444 - I couldn't agree more. Scheduled Jobs can be so complex, that having to rebuild them from scratch on various machines is a complete pain. It would be great to just be able to push the changes that have been checked in, and not worry about it. And since they're part of SQL, they ought to be part of SQL Source Control.

      • Rich HoltRich Holt commented  ·   ·  Flag as inappropriate

        One solution using RedGate Sql Source Control with TFS 2013:

        Use a "DBA" database, link it to RedGate source control.
        Write a stored procedure to manage each SQL Agent Job and check it in
        - Start with the SSMS "Script Job As Create To..." and modify the output
        - See example below

        In the example:
        - we create a "SqlAgent job proc" [prcAgentJob_Foo] that does the script work to create or drop+create the Job, called "Foo"
        - the Job, in a JobStep, runs a proc [prcTheProcToExecute] that also is under source control
        - Job Schedules, Alerts, Notifications, Operators can all be included!

        The final "stupid pet trick" is to include a post-build script in TFS that runs a "master script" whose name never changes (so your Build Definition doesn't have to change).
        In this TFS post-build script, you reference any "child" scripts that need to be executed.
        One of these "child scripts" contains a command to execute the stored procedure that manages the Agent Job ([prcAgentJob_Foo])

        Please review and modify the code as necessary for your own testing. The job, as scripted below, won't actually run.

        --------------------------
        USE [Dba]
        GO
        SET ANSI_NULLS, QUOTED_IDENTIFIER ON
        GO

        IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prcAgentJob_Foo]') AND type in (N'P', N'PC'))
        EXEC (N'CREATE PROCEDURE [dbo].[prcAgentJob_Foo] AS SELECT 1 AS [foo];');
        GO

        ALTER PROCEDURE [dbo].[prcAgentJob_Foo]
        AS BEGIN
        SET NOCOUNT OFF;

        BEGIN TRANSACTION;
        DECLARE @JobName NVARCHAR(128) = N'Foo';
        DECLARE @JobOwner NVARCHAR(20) = (select name from sys.server_principals where principal_id = 1);
        DECLARE @jobId BINARY(16);
        DECLARE @ReturnCode INT = 0;

        IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA' AND category_class=1)
        BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
        END

        IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
        EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule=1;

        SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = @JobName)
        IF (@jobId is NULL)
        BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = @JobName,
        @enabled=1,
        @notify_level_eventlog=2,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Foo- demonstrate SQL Agent Job SCC',
        @category_name=N'DBA',
        @owner_login_name = @JobOwner,
        @job_id = @jobId OUTPUT
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
        EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Foo Step 1',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'EXEC DBA.prcTheProcToExecute;',
        -- @database_name=N'Foo', -- SET the correct DatabaseName here!
        @flags=0
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 15 minutes at :00',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=4,
        @freq_subday_interval=15,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20131107,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 15 minutes at :07',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=4,
        @freq_subday_interval=15,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20131028,
        @active_end_date=99991231,
        @active_start_time=700,
        @active_end_time=235959
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
        EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
        COMMIT TRANSACTION

        ---- May want to disable the job initially...
        -- EXEC msdb.dbo.sp_update_job @job_name = @JobName, @enabled=0

        GOTO EndSave
        QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
        EndSave:

        END
        END

      • Rich HoltRich Holt commented  ·   ·  Flag as inappropriate

        SCC and automated build & deployment of SQL Agent Jobs is a must-have in an Enterprise environment. Currently, we have many environments in our path to Production, and are using TFS 2013 for automated build and release, integrated with RedGate SQL Source Control. But any SQL Agent Jobs "solution" is a kludge!

        Please, REDGATE, tell us if we should abandon your product in search of something better, or if you are working on an update to provide this capability.

        Please ?

      • Rich HoltRich Holt commented  ·   ·  Flag as inappropriate

        Gaping hole in the redGate SCC tool! *CAN* this be accomplished, and it is yet on the RedGate features list?

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

      ← Previous 1

      Feedback and Knowledge Base