Settings and activity
1 result found
-
480 votes
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 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
·
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