Pages

Saturday, 22 September 2012

SSDT SQL Agent Job Deployment Template

SQL Server Data Tools (SSDT) is a great product for database development.  It even includes the ability to code and publish some "server level" objects:
  • Logins (so we can associate database users)
  • Extended Events
  • Linked Servers
  • Cryptography keys
  • Service Broker queues
  • Server level triggers
  • Server roles and memberships
But one object currently omitted is the ability to define and deploy SQL Agent Jobs.  The only practical solution is to manage these through post deployment scripts.

As I've been working on a project that will have a fairly large number of SQL Agent jobs I have come up with a pattern for maintaining these that might be of use to other people.  Its a pretty simple approach, it does involve a few manual steps but as you'll see these aren't too bad.

For my projects I like to structure my deployment scripts into a "Scripts" folder.  Under that I put a number of sub-folders for various tasks, e.g:
  • Data population/seeding
  • Miscellaneous (usually this just has a script to drop the __Refactor table)
  • Jobs
The "root" Scripts folder just holds a Pre and Post deployment script that will reference other scripts in the various directories.

E.g:
/*
Post-Deployment Script Template       
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.  
 Use SQLCMD syntax to include a file in the post-deployment script.   
 Example:      :r .\myfile.sql        
 Use SQLCMD syntax to reference a variable in the post-deployment script.  
 Example:      :setvar TableName MyTable       
               SELECT * FROM [$(TableName)]     
--------------------------------------------------------------------------------------
*/

/* Data Population */
:r ".\Data\Seed Product Categories.sql"

/* Job Categores */
:r ".\Jobs\Job Categories.sql"

/* Jobs */
:r ".\Jobs\Example Job.sql"

/* Refactor table tidy up */
:r ".\Misc\Drop the __Refactor table.sql"


This allows me to add and remove scripts quickly and easily.  I can even just comment one out if I want.  Its also a good way to quickly find deployment code (as long as you name your files sensibly).

In the SSDT project, that'd look something like this:

The job template script

As you'll see from the partial screenshot above there is also a file named "_Template.sql" in the Jobs folder that was not referenced in the PostDeployment.sql file.  This template file contains a stub I use for creating the job deployment scripts.


The _Template.sql  file contains stubs for various steps we want to go through:
  1. Define a SQLCMD variable for the job name we want to use.  This parameter is used throughout the script as we reference the name a few times.
  2. Find out if there's an existing job with the same name.  If there is we need to determine if we're going to do nothing and leave it alone or if we're going to redeploy the job.
  3. If there is no existing job with the same name we will deploy the "new" job.
  4. If there is an existing job we will check another SQLCMD variable RedeployJobs.  If that variable is set to "Yes" then we'll drop the existing job and recreate it.
  5. Depending on results of the above we'll either deploy the job or simply do nothing.

It looks like this:
/*
Job Deployment Script Template                            
--------------------------------------------------------------------------------------

Instructions
------------
    1.  Copy the contents of this script (you do not need to copy these comments/instructions)
        to a script that will contain the job definition.

    2.  Update the JobName parameter with the desired name.

    3.  Script the job from the server using SSMS.

    4.  Copy the job category to the Job Categories.sql post deployment script.

    5.  Copy the relavent sections from the generated script to the various sections below.

    6.  Ensure the database name is correct.  I recommend using the built in $(DatabaseName)
        parameter.

*/

/* Copy from below this line to the end of the script */
--:setvar JobName "Enter the job name within these quotes and uncomment this line"

PRINT 'Deploying job: $(JobName)';
DECLARE @JobId  UNIQUEIDENTIFIER;

SELECT  @JobId = job_id
FROM    msdb.dbo.sysjobs
WHERE   name = '$(JobName)';

/*
 * First we check existence of the specified job name and whether the RedeployJobs parameter
 * has been set to "YES".
 */
