在 Azure SQL 托管实例中使用 SQL 代理作业自动执行管理任务Automate management tasks using SQL Agent jobs in Azure SQL Managed Instance

适用于: Azure SQL 托管实例

使用 SQL Server 中的 SQL Server 代理SQL 托管实例,可以创建和计划可针对一个或多个数据库定期执行的作业,以运行 Transact-SQL (T-SQL) 查询和执行维护任务。Using SQL Server Agent in SQL Server and SQL Managed Instance, you can create and schedule jobs that could be periodically executed against one or many databases to run Transact-SQL (T-SQL) queries and perform maintenance tasks. 本文介绍了 SQL 托管实例的 SQL 代理。This article introduced SQL Agent for SQL Managed Instance.

备注

SQL 代理不适用于 Azure SQL 数据库或 Azure Synapse Analytics。SQL Agent is not available in Azure SQL Database or Azure Synapse Analytics. 相反,建议使用弹性作业实现作业自动化Instead, we recommend Job automation with Elastic Jobs.

Azure SQL 托管实例中的 SQL 代理作业限制SQL Agent job limitations in Azure SQL managed instance

值得注意的是,SQL Server 中提供的 SQL 代理与 SQL 托管实例中的 SQL 代理之间存在差异。It is worth noting the differences between SQL Agent available in SQL Server and as part of SQL Managed Instance. 若要详细了解 SQL Server 和 SQL 托管实例支持的功能之间的差异,请参阅 Azure SQL 托管实例与 SQL Server 的 T-SQL 差异For more on the supported feature differences between SQL Server and SQL Managed Instance, see Azure SQL Managed Instance T-SQL differences from SQL Server.

在 SQL Server 中可用的某些 SQL 代理功能在 SQL 托管实例中不受支持:Some of the SQL Agent features that are available in SQL Server are not supported in SQL Managed Instance:

  • SQL 代理设置为只读。SQL Agent settings are read only.
    • SQL 托管实例中不支持系统存储过程 sp_set_agent_propertiesThe system stored procedure sp_set_agent_properties is not supported in SQL Managed Instance.
  • 目前,SQL 托管实例不支持启用/禁用 SQL 代理。Enabling/disabling SQL Agent is currently not supported in SQL Managed Instance. SQL 代理始终运行。SQL Agent is always running.
  • 部分支持通知:Notifications are partially supported:
    • 不支持寻呼机。Pager is not supported.
    • 不支持 NetSend。NetSend is not supported.
    • 不支持警报。Alerts are not supported.
  • 不支持代理。Proxies are not supported.
  • 不支持事件日志。Eventlog is not supported.
  • 不支持基于空闲 CPU 的作业计划触发器。Job schedule trigger based on an idle CPU is not supported.

何时使用 SQL 代理作业When to use SQL Agent jobs

SQL 代理作业有多种使用场景:There are several scenarios when you could use SQL Agent jobs:

  • 自动完成管理任务,并将作业计划为在每个工作日、数小时之后或按其他频率运行。Automate management tasks and schedule them to run every weekday, after hours, etc.
    • 部署架构更改、凭据管理、性能数据收集或租户(客户)遥测数据收集。Deploy schema changes, credentials management, performance data collection or tenant (customer) telemetry collection.
    • 更新引用数据(所有数据库的公用信息)、从 Azure Blob 存储加载数据。Update reference data (information common across all databases), load data from Azure Blob storage.
    • 常见维护任务,包括用于确保数据完整性的 DBCC CHECKDB,或用于提高查询性能的索引维护。Common maintenance tasks including DBCC CHECKDB to ensure data integrity or index maintenance to improve query performance. 配置作业,以便定期(例如,在非高峰时段)对一系列数据库执行作业。Configure jobs to execute across a collection of databases on a recurring basis, such as during off-peak hours.
    • 持续将一组数据库中的查询结果收集到中央表中。Collect query results from a set of databases into a central table on an on-going basis. 性能查询可以持续执行,并可配置为触发执行其他任务。Performance queries can be continually executed and configured to trigger additional tasks to be executed.
  • 收集要报告的数据Collect data for reporting
    • 将数据库集合中的数据聚合到单个目标表中。Aggregate data from a collection of databases into a single destination table.
    • 对大量的数据库执行长时间运行的数据处理查询,例如,收集客户遥测数据。Execute longer running data processing queries across a large set of databases, for example the collection of customer telemetry. 结果将收集到单个目标表以供进一步分析。Results are collected into a single destination table for further analysis.
  • 数据移动Data movements
    • 创建作业,用于将数据库中所做的更新复制到其他数据库,或者收集远程数据库中所做的更新,并在数据库中应用更改。Create jobs that replicate changes made in your databases to other databases or collect updates made in remote databases and apply changes in the database.
    • 创建作业用于通过 SQL Server Integration Services (SSIS) 从/向数据库加载数据。Create jobs that load data from or to your databases using SQL Server Integration Services (SSIS).

