在 Azure SQL 托管实例中使用 SQL 代理作业自动执行管理任务

适用于：Azure SQL 托管实例

在 AzureSQL 托管实例中使用 SQL Server 代理，可以创建和计划可定期针对一个或多个数据库执行的作业。 这些 SQL 代理作业运行 Transact-SQL（T-SQL）查询并执行维护任务。 本文介绍如何使用 SQL 代理的 SQL 托管实例。

何时使用 SQL 代理作业

SQL 代理作业有多种使用场景：

• 自动完成管理任务，并将作业计划为在每个工作日、数小时之后或按其他频率运行。
  • 部署架构更改、凭据管理、性能数据收集或租户（客户）遥测收集。
  • 更新引用数据（所有数据库共有的信息），并从 Azure Blob 存储加载数据。 有关用于向 Azure Blob 存储进行身份验证的参数，请参阅 BULK_INSERT 。
  • 常见维护任务（包括 DBCC CHECKDB）可确保数据完整性或索引维护以提高查询性能。 配置作业，以便定期（例如，在非高峰时段）对一系列数据库执行作业。
  • 持续将一组数据库的查询结果收集到中央表中。 可以持续执行性能查询，并配置为触发要执行的更多任务。
• 收集要报告的数据
  • 将数据库集合中的数据聚合到单个目标表中。
  • 对大量的数据库执行长时间运行的数据处理查询，例如，收集客户遥测数据。 结果将收集到单个目标表以供进一步分析。
• 数据移动
  • 创建作业，用于将数据库中所做的更新复制到其他数据库，或者收集远程数据库中所做的更新，并在数据库中应用更改。
  • 创建作业用于通过 SQL Server Integration Services (SSIS) 从/向数据库加载数据。

SQL 托管实例中的 SQL 代理作业

SQL Server 代理执行用于 SQL 托管实例中任务自动化的 SQL 代理作业。

SQL 代理作业是针对数据库指定的一系列 T-SQL 脚本。 使用作业能够定义可一次或多次运行的，并且可以监视其成功或失败状态的管理任务。

作业可以在一个本地实例或多个远程实例上运行。 SQL 代理作业是在 SQL 托管实例服务中执行的内部数据库引擎组件。

SQL 代理作业中有几个关键概念：

• 作业步骤 是应在作业中执行的一个或多个步骤集。 对于每个作业步骤，可以定义重试策略，以及作业步骤成功或失败时应采取的行动。
• 计划定义何时应执行该作业。
• 通知 使你能够定义规则，这些规则用于在作业完成后通过电子邮件通知操作员。

作业步骤

SQL 代理的任务步骤是 SQL 代理需要执行的操作序列。 每个步骤都有一个以下步骤，如果步骤成功或失败，则应执行以下步骤;如果步骤失败，应执行一组重试次数。

使用 SQL 代理可以创建不同类型的作业步骤。

• 针对数据库执行单个 Transact-SQL 批处理的作业步骤。
• 可以执行自定义 OS 脚本的 OS 命令/PowerShell 步骤。
• SSIS 作业步骤，可用于通过 SSIS 运行时加载数据。
• 复制步骤可以将您的数据库的更改发布到其他数据库。

事务复制可将表中的更改复制到 SQL 托管实例、Azure SQL 数据库或 SQL Server 中的其他数据库。 若要了解信息，请参阅在 Azure SQL 托管实例中配置复制。

SQL 托管实例中当前不支持其他类型的作业步骤，例如合并复制和队列读取器。

作业计划

计划指定运行作业的时间。 多个作业可按同一计划运行，可将多个计划应用到同一作业。

计划可为作业运行时间定义以下条件：

• 每当 SQL Server 代理启动时启动。 每次故障转移后都会激活作业。
• 在特定日期和时间启动一次，这对于延迟执行作业非常有用。
• 从定期计划开始。

有关计划 SQL 代理作业的详细信息，请参阅计划作业。

作业通知

当作业成功完成或失败时，SQL 代理作业可让你接收通知。 可以通过电子邮件接收通知。

如果尚未启用此功能，首先需要在 SQL 托管实例上配置数据库邮件功能：

GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE

作为示例练习，设置用于发送电子邮件通知的电子邮件帐户。 将帐户分配给名为 AzureManagedInstance_dbmail_profile 的电子邮件配置文件。 若要在 SQL 托管实例中使用 SQL 代理作业发送电子邮件，应创建一个配置文件且名称必须为 AzureManagedInstance_dbmail_profile。 否则，SQL 托管实例无法通过 SQL 代理发送电子邮件。