IF UPPER('$(RedeployJobs)') = 'YES'   /* Redeploy the job */
OR @JobId IS NULL                   /* It does not exist so deploy it anyway */
BEGIN;
    /*
     * Either this is a new job or the RedeployJobs parameter is set to YES.  If its an existing job
     * we need to remove it so that we can "redeploy" it.
     */
    IF @JobId IS NOT NULL
    BEGIN;
        PRINT ' Deleting existing job';
        EXEC msdb.dbo.sp_delete_job @job_id = @JobId;

        /*
         * Set the @JobId variable to NULL for the sp_add_job command later on.  If it is not null the
         * server things the job is from a MSX server
         */
        SET @JobId = NULL;
    END;
     
    /*
     * Add the job
     */

    /*
     * Add the job step(s)
     */

    /*
     * Add the job schedule
     */

    /*
     * Add the job server
     */
    EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';

    PRINT ' Created the job "$(JobName)"';
END;
ELSE
BEGIN;
    PRINT ' Bypassing job "$(JobName)" deployment as job exists and RedeployJob parameter is "$(RedeployJobs)"';
END;

Hopefully the template is pretty clear as far as determining whether to deploy the job or not.


Deploying a new job

I'm going to assume you've created a job in your development environment that you now want to include in your SSDT project.   Here's how we go about that:
  1. First we need to create a new "PostDeployment" script in our Jobs folder
  2. Right click on the Jobs folder --> Add --> Script...
  3. Select "Post-Deployment Script" and call it the same name as your job (simply so you can find it easily).  For the purposes of this blog post, it will be referred to as Example Job.sql

  4. Click Add
  5. Open up the _Template.sql file and copy its contents into the new Example Job.sql script.
  6. In the Example Job.sql script, uncomment the ":setvar JobName ...." line and enter the name of the job between the quotes:
    :setvar JobName "Example Job"
  7. Now open up SQL Server Management Studio and script out your job to a new query window or file.  From here we'll be copying various sections into our new Example Job.sql file to fill in the missing sections.
  8. First copy the section to add the job:

    Into the line just below the comment:
    /*
     * Add the job
     */
  9. Modify the step so that the @job_name parameter uses the SQLCMD variable:
  10. For each job step follow the same approach by copying the sp_add_jobstep commands to the line just below the comment:
    /*
     * Add the job step(s)
     */ 
  11. Note: For any step that references the database this project applies to, it is highly recommended that you update the command to use the SQLCMD variable
    $(DatabaseName)
    . E.g.:

  12. I also add the "sp_update_job" command in this section.  If you prefer you could create your own section for that.
  13. Finally add the job schedule. I like to name the schedule the same as the job and therefore use the $(JobName)
    SQLCMD variable here too:
     
  14. You should now have something that resembles this:
    :setvar JobName "Example Job"
    
    PRINT 'Deploying job: $(JobName)';
    DECLARE @JobId  UNIQUEIDENTIFIER;
    
    SELECT  @JobId = job_id
    FROM    msdb.dbo.sysjobs
    WHERE   name = '$(JobName)';
    
    /*
     * First we check existence of the specified job name and whether the RedeployJobs parameter
     * has been set to "YES".
     */
    IF UPPER('$(RedeployJobs)') = 'YES'   /* Redeploy the job */
    OR @JobId IS NULL                   /* It does not exist so deploy it anyway */
    BEGIN;
        /*
         * Either this is a new job or the RedeployJobs parameter is set to YES.  If its an existing job
         * we need to remove it so that we can "redeploy" it.
         */
        IF @JobId IS NOT NULL
        BEGIN;
            PRINT ' Deleting existing job';
            EXEC msdb.dbo.sp_delete_job @job_id = @JobId;
    
            /*
             * Set the @JobId variable to NULL for the sp_add_job command later on.  If it is not null the
             * server things the job is from a MSX server
             */
            SET @JobId = NULL;
        END;
         
        /*
         * Add the job
         */
        EXEC msdb.dbo.sp_add_job @job_name=N'$(JobName)', 
      @enabled=1, 
      @notify_level_eventlog=0, 
      @notify_level_email=0, 
      @notify_level_netsend=0, 
      @notify_level_page=0, 
      @delete_level=0, 
      @description=N'This is a SSDT example job deployment', 
      @category_name=N'Database Maintenance', 
      @owner_login_name=N'sa', @job_id = @jobId OUTPUT;
    
        /*
         * Add the job step(s)
         */
        EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'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'SELECT * FROM sys.databases', 
      @database_name=N'$(DatabaseName)', 
      @flags=12;
    
        EXEC msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1;
    
        /*
         * Add the job schedule
         */
        EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'$(JobName)', 
      @enabled=1, 
      @freq_type=4, 
      @freq_interval=1, 
      @freq_subday_type=4, 
      @freq_subday_interval=10, 
      @freq_relative_interval=0, 
      @freq_recurrence_factor=0, 
      @active_start_date=20120922, 
      @active_end_date=99991231, 
      @active_start_time=0, 
      @active_end_time=235959, 
      @schedule_uid=N'64aed10d-3b89-47ae-8f51-ca6d196b1444';
    
        /*
         * Add the job server
         */
        EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';
    
        PRINT ' Created the job "$(JobName)"';
    END;
    ELSE
    BEGIN;
        PRINT ' Bypassing job "$(JobName)" deployment as job exists and RedeployJob parameter is "$(RedeployJobs)"';
    END;
    
  15. Save your file
  16. Update the "master" post deployment script to reference the newly created job script

