Settings and activity

  1. 477 votes
    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    You have left! (?) (thinking…)
    31 comments  ·  SQL Source Control  ·  Flag idea as inappropriate…  ·  Admin →

    How important is this to you?

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    under review  ·  Kendra responded

    Thank you everyone for your comments and votes on this over the years. While I don’t have a 100% full resolution for this suggestion, I can sum up our current recommendations here. Continued feedback is very welcome.

    Our current recommendation is to use the post-deployment script feature of SQL Source Control (released in V6.3) to manage SQL Server Agent jobs.

    An example script for this is here: https://documentation.red-gate.com/soc/common-tasks/working-with-pre-post-deployment-scripts/create-sql-server-agent-job

    As some commenters in this thread have alluded to, it is possible (and sometimes very common) for SQL Agent jobs to have steps that touch multiple databases on a single SQL Server Instance. For this reason, some customers prefer to create a separate database for instance-level management and objects (sometimes named DBA or similar) and choose to manage things like linked servers and SQL Agent jobs with the post-script associated with that database.

    This separate-database architecture also makes sense if the jobs…

    An error occurred while saving the comment
    Rich Holt commented  · 

    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

    An error occurred while saving the comment
    Rich Holt commented  · 

    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 ?

    An error occurred while saving the comment
    Rich Holt commented  · 

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

    Rich Holt supported this idea  ·