创建、配置和管理弹性作业Create, configure, and manage elastic jobs

适用于:是Azure SQL 数据库 APPLIES TO: yesAzure SQL Database

在本文中,你将了解如何创建、配置和管理弹性作业。In this article, you will learn how to create, configure, and manage elastic jobs.

如果未使用弹性作业,请详细了解 Azure SQL 数据库中的作业自动化概念If you have not used Elastic jobs, learn more about the job automation concepts in Azure SQL Database.

创建并配置代理Create and configure the agent

  1. 创建或标识空的 S0 或更高级别的数据库。Create or identify an empty S0 or higher database. 该数据库在弹性作业代理创建期间将用作“作业数据库”。This database will be used as the Job database during Elastic Job agent creation.

  2. 通过门户PowerShell 创建弹性作业代理。Create an Elastic Job agent in the portal or with PowerShell.

    创建弹性作业代理

创建、运行和管理作业Create, run, and manage jobs

  1. 使用 PowerShellT-SQL 在作业数据库中创建执行作业所需的凭据。Create a credential for job execution in the Job database using PowerShell or T-SQL.

  2. 使用 PowerShellT-SQL 定义目标组(需对其运行作业的数据库)。Define the target group (the databases you want to run the job against) using PowerShell or T-SQL.

  3. 在作业将运行的每个数据库中创建作业代理凭据(向组中的每个数据库添加用户(或角色))Create a job agent credential in each database the job will run (add the user (or role) to each database in the group). 有关示例,请参阅 PowerShell 教程For an example, see the PowerShell tutorial.

  4. 使用 PowerShellT-SQL 创建作业。Create a job using PowerShell or T-SQL.

  5. 使用 PowerShellT-SQL 添加作业步骤。Add job steps using PowerShell or T-SQL.

  6. 使用 PowerShellT-SQL 运行作业。Run a job using PowerShell or T-SQL.

  7. 使用门户、PowerShellT-SQL 监视作业执行状态。Monitor job execution status using the portal, PowerShell or T-SQL.

    门户

运行作业所需的凭据Credentials for running jobs

作业在执行时使用数据库范围的凭据连接到目标组指定的数据库。Jobs use database scoped credentials to connect to the databases specified by the target group upon execution. 如果目标组包含服务器或池,则可使用这些数据库范围的凭据连接到 master 数据库,以便枚举可用的数据库。If a target group contains servers or pools, these database scoped credentials are used to connect to the master database to enumerate the available databases.

设置运行作业所需的适当凭据可能不太容易,因此请注意以下要点:Setting up the proper credentials to run a job can be a little confusing, so keep the following points in mind:

  • 必须在作业数据库中创建数据库范围的凭据。The database scoped credentials must be created in the Job database.
  • 所有目标数据库必须有一个具有足够权限的登录名,否则作业无法成功完成(下图中的 jobuser)。All target databases must have a login with sufficient permissions for the job to complete successfully (jobuser in the diagram below).
  • 凭据可以跨作业反复使用,而凭据密码经过加密后,无法供只能通过只读方式访问作业对象的用户访问。Credentials can be reused across jobs, and the credential passwords are encrypted and secured from users who have read-only access to job objects.

下图旨在帮助用户了解和设置适当的作业凭据。The following image is designed to assist in understanding and setting up the proper job credentials. 记住在作业需运行的每个数据库(所有目标用户数据库)中创建该用户Remember to create the user in every database (all target user dbs) the job needs to run.

弹性作业凭据

安全最佳做法Security best practices

使用弹性作业时的一些最佳做法注意事项:A few best practice considerations for working with Elastic Jobs:

  • 将 API 的使用限制为受信任的个人。Limit usage of the APIs to trusted individuals.
  • 凭据应该具有执行作业步骤所需的最低权限。Credentials should have the least privileges necessary to perform the job step. 有关详细信息,请参阅授权和权限For more information, see Authorization and Permissions.
  • 使用服务器和/或池目标组成员时,强烈建议创建另外一个凭据,该凭据有权在 master 数据库上查看/列出数据库,用于在作业执行之前展开服务器和/或池的数据库列表。When using a server and/or pool target group member, it is highly suggested to create a separate credential with rights on the master database to view/list databases that is used to expand the database lists of the server(s) and/or pool(s) prior to the job execution.

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

通过在 T-SQL 中设置 sp_add_jobstep 存储过程的 @max_parallelism 参数,或者通过在 PowerShell 中设置 Add-AzSqlElasticJobStep -MaxParallelism,来设置作业运行的并发数据库数。Set the number of concurrent databases a job runs on by setting the sp_add_jobstep stored procedure's @max_parallelism parameter in T-SQL, or Add-AzSqlElasticJobStep -MaxParallelism in PowerShell.

创建作业的最佳做法Best practices for creating jobs

幂等脚本Idempotent scripts

作业的 T-SQL 脚本必须幂等A job's T-SQL scripts must be idempotent. “幂等”是指如果脚本成功,则再次运行时,会出现相同的结果。Idempotent means that if the script succeeds, and it is run again, the same result occurs. 脚本可能由于暂时性网络问题而失败。A script may fail due to transient network issues. 在此情况下,作业会自动重试运行脚本,达到默认的次数才停止。In that case, the job will automatically retry running the script a preset number of times before desisting. 即使幂等脚本已成功运行两次(或更多次),也仍会返回相同的结果。An idempotent script has the same result even if its been successfully run twice (or more).

一个简单的策略是在创建对象之前测试其是否存在。A simple tactic is to test for the existence of an object before creating it.

IF NOT EXISTS (some_object)
    -- Create the object
    -- If it exists, drop the object before recreating it.

同样地,脚本必须以逻辑方式测试并反驳它所找到的任何条件,才能成功执行。Similarly, a script must be able to execute successfully by logically testing for and countering any conditions it finds.

后续步骤Next steps