Before you deploy...

If you were to try and deploy this now, it would fail.  We haven't yet added the $(RedeployJobs) variable to the project.  This is very simple to do:
  1. Open up the properties for the project
  2. Select the "SQLCMD Variables" tab
  3. Add the RedeployJobs variable with Yes as the value for Default and Local:

Deploying

Now when you deploy you can decide whether to override any existing jobs with the same names or leave them as they are. As it uses a SQLCMD variable you can specify this as part of your continuous integration tools. Even if you specify "No" for RedeployJobs a new job will always be deployed.

Whilst there are quite a few steps to follow, once you've done this once or twice you'll realise that its a very easy approach.  The main dangers are forgetting to update the generated job names and database references with the appropriate SQLCMD variables.

4 comments:

  1. Thank you for this, I like the pattern as it is simple and neat, I will be attempting to integrate this into my own projects

    ReplyDelete
    Replies
    1. Hi Joey,

      Glad you find it useful.

      Regards
      Kent

      Delete
  2. Thanks for sharing your knowledge. Very nice and neat blog! I like the way to use the cmd variables in scripts.
    Linda.

    ReplyDelete
  3. This has certainly solved a crucial problem in our day to day DB development! Your template is now part of our must-do process, so thankyou.

    Only one alteration I'd suggest and thought I'd put here for others - although this checks for the presence of the job, it always assumes the Schedule doesn't exist. Found this results in multiple Job Schedules being created on the server rather than deleting and recreating. Below is what we've done to replace ln 66 to 81:

    /* Check if Schedule exists, otherwise create. */
    IF EXISTS (SELECT 1 FROM msdb.dbo.sysschedules WHERE name = N'My Schedule')
    BEGIN
    PRINT N'Job Schedule already exists.';
    END
    ELSE
    BEGIN
    EXEC msdb.dbo.sp_add_schedule @schedule_name=N'My Schedule',
    @enabled=1,
    @freq_type=4,
    @freq_interval=1,
    @freq_subday_type=2,
    @freq_subday_interval=60,
    @freq_relative_interval=0,
    @freq_recurrence_factor=0,
    @active_start_date=20120319,
    @active_end_date=99991231,
    @active_start_time=0,
    @active_end_time=235959

    PRINT N'Job Schedule created successfully.';
    END

    /* Add the job to the schedule */
    EXEC msdb.dbo.sp_attach_schedule @job_id=@jobId, @schedule_name=N'My Schedule'

    Hope it helps!

    ReplyDelete