Azure SQL 托管实例中的 SQL 代理作业SQL Agent jobs in Azure SQL managed instance

SQL 代理作业由 SQL 代理服务执行,该服务继续用于 SQL Server 和 SQL 托管实例中的任务自动化。SQL Agent Jobs are executed by the SQL Agent service that continues to be used for task automation in SQL Server and SQL Managed Instance.

SQL 代理作业是针对数据库指定的 T-SQL 脚本系列。SQL Agent Jobs are a specified series of T-SQL scripts against your database. 使用作业能够定义可一次或多次运行的,并且可以监视其成功或失败状态的管理任务。Use jobs to define an administrative task that can be run one or more times and monitored for success or failure.

一个作业可在一台本地服务器或者多台远程服务器上运行。A job can run on one local server or on multiple remote servers. SQL 代理作业是内部的数据库引擎组件,在 SQL 托管实例服务中执行。SQL Agent Jobs are an internal Database Engine component that is executed within the SQL Managed Instance service.

SQL 代理作业有几个关键概念:There are several key concepts in SQL Agent Jobs:

  • 作业步骤 集是指应在作业中执行的一个或多个步骤。Job steps set of one or many steps that should be executed within the job. 对于每个作业步骤,可以定义重试策略,以及该作业步骤成功或失败时应执行的操作。For every job step you can define retry strategy and the action that should happen if the job step succeeds or fails.
  • 计划 定义何时应执行该作业。Schedules define when the job should be executed.
  • 使用 通知 可以定义作业完成后,用于通过电子邮件通知操作员的规则。Notifications enable you to define rules that will be used to notify operators via email once the job completes.

SQL 代理作业步骤SQL Agent job steps

SQL 代理作业步骤是 SQL 代理应执行的操作序列。SQL Agent Job steps are sequences of actions that SQL Agent should execute. 每个步骤包含该步骤成功或失败时应执行的后续步骤,以及失败时的重试次数。Every step has the following step that should be executed if the step succeeds or fails, number of retries in a case of failure.

SQL 代理允许创建不同类型的作业步骤,例如,用于针对数据库执行单个 Transact-SQL 批处理的 Transact-SQL 作业步骤、可执行自定义 OS 脚本的 OS 命令/PowerShell 步骤、用于通过 SSIS 运行时加载数据的 SSIS 作业步骤,或者可将数据库中的更改发布到其他数据库的复制步骤。SQL Agent enables you to create different types of job steps, such as Transact-SQL job steps that execute a single Transact-SQL batch against the database, or OS command/PowerShell steps that can execute custom OS script, SSIS job steps that enable you to load data using SSIS runtime, or replication steps that can publish changes from your database to other databases.

备注

若要详细了解如何结合使用 Azure SSIS Integration Runtime 与 Azure SQL 托管实例托管的 SSISDB,请参阅在 Azure 数据工厂中结合使用 Azure SQL 托管实例和 SQL Server Integration Services (SSIS)For more information on leveraging the Azure SSIS Integration Runtime with SSISDB hosted by Azure SQL Managed Instance, see Use Azure SQL Managed Instance with SQL Server Integration Services (SSIS) in Azure Data Factory.

事务复制可将表中的更改复制到 Azure SQL 托管实例、Azure SQL 数据库或 SQL Server 中的其他数据库。Transactional replication can replicate the changes from your tables into other databases in Azure SQL Managed Instance, Azure SQL Database, or SQL Server. 若要了解信息,请参阅在 Azure SQL 托管实例中配置复制For information, see Configure replication in Azure SQL Managed Instance.

SQL 托管实例中目前不支持其他类型的工作步骤,包括:Other types of job steps are not currently supported in SQL Managed Instance, including:

  • 不支持合并复制作业步骤。Merge replication job step is not supported.
  • 不支持队列读取器。Queue Reader is not supported.
  • 不支持 Analysis ServicesAnalysis Services are not supported

SQL 代理作业计划SQL Agent job schedules

计划指定运行作业的时间。A schedule specifies when a job runs. 多个作业可按同一计划运行,可将多个计划应用到同一作业。More than one job can run on the same schedule, and more than one schedule can apply to the same job.

计划可为作业运行时间定义以下条件:A schedule can define the following conditions for the time when a job runs:

  • 每当 SQL Server 代理启动时。Whenever SQL Server Agent starts. 每次故障转移后会激活作业。Job is activated after every failover.
  • 在特定的日期和时间运行一次,这对于延迟执行某些作业非常有用。One time, at a specific date and time, which is useful for delayed execution of some job.
  • 按重复的计划运行。On a recurring schedule.

备注

SQL 托管实例目前不允许在 CPU 空闲时启动作业。SQL Managed Instance currently does not enable you to start a job when the CPU is idle.

SQL 代理作业通知SQL Agent job notifications

