使用数据库作业自动完成管理任务Automate management tasks using database jobs

适用于:是 Azure SQL 数据库 是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

Azure SQL 数据库允许创建和计划可针对一个或多个数据库定期执行的作业,以运行 T-SQL 查询和执行维护任务。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.

可以定义目标数据库或者要在其中执行作业的数据库组,同时定义作业的运行计划。You can define target database or groups of databases where the job will be executed, and also define schedules for running a job. 作业可以处理登录到目标数据库的任务。A job handles the task of logging in to the target database. 此外,可以定义、维护以及保存要跨一组数据库执行的 Transact-SQL 脚本。You also define, maintain, and persist Transact-SQL scripts to be executed across a group of databases.

每个作业会记录执行状态,如果发生任何失败,则还会自动重试操作。Every job logs the status of execution and also automatically retries the operations if any failure occurs.

何时使用自动化作业When to use automated jobs

作业自动化有多种使用方案:There are several scenarios when you could use job automation:

  • 自动完成管理任务,并将作业计划为在每个工作日、数小时之后或按其他频率运行。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.
    • 重建索引以提升查询性能。Rebuild indexes 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).

概述Overview

可以使用以下作业计划技术:The following job scheduling technologies are available:

  • SQL 代理作业是经典的且经过实战检验的 SQL Server 作业计划组件,可在 Azure SQL 托管实例中使用。SQL Agent Jobs are classic and battle-tested SQL Server job scheduling component that is available in Azure SQL Managed Instance. SQL 代理作业在 Azure SQL 数据库中不可用。SQL Agent Jobs are not available in Azure SQL Database.
  • 弹性数据库作业(预览) 是针对一个或多个 Azure SQL 数据库中的数据库执行自定义作业的作业计划服务。Elastic Database Jobs (preview) are Job Scheduling services that execute custom jobs on one or many databases in Azure SQL Database.

值得注意的是,SQL 代理(可以在本地使用以及作为 SQL 托管实例的一部分使用)与数据库弹性作业代理(适用于 Azure SQL 数据库中的单一数据库和 SQL 数据仓库中的数据库)之间存在一些差异。It is worth noting a couple of differences between SQL Agent (available on-premises and as part of SQL Managed Instance), and the Database Elastic Job agent (available for single databases in Azure SQL Database and databases in SQL Data Warehouse).

弹性作业Elastic Jobs SQL 代理SQL Agent
范围Scope 作业代理所在 Azure 云中任意数目的 Azure SQL 数据库中的数据库和/或数据仓库。Any number of databases in Azure SQL Database and/or data warehouses in the same Azure cloud as the job agent. 目标可以位于不同的服务器、订阅和/或区域中。Targets can be in different servers, subscriptions, and/or regions.

目标组可以包含单个数据库或数据仓库,也可以包含某个服务器、池或分片映射中的所有数据库(在作业运行时动态枚举)。Target groups can be composed of individual databases or data warehouses, or all databases in a server, pool, or shardmap (dynamically enumerated at job runtime).
SQL 代理所在实例中的任何单个数据库。Any individual database in the same instance as the SQL agent.
支持的 API 和工具Supported APIs and Tools 门户、PowerShell、T-SQL、Azure 资源管理器Portal, PowerShell, T-SQL, Azure Resource Manager T-SQL、SQL Server Management Studio (SSMS)T-SQL, SQL Server Management Studio (SSMS)

SQL 代理作业SQL Agent Jobs

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 Agent Jobs are an internal Database Engine component that is executed within the 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.

作业步骤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.

事务复制是一项数据库引擎功能,可用于发布对一个数据库中的一个或多个表所做的更改,然后将这些更改发布/分发到一组订阅服务器数据库。Transactional replication is a Database Engine feature that enables you to publish the changes made on one or multiple tables in one database and publish/distribute them to a set of subscriber databases. 更改发布是使用以下 SQL 代理作业步骤类型实现的:Publishing of the changes is implemented using the following SQL Agent job step types:

  • 事务日志读取器。Transaction-log reader.
  • 快照。Snapshot.
  • 分发服务器。Distributor.

