Job creation template

Not so long ago we have faced the following problem while implementing SQL jobs to test and production environment:

  • Job is developed be developer by deployed to test or production by DBA.
  • E-mail lists, folders are different on each environment.

So the process was the following: developer come and together with DBA was trying to recreate job on test/production and not miss a thing. Of course, as this was done manually the process usually caused errors in jobs’ configuration. As we’re changing and creating SQL jobs regularly it was quite a big problem.

So, it was decided to create SQL job template and to create job using script, designing in such a way to create correct result on any of our environment.

First, we have to create parameters for all our servers and put them into variables:

BEGIN TRANSACTION
— Set parameters
— Job owner
declare @joboperator sysname
— operator email
declare @email_operator_BU   sysname
declare @email_operator_IS   sysname
— path to java scripts
declare @localPath varchar(1000)
— job identity
declare @jobid binary(16)
— var for parameter @command
declare @path nvarchar(3201)
— additional parameters
declare @Job_Enable bit
declare @Delete_Job bit
declare @database_user_name sysname
declare @returncode int
select @returncode = 0
 
select
      — Setting job owner
      @joboperator =
      case @@servername
            when ‘PROD_1’ then ‘sqljoboperator’
            when ‘PROD_2’ then ‘sqla’
            when ‘PROD_3’ then ‘sqlaedd’
            when ‘TEST_4’ then ‘devsqljoboper’
            when ‘TEST_3’ then ‘devsqljoboper’
            when ‘TEST_1’ then ‘sqlaeddd’
            when ‘TEST_2’ then ‘sqlaeddd’
      end,
— Setting path to java ascripts
      @localPath =
      case @@servername
            when ‘PROD_3’ then ‘D:\’
            when ‘PROD_2’ then ‘not application’
            when ‘PROD_1’ then ‘not application’
            when ‘DEV_1’ then ‘D:\DEV1\’
            when ‘DEV_2’ then ‘D:\DEV2\’
            when ‘DEV_3’ then ‘D:\DEV3\’
            when ‘TEST_1’ then ‘D:\TEST1\’
            when ‘TEST_2’ then ‘D:\TEST2\’
            when ‘TEST_4’ then ‘not application’
            when ‘TEST_3’ then ‘not application’
      end,
— Setting operator’s email
      @email_operator_BU =
      case @@servername
            when ‘PROD_3’ then ‘Job BU’
            when ‘PROD_1’ then ‘Job BU’
            when ‘PROD_2’ then ‘Job IS’
            else ‘Job IS’
      end,
      @email_operator_IS = ‘Job IS’,
      — Enable or disable job depending on server name
      @Job_Enable =
      case @@servername
            when ‘PROD_1’ then 1
            when ‘PROD_3’ then 1
            when ‘PROD_2’ then 1
            when ‘DEV_1’ then 0
            when ‘DEV_2’ then 0
            when ‘DEV_3’ then 0
            when ‘TEST_4’ then 0
            when ‘TEST_3’ then 0
            when ‘TEST_1’ then 0
            when ‘TEST_2’ then 0
      end,
      — Delete job depending on server name
      @Delete_Job =
      case @@servername
            when ‘PROD_1’ then 1
            when ‘PROD_3’ then 1
            when ‘PROD_2’ then 1
            when ‘DEV_1’ then 0
            when ‘DEV_2’ then 0
            when ‘DEV_3’ then 0
            when ‘TEST_4’ then 1
            when ‘TEST_3’ then 1
            when ‘TEST_1’ then 1
            when ‘TEST_2’ then 1
      end,
      @database_user_name =
      case @@servername
            when ‘PROD_1’ then N”
            when ‘PROD_2’ then N”
            when ‘PROD_3’ then N”
            when ‘TEST_4’ then N”
            when ‘TEST_3’ then N”
            when ‘TEST_1’ then N”
            when ‘TEST_2’ then N”
      end
 
— If server name is not listed – rollback with error
if @localPath is null or @email_operator_IS is null or @email_operator_BU is null
BEGIN
            RAISERROR (N’Parameters are not set for the specified server’, 16, 1)
            GOTO QuitWithRollback
END

Then, we have to delete job if it’s already exists on the server. We do not do this for developer server – as developers can delete and create SQL jobs on dev environment. [ Job Name ] – must be replaced by real job name in the template.

