使用弹性作业(预览版)自动完成管理任务Automate management tasks using elastic jobs (preview)

适用于: Azure SQL 数据库

可以创建和计划可针对一个或多个 Azure SQL 数据库定期执行的弹性作业,以运行 Transact-SQL (T-SQL) 查询和执行维护任务。You can create and schedule elastic jobs that could be periodically executed against one or many Azure SQL 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 elastic jobs

弹性作业自动化有多种使用场景:There are several scenarios when you could use elastic 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.
  • 配置作业,以便定期(例如,在非高峰时段)对一系列数据库执行作业。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

其他平台上的自动化Automation on other platforms

在不同平台上考虑以下作业安排技术:Consider the following job scheduling technologies on different platforms:

  • 弹性作业是针对一个或多个 Azure SQL 数据库中的数据库执行自定义作业的作业计划服务。Elastic Jobs are Job Scheduling services that execute custom jobs on one or many databases in Azure SQL Database.
  • SQL 代理作业由 SQL 代理服务执行,该服务继续用于 SQL Server 中的任务自动化,同时还包含在 Azure SQL 托管实例中。SQL Agent Jobs are executed by the SQL Agent service that continues to be used for task automation in SQL Server and is also included with Azure SQL Managed Instances. SQL 代理作业在 Azure SQL 数据库中不可用。SQL Agent Jobs are not available in Azure SQL Database.

弹性作业可以将 Azure SQL数据库Azure SQL 数据库弹性池分片映射中的 Azure SQL 数据库作为目标。Elastic Jobs can target Azure SQL Databases, Azure SQL Database elastic pools, and Azure SQL Databases in shard maps.

对于 SQL Server 和 Azure SQL 托管实例中的 T-SQL 脚本作业自动化,请考虑 SQL 代理For T-SQL script job automation in SQL Server and Azure SQL Managed Instance, consider SQL Agent.

对于 Azure Synapse Analytics 中的 T-SQL 脚本作业自动化,请考虑具有重复触发器的管道,这些管道基于 Azure 数据工厂。For T-SQL script job automation in Azure Synapse Analytics, consider pipelines with recurring triggers, which are based on Azure Data Factory.

值得注意的是,SQL 代理(在 SQL Server 中提供以及作为 SQL 托管实例的一部分提供)与数据库弹性作业代理(可对 Azure SQL 数据库或 SQL Server 和 Azure SQL 托管实例、Azure Synapse Analytics 中的数据库执行 T-SQL)之间存在差异。It is worth noting differences between SQL Agent (available in SQL Server and as part of SQL Managed Instance), and the Database Elastic Job agent (which can execute T-SQL on Azure SQL Databases or databases in SQL Server and Azure SQL Managed Instance, Azure Synapse Analytics).

弹性作业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 shard map (dynamically enumerated at job runtime).
SQL 代理所在实例中的任何单个数据库。Any individual database in the same instance as the SQL agent. SQL Server 代理的多服务器管理功能使主实例/目标实例能够协调作业执行,但此功能在 SQL 托管实例中不可用。The Multi Server Administration feature of SQL Server Agent allows for master/target instances to coordinate job execution, though this feature is not available in SQL managed instance.
支持的 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)

弹性作业目标Elastic job targets

使用弹性作业,可以按计划或按需跨大量数据库并行运行一个或多个 T-SQL 脚本。Elastic 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.

可以针对数据库的任意组合运行计划作业:可以是一个或多个单独的数据库,可以是一个服务器上的所有数据库,可以是一个弹性池中的所有数据库,也可以是分片映射,而且还可以灵活地包括或排除任何特定的数据库。You can run scheduled jobs against any combination of databases: one or more individual databases, all databases on a server, all databases in an elastic pool, or shard map, 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. 代理将这个现有的 Azure SQL 数据库配置为作业数据库The agent configures this existing Azure SQL 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.

弹性作业数据库Elastic 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 或更高的 Azure SQL 数据库。The Job database should be a clean, empty, S0 or higher service objective Azure SQL Database. 作业数据库的服务对象建议使用 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.

如果针对作业数据库的操作的速度比预期慢,则在出现速度缓慢的情况时使用 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.

弹性作业数据库权限Elastic 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. 有关逻辑服务器的详细信息,请参阅 Azure SQL 数据库和 Azure Synapse Analytics 中的服务器是什么?For more information on logical servers, see What is a server in Azure SQL Database and Azure Synapse Analytics?.
  • 弹性池 - 如果指定一个弹性池,则在执行作业时位于该弹性池中的所有数据库都会成为组的一部分。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.
  • 分片映射 - 分片映射的数据库。Shard map - databases of a shard map.

提示

在执行作业时,动态枚举会重新评估包含服务器或池的目标组中的数据库集。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.

弹性作业和作业步骤Elastic jobs and job steps

作业是按计划执行的或只执行一次的工作单元。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.

每个作业步骤都会指定一个要执行的 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

通过查询表 jobs.job_executions,在作业数据库中查看弹性作业执行历史记录。View Elastic Job execution history in the Job database by querying the table jobs.job_executions. 系统清除作业会清除时间超过 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.

作业状态Job status

可以通过查询表 jobs.job_executions,在作业数据库中监视弹性作业执行情况。You can monitor Elastic Job executions in the Job database by querying the table jobs.job_executions.

代理性能、容量和限制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 limit is 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