在源代码管理中包含 SQL Server 代理作业的最佳方式是什么？

我真的很高兴你问了这个问题。我对 Visual Studio 数据库项目中的 SQL Server 作业和作业部署以及数据库有同样的需求。

我希望解决方案文件包含我已实现的 SQL 数据库流程所需的所有内容，全部集中在一个地方，以创建此解决方案并将此解决方案部署到任何给定的服务器（只需单击几下）。此解决方案中的组件包括：

• 数据库架构
• SSIS 包
• SQL Server 配置（master db“ Credential”创建；msdb“ Proxy”创建；SQL Agent“ Job”创建）。

我想把它放在一起有两个原因：

• 简化部署（来源：本地开发实例/目标：服务器开发实例）
• 解决方案文档

所以我开始着手解决这个问题。这是我创建的内容以及我如何获取此设置。它非常适合预期目的。

概述：

1. 使用 TSQL 代码创建脚本文件。我使用了 CHECK FOR EXISTENCE 方法来避免错误，因为我可能会创建环境中已经存在的东西。

参考：如何检查 SQL Server 实例中是否存在凭据？

例子：

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

2. 创建一个执行脚本文件的脚本文件。更多详情如下。

3. 发布数据库项目，BOOM你刚刚做了一些很棒的事情。

SSDT 设置：

1. Pluralsight：Robert Cain 的“VS 中的 DB 项目开发”| 模块：数据和架构比较、部署前和部署后脚本

首先，我查看了这个有用的 Pluralsight 视频。我能够跟随，而我所做的是......

一）New Folder。在项目中创建一个名为的新文件夹 [ScriptsPostDeployment]

乙）New Script。为名为 [Create_SqlAgentJobs.sql].

2. 错误消息：“Visual Studio 数据库项目中只能有一个部署后脚本”

如果您尝试使用 [Build Action="Post Deploy"] 创建多个脚本文件，您将收到此错误消息。

总的来说，我的项目不仅需要这个脚本，还需要其他一些脚本—— Create_SqlAgentJobs.sql、、、Credential_GEORGES-bl0040ep.sql。ProxyAccount_GEORGES-bl0040ep.sql在 StackOverFlow 上的这个线程中讨论并解决了该错误：SQL Server project execution multiple script post deploy。

新文件夹总共有 4 个脚本 - 1 个用于运行脚本；3 个需要执行的脚本。

ssdt-dbproj_DB-ScriptsPostDeployment.png

SSDT 发布：

我运行了发布文件并选择了“生成脚本”选项。我查看了脚本并且... POST DEPLOYMENT 代码被自动插入以添加 SQL 代理作业！成功！下面是在 SSMS 中对 SQL 实例运行查询后的脚本和脚本执行消息...

/*
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

我认为最好的选择是将创建作业语句包含在部署后脚本中。就像这个线程中的解决方案：

如何在 VS SQL 项目中部署 SQL Server 代理作业？

4 年前有人问过这个问题，但仍会添加答案以防万一。

博客文章很好地解释了如何有效地做到这一点。

https://www.alexvolok.com/2020/azure-devops-for-sql-server-dba-continuous-delivery-of-sql-agent-jobs/