目前不支持其他类型的工作步骤,包括:Other types of job steps are not currently supported, including:

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

作业计划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 Instance is restarted (or when 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 托管实例目前不允许在实例“空闲”时启动作业。SQL Managed Instance currently does not enable you to start a job when the instance is "idle".

作业通知Job notifications

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

首先,需要设置用于发送电子邮件通知的电子邮件帐户,然后将该帐户分配到名为 AzureManagedInstance_dbmail_profile 的电子邮件配置文件,如以下示例中所示:First, you would need to set up the email account that will be used to send the email notifications and assign the account to the email profile called AzureManagedInstance_dbmail_profile, as shown in 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;

此外,需要在托管实例上启用数据库邮件:You would also need to enable Database Mail on Managed Instance:

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

可以通知操作员 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.

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

EXEC msdb.dbo.sp_add_operator
    @name=N'Mihajlo Pupun',
    @enabled=1,
    @email_address=N'mihajlo.pupin@contoso.com'

可以使用 SSMS 或以下 Transact-SQL 脚本修改任何作业并分配操作员,以便在作业完成、失败或成功时向其发送电子邮件通知:You can modify any 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'Mihajlo Pupun'

SQL 代理作业限制SQL Agent Job Limitations

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

  • SQL 代理设置为只读。SQL Agent settings are read only. 托管实例不支持过程 sp_set_agent_propertiesProcedure sp_set_agent_properties is not supported in Managed Instance.
  • 目前,托管实例不支持启用/禁用 SQL 代理。Enabling/disabling SQL Agent is currently not supported in 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.

有关 SQL Server 代理的信息,请参阅 SQL Server 代理For information about SQL Server Agent, see SQL Server Agent.

弹性数据库作业(预览版)Elastic Database Jobs (preview)

使用弹性数据库作业,可以按计划或按需跨大量数据库并行运行一个或多个 T-SQL 脚本。Elastic Database Jobs provide the ability to run one or more T-SQL scripts in parallel, across a large number of databases, on a schedule or on-demand.

针对数据库的任意组合运行作业:可以是一个或多个单独的数据库,可以是一个服务器上的所有数据库,可以是一个弹性池中的所有数据库,也可以是分片映射,而且还可以灵活地包括或排除任何特定的数据库。Run jobs against any combination of databases: one or more individual databases, all databases on a server, all databases in an elastic pool, or shardmap, with the added flexibility to include or exclude any specific database. 作业可以跨多个服务器、多个池来运行,甚至可以针对不同订阅中的数据库来运行。Jobs can run across multiple servers, multiple pools, and can even run against databases in different subscriptions. 服务器和池会在运行时进行动态枚举,因此作业会针对执行时目标组中存在的所有数据库来运行。Servers and pools are dynamically enumerated at runtime, so jobs run against all databases that exist in the target group at the time of execution.

下图显示了一个跨不同类型的目标组执行作业的作业代理:The following image shows a job agent executing jobs across the different types of target groups:

弹性作业代理概念模型

弹性作业组件Elastic Job components

组件Component 说明(表下提供其他详细信息)Description (additional details are below the table)
弹性作业代理Elastic Job agent 为了运行和管理作业而创建的 Azure 资源。The Azure resource you create to run and manage Jobs.
作业数据库Job database 作业代理用来存储作业相关数据、作业定义等内容的 Azure SQL 数据库中的数据库。A database in Azure SQL Database that the job agent uses to store job related data, job definitions, etc.
目标组Target group 一组服务器、池、数据库和分片映射,可对其运行作业。The set of servers, pools, databases, and shard maps to run a job against.
作业Job 作业是由一个或多个作业步骤组成的工作单元。A job is a unit of work that is composed of one or more job steps. 作业步骤指定要运行的 T-SQL 脚本,以及执行脚本所需的其他详细信息。Job steps specify the T-SQL script to run, as well as other details required to execute the script.

弹性作业代理Elastic Job agent

弹性作业代理是用于创建、运行和管理作业的 Azure 资源。An Elastic Job agent is the Azure resource for creating, running, and managing jobs. 弹性作业代理是在门户(也支持 PowerShell 和 REST)中创建的 Azure 资源。The Elastic Job agent is an Azure resource you create in the portal (PowerShell and REST are also supported).

创建“弹性作业代理”需要现有的 Azure SQL 数据库中的数据库。Creating an Elastic Job agent requires an existing database in Azure SQL Database. 代理将此现有的数据库配置为作业数据库The agent configures this existing database as the Job database.

弹性作业代理免费。The Elastic Job agent is free. 作业数据库的费率与任何 Azure SQL 数据库中的数据库一样。The job database is billed at the same rate as any database in Azure SQL Database.

作业数据库Job database

作业数据库用于定义作业以及跟踪作业执行操作的状态和历史记录。The Job database is used for defining jobs and tracking the status and history of job executions. 作业数据库也用于存储代理元数据、日志、结果、作业定义,并且还包含许多有用的存储过程,以及其他数据库对象,可以通过 T-SQL 创建、运行和管理作业。The Job database is also used to store agent metadata, logs, results, job definitions, and also contains many useful stored procedures and other database objects for creating, running, and managing jobs using T-SQL.

就目前的预览版来说,需要使用现有的 Azure SQL 数据库中的数据库(S0 或更高级别)来创建弹性作业代理。For the current preview, an existing database in Azure SQL Database (S0 or higher) is required to create an Elastic Job agent.

作业数据库不一定需要是新的,但应该干净且为空,其服务目标应该为 S0 或更高。The Job database doesn't literally need to be new, but should be a clean, empty, S0 or higher service objective. 作业数据库的服务对象建议使用 S1 或更高,但最佳选择取决于作业的性能需求:作业步骤数,作业目标数,以及作业的运行频率。The recommended service objective of the Job database is S1 or higher, but the optimal choice depends on the performance needs of your job(s): the number of job steps, the number of job targets, and how frequently jobs are run. 例如,对于每小时只运行数个作业且以十个以下数据库为目标的作业代理,也许 S0 数据库就够用了,但 S0 数据库的每分钟运行一个作业的速度可能不够快,因此使用更高的服务层级可能会更好。For example, an S0 database might be sufficient for a job agent that runs few jobs an hour targeting less than ten databases, but running a job every minute might not be fast enough with an S0 database, and a higher service tier might be better.

如果针对作业数据库的操作的速度比预期慢,则在出现速度缓慢的情况时使用 Azure 门户或 sys.dm_db_resource_stats DMV 监视作业数据库中的数据库性能和资源利用率。If operations against the job database are slower than expected, monitor database performance and the resource utilization in the job database during periods of slowness using Azure portal or the sys.dm_db_resource_stats DMV. 如果资源(如 CPU、数据 IO 或日志写入)的使用率达到 100%,且与出现缓慢情况的时间段相关,请考虑以增量方式将数据库扩展到更高的服务目标(采用 DTU 模型vCore 模型),直到工作数据库性能得到充分改进。If utilization of a resource, such as CPU, Data IO, or Log Write approaches 100% and correlates with periods of slowness, consider incrementally scaling the database to higher service objectives (either in the DTU model or in the vCore model) until job database performance is sufficiently improved.

作业数据库权限Job database permissions

在创建作业代理期间,会在作业数据库中创建一个架构、多个表和一个名为 jobs_reader 的角色。During job agent creation, a schema, tables, and a role called jobs_reader are created in the Job database. 此角色使用以下权限创建,旨在为管理员提供进行作业监视所需的更细致访问控制:The role is created with the following permission and is designed to give administrators finer access control for job monitoring:

角色名称Role name “作业”架构权限'jobs' schema permissions “jobs_internal”架构权限'jobs_internal' schema permissions
jobs_readerjobs_reader SELECTSELECT None

重要

在以数据库管理员身份授予作业数据库的访问权限之前,请考虑清楚安全隐患。Consider the security implications before granting access to the Job database as a database administrator. 有权创建或编辑作业的恶意用户可能会创建或编辑一个作业,以便使用存储的凭据连接到受其控制的数据库,从而确定凭据的密码。A malicious user with permissions to create or edit jobs could create or edit a job that uses a stored credential to connect to a database under the malicious user's control, which could allow the malicious user to determine the credential’s password.

目标组Target group

目标组定义可以在其上执行作业步骤的数据库集。A target group defines the set of databases a job step will execute on. 目标组可以包含任意数目和任意组合的以下项:A target group can contain any number and combination of the following:

  • SQL Server - 如果指定一个服务器,则在执行作业时存在于该服务器中的所有数据库都会成为组的一部分。Logical SQL server - if a server is specified, all databases that exist in the server at the time of the job execution are part of the group. 必须提供 master 数据库凭据,然后才能在执行作业之前枚举和更新组。The master database credential must be provided so that the group can be enumerated and updated prior to job execution.
  • 弹性池 - 如果指定一个弹性池,则在执行作业时位于该弹性池中的所有数据库都会成为组的一部分。Elastic pool - if an elastic pool is specified, all databases that are in the elastic pool at the time of the job execution are part of the group. 就服务器来说,必须提供 master 数据库凭据,然后才能在执行作业之前更新组。As for a server, the master database credential must be provided so that the group can be updated prior to the job execution.
  • 单个数据库 - 指定一个或多个将要成为组的一部分的单独数据库。Single database - specify one or more individual databases to be part of the group.
  • 分片映射 - 一个分片映射的数据库。Shardmap - databases of a shardmap.

提示

在执行作业时,动态枚举会重新评估包含服务器或池的目标组中的数据库集。At the moment of job execution, dynamic enumeration re-evaluates the set of databases in target groups that include servers or pools. 动态枚举确保在执行作业时,作业可以跨服务器或池中存在的所有数据库运行Dynamic enumeration ensures that jobs run across all databases that exist in the server or pool at the time of job execution. 在池或服务器成员身份更改频繁的情况下,在运行时重新评估数据库列表特别有用。Re-evaluating the list of databases at runtime is specifically useful for scenarios where pool or server membership changes frequently.

可以将池和单个数据库指定为包括在组中或从组中排除。Pools and single databases can be specified as included or excluded from the group. 这样就可以使用任意数据库组合来创建目标组。This enables creating a target group with any combination of databases. 例如,可以向目标组添加一个服务器,但将弹性池中的特定数据库排除出去(也可以排除整个池)。For example, you can add a server to a target group, but exclude specific databases in an elastic pool (or exclude an entire pool).

目标组可以包括多个区域的多个订阅中的数据库。A target group can include databases in multiple subscriptions, and across multiple regions. 请注意,跨区域执行的延迟高于同一区域内的执行。Note that cross-region executions have higher latency than executions within the same region.

以下示例演示了如何在执行作业时动态枚举不同的目标组定义,以便确定作业要运行哪些数据库:The following examples show how different target group definitions are dynamically enumerated at the moment of job execution to determine which databases the job will run:

目标组示例

示例 1 演示的目标组包含一个由各个数据库组成的列表。Example 1 shows a target group that consists of a list of individual databases. 使用此目标组执行某个作业步骤时,作业步骤的操作会在这其中的每个数据库中执行。When a job step is executed using this target group, the job step's action will be executed in each of those databases.
示例 2 演示的目标组包含一个充当目标的服务器。Example 2 shows a target group that contains a server as a target. 使用此目标组执行某个作业步骤时,会动态枚举服务器,以便确定目前在服务器中的数据库的列表。When a job step is executed using this target group, the server is dynamically enumerated to determine the list of databases that are currently in the server. 作业步骤的操作会在这其中的每个数据库中执行。The job step's action will be executed in each of those databases.
示例 3 演示的目标组与示例 2 的类似,但明确排除了单个数据库。Example 3 shows a similar target group as Example 2, but an individual database is specifically excluded. 作业步骤的操作不会在排除的数据库中执行。The job step's action will not be executed in the excluded database.
示例 4 演示的目标组包含一个充当目标的弹性池。Example 4 shows a target group that contains an elastic pool as a target. 示例 2 类似,此池会在作业运行时动态枚举,以便确定池中数据库的列表。Similar to Example 2, the pool will be dynamically enumerated at job run time to determine the list of databases in the pool.

目标组示例

示例 5示例 6 演示高级方案,其中的服务器、弹性池和数据库可以使用包括和排除规则进行组合。Example 5 and Example 6 show advanced scenarios where servers, elastic pools, and databases can be combined using include and exclude rules.
示例 7 表明分片映射中的分片也可在作业运行时进行评估。Example 7 shows that the shards in a shard map can also be evaluated at job run time.

备注

作业数据库本身可以是作业的目标。The Job database itself can be the target of a job. 在这种情况下,会像处理任何其他目标数据库一样处理作业数据库。In this scenario, the Job database is treated just like any other target database. 必须在作业数据库中创建作业用户并为其授予足够权限,并且该作业用户的数据库范围的凭据也必须存在于作业数据库中,就像任何其他目标数据库的情况一样。The job user must be created and granted sufficient permissions in the Job database, and the database scoped credential for the job user must also exist in the Job database, just like it does for any other target database.

作业Job

作业是按计划执行的或只执行一次的工作单元。A job is a unit of work that is executed on a schedule or as a one-time job. 作业包含一个或多个作业步骤。A job consists of one or more job steps.

作业步骤Job step

每个作业步骤都会指定一个要执行的 T-SQL 脚本、一个或多个要对其运行 T-SQL 脚本的目标组,以及作业代理连接到目标数据库所需的凭据。Each job step specifies a T-SQL script to execute, one or more target groups to run the T-SQL script against, and the credentials the job agent needs to connect to the target database. 每个作业步骤都有可自定义的超时和重试策略,并且可以选择性地指定输出参数。Each job step has customizable timeout and retry policies, and can optionally specify output parameters.

作业输出Job output

针对每个目标数据库执行的作业步骤的结果会详细记录,而脚本输出则可捕获到指定的表中。The outcome of a job's steps on each target database are recorded in detail, and script output can be captured to a specified table. 可以指定一个数据库,用于保存从作业返回的任何数据。You can specify a database to save any data returned from a job.

作业历史记录Job history

作业执行历史记录存储在作业数据库中。Job execution history is stored in the Job database. 系统清除作业会清除时间超过 45 天的执行历史记录。A system cleanup job purges execution history that is older than 45 days. 若要删除时间不到 45 天的历史记录,请调用作业数据库中的 sp_purge_history 存储过程。To remove history less than 45 days old, call the sp_purge_history stored procedure in the Job database.

代理性能、容量和限制Agent performance, capacity, and limitations

弹性作业在等待长时间运行的作业完成时使用极少的计算资源。Elastic Jobs use minimal compute resources while waiting for long-running jobs to complete.

根据目标数据库组的大小和作业所需执行时间(并发辅助角色数)的不同,代理需要的计算量和作业数据库性能也会有所不同(目标数和作业数越多,所需计算量越大)。Depending on the size of the target group of databases and the desired execution time for a job (number of concurrent workers), the agent requires different amounts of compute and performance of the Job database (the more targets and the higher number of jobs, the higher the amount of compute required).

目前,预览版的限制是 100 个并发作业。Currently, the preview is limited to 100 concurrent jobs.

防止作业降低目标数据库性能Prevent jobs from reducing target database performance

若要确保针对 SQL 弹性池中的数据库运行作业时资源不会超负荷,可以对作业进行配置,限制可以在同一时间对其运行作业的数据库数。To ensure resources aren't overburdened when running jobs against databases in a SQL elastic pool, jobs can be configured to limit the number of databases a job can run against at the same time.

后续步骤Next steps