IF @Delete_Job = 1
BEGIN
      — Delete the job with the same name (if it exists)
      SELECT @JobID = job_id FROM msdb.dbo.sysjobs with(nolock) WHERE (name = N'[ Job Name ]’)
      IF (@JobID IS NOT NULL)
      BEGIN
            — Check if the job is a multi-server job
            if (exists (select * from msdb.dbo.sysjobservers with(nolock) where (job_id = @jobid) and (server_id <> 0)))
            BEGIN
                  — There is, so abort the script
                  RAISERROR (N’Unable to import job ”[ Job Name ]” since there is already a multi-server job with this name.’, 16, 1)
                  GOTO QuitWithRollback
            END
            ELSE
                  — Delete the [local] job
                  execute msdb.dbo.sp_delete_job @job_name = N'[ Job Name ]’
                  select @jobid = null
      END
END
 

Now we are ready for job creation.

— Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = N'[ Job Name ]’,
@owner_login_name = @joboperator,
@description = N'[ Job Description ]’,
@category_name = N’Application jobs’,
@enabled = @Job_Enable,
@notify_level_email = 2,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 0,
@delete_level= 0,
@notify_email_operator_name = @email_operator_IS
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Now let’s add job steps. In our case in the template we have provided all possible variants that can be used by our team:

  • Execute of java script,
  • Execute of SQL command,
  • Execute SSIS package.
— Add the job steps
— Job step template for use @subsystem = N‘CmdExec’
— @on_[success/fail]_action 1 –  exit without error, 2 – exit with error, 3 – go to next step
set @path = N’cscript ‘ + @localPath + ‘WScripts\[ Script Name ].js
echo %errorlevel%’
 
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
            @step_id = 1,
            @step_name = N’Send WebMethod.[ Web Method Name ] ‘,
            @command = @path,
            @database_name = N”,
            @server = N”,
            @database_user_name = @database_user_name,
            @subsystem = N’CmdExec’,
            @cmdexec_success_code = 0,
            @flags = 0,
            @retry_attempts = 0,
            @retry_interval = 1,
            @output_file_name = N”,
            @on_success_step_id = 0,
            @on_success_action = 3,
            @on_fail_step_id = 0,
            @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

— Job step template for use @subsystem = N’TSQL’
— @on_[success/fail]_action 1 –  exit without error, 2 – exit with error, 3 – go to next step
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
            @step_id = 1,
            @step_name = N'[STEP NAME]’,
            @command = N’SQL Script’,
            @database_name = N’database name’,
            @server = N”,
            @database_user_name = @database_user_name,
            @subsystem = N’TSQL’,
            @cmdexec_success_code = 0,
            @flags = 0,
            @retry_attempts = 0,
            @retry_interval = 1,
            @output_file_name = N”,
            @on_success_step_id = 0,
            @on_success_action = 3,
            @on_fail_step_id = 0,
            @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

— Job step template for use @subsystem = N’SSIS’
— @on_[success/fail]_action 1 –  exit without error, 2 – exit with error, 3 – go to next step
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
            @step_id = 1,
            @step_name = N'[STEP NAME]’,
            @command = @path,
            @database_name = N”,
            @server = N”,
            @database_user_name = @database_user_name,
            @subsystem = N’SSIS’,
            @cmdexec_success_code = 0,
            @flags = 0,
            @retry_attempts = 0,
            @retry_interval = 1,
            @output_file_name = N”,
            @on_success_step_id = 0,
            @on_success_action = 1,
            @on_fail_step_id = 0,
            @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

And the final step – add job schedule:

— Add the job schedules
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId,
            @name=N'[ Schedule Name]’,
            @enabled=1,
            @freq_type=8,
            @freq_interval=2,
            @freq_subday_type=1,
            @freq_subday_interval=1,
            @freq_relative_interval=0,
            @freq_recurrence_factor=1,
            @active_start_date=20110627,
            @active_end_date=99991231,
            @active_start_time=113000,
            @active_end_time=235959
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 

Final step – adding target servers and commit or rollback transaction:

 — Add the Target Servers
 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
 @server_name = N'(local)’
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Now I can say, that implement or correct jobs on different servers it’s really not a problem in our day-to-date activities. All jobs’ scripts are stored in Team Foundation Server

Also if you’re restaging test environment for UAT and want to restore there all jobs that are on production, you just send a list of scripts to DBA and he just execute them and that’s all. No manual work, no errors, no human factor. From my point of view it’s very elegant decision.

Advertisements
This entry was posted in Database development, SQL Server, Technology and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s