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.