What is the best way to include SQL Server Agent jobs in Source Control?
I'm really glad you asked this question. I had this very same need for SQL Server jobs in a Visual Studio database project and job deployments along with a database.
I wanted the solution file to contain everything that is needed for the SQL database process I had implemented, all in one place, to Create this solution AND Deploy this solution to any given server (in just a few clicks). Components in this solution are:
- Database schema
- SSIS package
- SQL Server configurations (master db “
Credential” creation; msdb “Proxy” creation; SQL Agent “Job” creation).I wanted to keep it all together for two reasons:
- Simplified deployment (SOURCE: Local Dev Instance / TARGET: Server Dev Instance)
- Solution documentation
So I set to work on figuring this out. Here is what I created and how I sourced this setup. It works great for the intended purposes.
Overview:
- Create the script files with the TSQL code. I used the CHECK FOR EXISTENCE method to avoid errors where I could possibly be creating something that already exists in the environment.
Reference: How to check if a Credential exists in a SQL Server instance?
Example:
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'MyJobNameInSqlAgent')
BEGIN
print 'Yep, found it!!!'
--Really you need to add the script to create it...
END
Create a script file that executes the script files. More details below.
Publish the database project, and BOOM you've just done something awesome.
SSDT Setup:
- Pluralsight: "DB Project development in VS" by Robert Cain | Module: Data and Schema Comparisons, Pre and Post Deployment Scripts
First I reviewed this helpful Pluralsight Video. I was able to follow along, and what I did was...
A)
New Folder. Create a new folder in the project called[ScriptsPostDeployment]B)
New Script. Create a new script to the folder called[Create_SqlAgentJobs.sql].
- ERROR MSG: "You can only have one post-deployment script in visual studio database project"
You will get this error message if you try to create multiple script files with [Build Action="Post Deploy"].
In total my project needed not only this one script, but it need some other scripts as well--
Create_SqlAgentJobs.sql,Credential_GEORGES-bl0040ep.sql,ProxyAccount_GEORGES-bl0040ep.sql. The error was discussed and solved in this thread on StackOverFlow: SQL Server project executing multiple script post deploy.In total the new folder has 4 scripts-- 1 to run the scripts; 3 scripts that need to be executed.
ssdt-dbproj_DB-ScriptsPostDeployment.png
SSDT Publish:
I ran the Publish file and chose the option to "Generate Script". I viewed the script and... POST DEPLOYMENT code was automatically inserted to add the SQL AGENT JOB!! Success! Below is the script and script execution messages after running the query on the SQL instance in SSMS...
/*
Deployment script for DBA
This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
:setvar DatabaseName "DBA"
:setvar DefaultFilePrefix "DBA"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"
GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
SET NOEXEC ON;
END
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
DECLARE @rc int, -- return code
@fn nvarchar(4000), -- file name for back up
@dir nvarchar(4000) -- backup directory
EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @dir output, 'no_output'
if (@rc = 0) SELECT @dir = @dir + N'\'
IF (@dir IS NULL)
BEGIN
EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @dir output, 'no_output'
if (@rc = 0) SELECT @dir = @dir + N'\'
END
IF (@dir IS NULL)
BEGIN
EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @dir output, 'no_output'
if (@rc = 0) SELECT @dir = @dir + N'\Backup\'
END
IF (@dir IS NULL)
BEGIN
SELECT @dir = N'$(DefaultDataPath)'
END
SELECT @fn = @dir + N'$(DatabaseName)' + N'-' +
CONVERT(nchar(8), GETDATE(), 112) + N'-' +
RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(hh, GETDATE()))), 2) +
RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(mi, getdate()))), 2) +
RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(ss, getdate()))), 2) +
N'.bak'
BACKUP DATABASE [$(DatabaseName)] TO DISK = @fn
END
GO
USE [$(DatabaseName)];
GO
PRINT N'SKIPPING THIS STEP (Manual)!!! Dropping [olap].[UsageStatsLog_GCOP039_bak20190218]...';
GO
--DROP TABLE [olap].[UsageStatsLog_GCOP039_bak20190218];
GO
/*
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)]
--------------------------------------------------------------------------------------
*/
/*
REFERENCE
--https://dba.stackexchange.com/questions/202000/what-is-the-best-way-to-include-sql-server-agent-jobs-in-source-control
--
*/
--DESCRIPTION:
--SQL Agent Job created.
--Credential and Proxy Account required setups are preceded in this script
USE master
GO
/*
REFERENCE
--https://dataqueen.unlimitedviz.com/2015/05/creating-a-proxy-user-to-run-an-ssis-package-in-sql-server-agent/
--https://stackoverflow.com/questions/10946718/how-to-check-if-a-credential-exists-in-a-sql-server-instance
*/
IF EXISTS (select * from sys.credentials where name = 'Credential_BL0040EP')
BEGIN
PRINT 'The credential [Credential_BL0040EP] already exists...'
END
IF NOT EXISTS (select * from sys.credentials where name = 'Credential_BL0040EP')
BEGIN
-- Create a proxy credential for xp_cmdshell.
EXEC sp_xp_cmdshell_proxy_account 'GEORGES\bl0040ep', 'EnterThePasswordHere'; --SELECT * FROM [master].[sys].[credentials]
-- Grant execute permission on xp_cmdshell to the SQL Server login account.
GRANT exec ON sys.xp_cmdshell TO [GEORGES\bl0040ep]
-- Create a credential containing the GEORGES account PowerGEORGES\PowerUser and its password
CREATE CREDENTIAL Credential_BL0040EP WITH IDENTITY = N'GEORGES\bl0040ep', SECRET = N'EnterThePasswordHere'
PRINT 'The credential [Credential_BL0040EP] was created!'
END
USE [msdb]
/*
REFERENCE
--https://dataqueen.unlimitedviz.com/2015/05/creating-a-proxy-user-to-run-an-ssis-package-in-sql-server-agent/
--http://sqldbatask.blogspot.com/2013/10/credentials-and-proxy-creation-script.html?_sm_au_=iVVM80kNTL4rs4Dq
*/
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = 'Proxy_BL0040EP')
BEGIN
PRINT 'The proxy [Proxy_BL0040EP] already exists...'
END
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = 'Proxy_BL0040EP')
BEGIN
-- Create a new proxy called SSISProxy and assign the PowerUser credentail to it
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'Proxy_BL0040EP',@credential_name=N'Credential_BL0040EP',@enabled=1
-- Grant SSISProxy access to the "SSIS package execution" subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'Proxy_BL0040EP', @subsystem_id=11
-- Grant the login testUser the permissions to use SSISProxy
EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'GEORGES\bl0040ep', @proxy_name=N'Proxy_BL0040EP'
PRINT 'The proxy [Proxy_BL0040EP] was added!'
END
USE [msdb]
GO
/*
REFERENCE
--https://stackoverflow.com/questions/136771/sql-server-agent-job-exists-then-drop
*/
USE [msdb]
GO
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DatabaseSSASUsageStats')
BEGIN
PRINT 'The job [DatabaseSSASUsageStats] already exists...'
END
IF NOT EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DatabaseSSASUsageStats')
BEGIN
/****** Object: Job [DatabaseSSASUsageStats] Script Date: 2/21/2019 2:04:38 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 2/21/2019 2:04:38 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DatabaseSSASUsageStats',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'GEORGES\bl0040ep', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [SSASUsageStats] Script Date: 2/21/2019 2:04:38 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SSASUsageStats',
@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'SSIS',
@command=N'/ISSERVER "\"\SSISDB\IsolatedPackages\SSASUsageStats\SSASUsageStats.dtsx\"" /SERVER GCO07766 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";3 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
@database_name=N'master',
@flags=0,
@proxy_name=N'Proxy_BL0040EP'
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'DatabaseSSASUsageStats (Hourly_0800-1700)',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20190125,
@active_end_date=20190208,
@active_start_time=80000,
@active_end_time=170000,
@schedule_uid=N'6cd0bef8-9091-4e30-a0c5-5262685aeff0'
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
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
END
GO
GO
PRINT N'Update complete.';
GO
I think the best option is to include the create job statement in a post-deployment script. Like the solution in this thread:
How to deploy a SQL Server Agent Job in a VS SQL project?