当作业成功完成或失败时,SQL 代理作业可让你接收通知。SQL Agent Jobs enable you to get notifications when the job finishes successfully or fails. 可以通过电子邮件接收通知。You can receive notifications via email.

如果未启用,首先需要在 Azure SQL 托管实例上配置数据库邮件功能If it isn't already enabled, first you would need to configure the Database Mail feature on Azure SQL Managed Instance:

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

例如,设置将用于发送电子邮件通知的电子邮件帐户。As an example exercise, set up the email account that will be used to send the email notifications. 将帐户分配给名为 AzureManagedInstance_dbmail_profile 的电子邮件配置文件。Assign the account to the email profile called AzureManagedInstance_dbmail_profile. 若要在 SQL 托管实例中使用 SQL 代理作业发送电子邮件,应创建一个配置文件且名称必须为 AzureManagedInstance_dbmail_profileTo send e-mail using SQL Agent jobs in SQL Managed Instance, there should be a profile that must be called AzureManagedInstance_dbmail_profile. 否则,SQL 托管实例将无法通过 SQL 代理发送电子邮件。Otherwise, SQL Managed Instance will be unable to send emails via SQL Agent. 请查看以下示例:See the following sample:

-- 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_db_mail 系统存储过程通过 T-SQL 测试数据库邮件配置:Test the Database Mail configuration via T-SQL using the sp_send_db_mail system stored procedure:

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 代理作业发生了问题。You can notify the operator that something happened with your SQL Agent jobs. 操作员为一个或多个 SQL 托管实例中的实例的维护负责人定义联系信息。An operator defines contact information for an individual responsible for the maintenance of one or more instances in SQL Managed Instance. 有时,操作员职责会分配给一个人。Sometimes, operator responsibilities are assigned to one individual.

在包含多个 SQL 托管实例中的实例或 SQL Server 的系统中,可由多个人分担操作员的职责。In systems with multiple instances in SQL Managed Instance or SQL Server, many individuals can share operator responsibilities. 操作员不涉及安全信息,因此不会定义安全主体。An operator does not contain security information, and does not define a security principal. 理想情况下,操作员不应是其责任可能发生变化的个人,而应是电子邮件通讯组。Ideally, an operator is not an individual whose responsibilities may change, but an email distribution group.

可以使用 SQL Server Management Studio (SSMS) 或以下示例中所示的 Transact-SQL 脚本创建操作员You can create operators using SQL Server Management Studio (SSMS) or the Transact-SQL script shown in the following example:

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

通过 SSMS 中的数据库邮件日志确认电子邮件是成功还是失败。Confirm the email's success or failure via the Database Mail Log in SSMS.

然后可以使用 SSMS 或以下 Transact-SQL 脚本修改任何 SQL 代理作业并分配操作员,以便在作业完成、失败或成功时向其发送电子邮件通知:You can then modify any SQL Agent job and assign operators that will be notified via email if the job completes, fails, or succeeds using SSMS or the following Transact-SQL script:

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 Agent job history

Azure SQL 托管实例当前不允许更改任何 SQL 代理属性,因为这些属性存储在基础注册表值中。Azure SQL Managed Instance currently doesn't allow you to change any SQL Agent properties because they are stored in the underlying registry values. 这意味着,用于调整作业历史记录的代理保留策略的选项固定为默认的 1000 条总记录,每个作业至多 100 条历史记录。This means options for adjusting the Agent retention policy for job history records are fixed at the default of 1000 total records and max 100 history records per job.

SQL 代理固定数据库角色成员身份SQL Agent fixed database role membership

如果将链接到非 sysadmin 登录名的用户添加到 msdb 系统数据库中三个 SQL 代理固定数据库角色中的任何一个角色,则会出现以下问题:需要向主存储过程授予显式 EXECUTE 权限才能使这些登录名有效。If users linked to non-sysadmin logins are added to any of the three SQL Agent fixed database roles in the msdb system database, there exists an issue in which explicit EXECUTE permissions need to be granted to the master stored procedures for these logins to work. 如果遇到此问题,将显示错误消息“在对象 <object_name> 中拒绝了 EXECUTE 权限(Microsoft SQL Server,错误:229)”。If this issue is encountered, the error message "The EXECUTE permission was denied on the object <object_name> (Microsoft SQL Server, Error: 229)" will be shown.

将用户添加到 msdb 中的 SQL 代理固定数据库角色(SQLAgentUserRole、SQLAgentReaderRole 或 SQLAgentOperatorRole)后,对于添加到这些角色的每个用户登录名,请执行以下 T-SQL 脚本,向列出的系统存储过程显式授予 EXECUTE 权限。Once you add users to a SQL Agent fixed database role (SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole) in msdb, for each of the user's logins added to these roles, execute the below T-SQL script to explicitly grant EXECUTE permissions to the system stored procedures listed. 此示例假定用户名与登录名相同。This example assumes that the user name and login name are the same.

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];

了解详细信息Learn more