使用以下示例脚本创建数据库邮件帐户和配置文件，然后将它们关联在一起：

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQL Agent Account',
    @description = 'Mail account for Azure SQL Managed Instance SQL Agent system.',
    @email_address = '$(loginEmail)',
    @display_name = 'SQL Agent Account',
    @mailserver_name = '$(mailserver)' ,
    @username = '$(loginEmail)' ,
    @password = '$(password)';

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @description = 'E-mail profile used for messages sent by Managed Instance SQL Agent.';

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @account_name = 'SQL Agent Account',
    @sequence_number = 1;

使用 sp_send_dbmail 系统存储过程通过 T-SQL 测试数据库邮件配置：

DECLARE @body VARCHAR(4000) = 'The email is sent from ' + @@SERVERNAME;
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @recipients = 'ADD YOUR EMAIL HERE',
    @body = 'Add some text',
    @subject = 'Azure SQL Instance - test email';

可以通知操作员 SQL 代理作业发生了问题。 操作员为一个或多个 SQL 托管实例中的实例的维护负责人定义联系信息。 有时，操作员职责会分配给一个人。

在具有多个 SQL 托管实例或 SQL Server 实例的环境中，许多个人可以分担操作员的责任。 操作员不涉及安全信息，因此不会定义安全主体。 理想情况下，操作员不是职责可以更改的个人，而是电子邮件通讯组。

可以使用 SQL Server Management Studio (SSMS) 或以下示例中所示的 Transact-SQL 脚本创建操作员：

EXEC msdb.dbo.sp_add_operator
    @name=N'AzureSQLTeam',
    @enabled=1,
    @email_address=N'AzureSQLTeamn@contoso.com';

通过 SSMS 中的数据库邮件日志确认电子邮件是成功还是失败。

如果作业完成、失败或成功，可以 修改任何 SQL 代理作业 并分配通过电子邮件通知的操作员。 使用 SSMS 或以下 T-SQL 脚本修改作业：

EXEC msdb.dbo.sp_update_job @job_name=N'Load data using SSIS',
    @notify_level_email=3, -- Options are: 1 on succeed, 2 on failure, 3 on complete
    @notify_email_operator_name=N'AzureSQLTeam';

作业历史记录

SQL 托管实例当前不允许更改任何 SQL 代理属性，因为它们存储在基础注册表值中。 这意味着用于调整作业历史记录代理保留策略的选项已固定为默认设置，总记录数为1,000条，且每个作业最多保留100条历史记录。

有关详细信息，请参阅查看 SQL 代理作业历史记录。

固定数据库角色成员身份

如果将链接到 nonsysadmin 登录名的用户添加到系统数据库中三个 SQL 代理固定数据库角色msdb中的任何一个，则存在需要向数据库中的三个系统存储过程EXECUTE授予显式master权限的问题。 如果遇到此问题，将显示错误消息 The EXECUTE permission was denied on the object <object_name> (Microsoft SQL Server, Error: 229)。

将用户添加到 SQL 代理固定数据库角色（SQLAgentUserRole、SQLAgentReaderRole 或 SQLAgentOperatorRole）msdb 后，对于为这些角色添加的每个用户的登录名，请执行以下 T-SQL 脚本，向列出的系统存储过程显式授予 EXECUTE 权限。 此示例假定用户名与登录名相同：

USE [master]
GO
CREATE USER [login_name] FOR LOGIN [login_name];
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [login_name];
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [login_name];
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [login_name];

SQL 托管实例中的 SQL 代理作业限制

值得注意的是，SQL Server 中提供的 SQL 代理与作为 SQL 托管实例的一部分可用的 SQL 代理之间的差异。 若要详细了解 SQL Server 和 SQL 托管实例支持的功能之间的差异，请参阅 Azure SQL 托管实例与 SQL Server 的 T-SQL 差异。

SQL Server 中提供的某些 SQL 代理功能在 SQL 托管实例中不受支持：

• SQL 代理设置为只读。
  • 系统存储过程 sp_set_agent_properties 不受支持。
• 目前，不支持启用/禁用 SQL 代理。 SQL 代理始终运行。
• 虽然通知支持部分功能，但不支持以下各项：
  • 不支持寻呼机。
  • 不支持 NetSend。
  • 不支持警报。
• 不支持代理。
• 不支持 Eventlog。
• 不支持基于空闲 CPU 的作业计划触发器。
• 不支持合并复制作业步骤。
• 不支持队列读取器。
• 不支持 Analysis Services。
• 不支持在磁盘上运行作为文件存储的脚本。
• 不支持导入外部模块（如 dbatools 和 dbachecks）。
• 不支持 PowerShell Core。

相关内容

• 什么是 Azure SQL 托管实例？
• Azure SQL 托管实例的新增功能
• Azure SQL 托管实例与 SQL Server 的 T-SQL 差异
• 功能比较：Azure SQL 数据库和 Azure SQL 托管实例
• 配置数据库邮件
• 在 Azure 中排查出站 SMTP 连接问题