使用 T-SQL 创建和管理弹性作业
适用于：
Azure SQL 数据库
本文通过教程和示例说明了如何使用 T-SQL 来完成弹性作业的入门。 弹性作业可以跨多个数据库并行运行一个或多个 Transact-SQL (T-SQL) 脚本。
本端到端教程介绍跨多个数据库运行查询所需的步骤：
- 创建弹性作业代理
- 创建作业凭据，使作业可以在其目标上执行脚本
- 定义要对其运行作业的目标（服务器、弹性池、数据库）
- 在目标数据库中创建数据库范围的凭据，以便代理在连接后执行作业
- 创建作业
- 向作业添加作业步骤
- 开始执行作业
- 监视作业
创建弹性作业代理
Transact-SQL (T-SQL) 可用于创建、配置、执行和管理作业。
T-SQL 不支持创建弹性作业代理，因此必须先使用 Azure 门户创建弹性作业代理或使用 PowerShell 创建弹性作业代理。
创建作业身份验证
弹性作业代理必须能够对每个目标服务器或数据库进行身份验证。 如创建作业代理身份验证中所述，建议的方法是将 Microsoft Entra 身份验证（以前称为 Azure Active Directory）与用户分配的托管标识 (UMI) 配合使用。 以前，数据库范围的凭据是唯一选项。
将 Microsoft Entra 身份验证与 UMI 配合使用来执行作业
要按照建议将 Microsoft Entra（以前称为 Azure Active Directory）身份验证与用户分配的托管标识 (UMI) 配合使用，请执行以下步骤。 弹性作业代理通过 Microsoft Entra 身份验证连接到所需的目标逻辑服务器/数据库。
请注意，除了登录名和数据库用户，还在以下脚本中添加了 GRANT 命令。 这些权限是为此示例作业选择的脚本所需要的。 作业可能需要不同的权限才能运行。 由于此示例会在目标数据库中创建新表，因此每个目标数据库中的数据库用户需要适当的权限才能成功运行。
在每个目标服务器/数据库中，创建映射到 UMI 的包含用户。
- 如果弹性作业具有逻辑服务器或池目标，则必须在目标逻辑服务器的
master数据库中创建映射到 UMI 的包含用户。 - 例如，若要根据名为
job-agent-UMI的用户分配的托管标识 (UMI) 在master数据库中创建包含的数据库登录名，并在用户数据库中创建用户：
--Create a login on the master database mapped to a user-assigned managed identity (UMI)
CREATE LOGIN [job-agent-UMI] FROM EXTERNAL PROVIDER;
--Create a user on a user database mapped to a login.
CREATE USER [job-agent-UMI] FROM LOGIN [job-agent-UMI];
-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO jobuser;
GRANT CREATE TABLE TO jobuser;
- 逻辑服务器上不需要登录名时若要创建包含的数据库用户：
--Create a contained database user on a user database mapped to a user-assigned managed identity (UMI)
CREATE USER [job-agent-UMI] FROM EXTERNAL PROVIDER;
-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO jobuser;
GRANT CREATE TABLE TO jobuser;
使用数据库范围的凭据执行作业
数据库范围的凭据用于连接到目标数据库，以便执行脚本。 此凭据需要在目标组指定的数据库上拥有适当的权限，否则无法成功地执行脚本。 使用逻辑 SQL 服务器和/或池目标组成员时，建议创建一个用于刷新此凭据的凭据，然后再在执行作业时扩展服务器和/或池。 数据库范围的凭据在作业代理数据库中创建。
必须使用同一凭据来创建登录名，并创建基于登录名的用户，以便在所有目标数据库上授予登录数据库权限。
--Connect to the new job database specified when creating the elastic job agent
-- Create a database master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';
-- Create two database-scoped credentials.
-- The credential to connect to the Azure SQL logical server, to execute jobs
CREATE DATABASE SCOPED CREDENTIAL job_credential WITH IDENTITY = 'job_credential',
SECRET = '<EnterStrongPasswordHere>';
GO
-- The credential to connect to the Azure SQL logical server, to refresh the database metadata in server
CREATE DATABASE SCOPED CREDENTIAL refresh_credential WITH IDENTITY = 'refresh_credential',
SECRET = '<EnterStrongPasswordHere>';
GO
然后，在目标服务器上创建登录名，或在目标数据库上创建包含的数据库用户。
重要
每个目标服务器/数据库的登录名/用户必须采用与作业用户的数据库范围凭据的标识相同的名称，并采用与作业用户的数据库范围凭据相同的密码。
在逻辑 SQL Server 的 master 数据库中创建登录名，并在每个用户数据库中创建用户。
--Create a login on the master database
CREATE LOGIN job_credential WITH PASSWORD='<Enter_same_StrongPassword_as_database_scoped_credential>';
--Create a user on a user database mapped to a login.
CREATE USER [job_credential] FROM LOGIN [job_credential];
-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO job_credential;
GRANT CREATE TABLE TO job_credential;
如果逻辑服务器上不需要登录名，则创建包含的数据库用户。 通常，只有当你有一个数据库需要使用此弹性作业代理进行管理时，你才会执行此操作。
--Create a contained database user on a user database mapped to a Microsoft Entra account
CREATE USER [job_credential] WITH PASSWORD='<Enter_same_StrongPassword_as_database_scoped_credential>';
-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO job_credential;
GRANT CREATE TABLE TO job_credential;
定义目标服务器和数据库
以下示例演示如何针对服务器中的所有数据库执行作业。
连接到 job_database 并运行以下命令来添加目标组和目标成员：
-- Connect to the job database specified when creating the job agent
-- Add a target group containing server(s)
EXEC jobs.sp_add_target_group 'ServerGroup1';
-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'ServerGroup1',
@target_type = 'SqlServer',
@server_name = 'server1.database.chinacloudapi.cn';
--View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name='ServerGroup1';
SELECT * FROM jobs.target_group_members WHERE target_group_name='ServerGroup1';
排除单个数据库
以下示例演示如何针对服务器中的所有数据库执行作业，名为 MappingDB 的数据库除外。
使用 Microsoft Entra 身份验证（以前称为为 Azure Active Directory）时，忽略 @refresh_credential_name 参数，仅当使用数据库范围的凭据时，才应提供此参数。 在以下示例中，已注释掉 @refresh_credential_name 参数。
连接到 job_database，然后运行以下命令：
--Connect to the job database specified when creating the job agent
-- Add a target group containing server(s)
EXEC [jobs].sp_add_target_group N'ServerGroup';
GO
-- Add a server target member
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@target_type = N'SqlServer',
--@refresh_credential_name = N'refresh_credential', --credential required to refresh the databases in a server
@server_name = N'London.database.chinacloudapi.cn';
GO
-- Add a server target member
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@target_type = N'SqlServer',
--@refresh_credential_name = N'refresh_credential', --credential required to refresh the databases in a server
@server_name = 'server2.database.chinacloudapi.cn';
GO
--Exclude a database target member from the server target group
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@membership_type = N'Exclude',
@target_type = N'SqlDatabase',
@server_name = N'server1.database.chinacloudapi.cn',
@database_name = N'MappingDB';
GO
--View the recently created target group and target group members
SELECT * FROM [jobs].target_groups WHERE target_group_name = N'ServerGroup';
SELECT * FROM [jobs].target_group_members WHERE target_group_name = N'ServerGroup';
创建目标组（池）
以下示例演示如何以一个或多个弹性池中的所有数据库为目标。
使用 Microsoft Entra 身份验证（以前称为为 Azure Active Directory）时，忽略 @refresh_credential_name 参数，仅当使用数据库范围的凭据时，才应提供此参数。 在以下示例中，已注释掉 @refresh_credential_name 参数。
连接到 job_database，然后运行以下命令：
--Connect to the job database specified when creating the job agent
-- Add a target group containing pool(s)
EXEC jobs.sp_add_target_group 'PoolGroup';
-- Add an elastic pool(s) target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'PoolGroup',
@target_type = 'SqlElasticPool',
--@refresh_credential_name = 'refresh_credential', --credential required to refresh the databases in a server
@server_name = 'server1.database.chinacloudapi.cn',
@elastic_pool_name = 'ElasticPool-1';
-- View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name = N'PoolGroup';
SELECT * FROM jobs.target_group_members WHERE target_group_name = N'PoolGroup';
创建作业和步骤
使用 T-SQL，在作业数据库中使用系统存储过程创建作业：jobs.sp_add_job 和 jobs.sp_add_jobstep。 T-SQL 命令的语法类似于在 SQL Server 中创建 SQL 代理作业和作业步骤所需的步骤。
不得更新“作业数据库”中的内部目录视图。 手动更改上述目录视图可能会损坏“作业数据库”并导致失败。 上述视图仅用于只读查询。 可使用“作业数据库” jobs 架构的中存储过程。
- 当使用适用于 Microsoft Entra ID 或用户分配的托管标识的 Microsoft Entra 身份验证对目标服务器/数据库进行身份验证时，不应为
sp_add_jobstep或sp_update_jobstep提供 @credential_name。 同样，忽略可选的 @output_credential_name 和 @refresh_credential_name 参数。 - 使用数据库范围的凭据对目标服务器/数据库进行身份验证时，需要为
sp_add_jobstep和sp_update_jobstep提供 @credential_name 参数。- 例如
@credential_name = 'job_credential'。
- 例如
以下示例提供了使用 T-SQL 创建作业和作业步骤的指南，以使用弹性作业完成常见任务。
示例
将新架构部署到多个数据库
以下示例演示如何将新架构部署到所有数据库。
连接到 job_database，然后运行以下命令：
--Connect to the job database specified when creating the job agent
--Add job for create table
EXEC jobs.sp_add_job @job_name = 'CreateTableTest', @description = 'Create Table Test';
-- Add job step for create table
EXEC jobs.sp_add_jobstep @job_name = 'CreateTableTest',
@command = N'IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(''Test''))
CREATE TABLE [dbo].[Test]([TestId] [int] NOT NULL);',
@target_group_name = 'PoolGroup';
使用内置参数进行数据收集
在许多数据收集方案中，可以包括部分下述脚本变量，以便对作业结果进行后期处理。
$(job_name)$(job_id)$(job_version)$(step_id)$(step_name)$(job_execution_id)$(job_execution_create_time)$(target_group_name)
例如，若要将同一作业执行操作的所有结果组合到一起，请使用 $(job_execution_id)，如以下命令所示：
@command= N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());'
注意
弹性作业中的所有时间均处于 UTC 时区。
监视数据库性能
以下示例创建一个新作业，以便从多个数据库收集性能数据。
默认情况下，作业代理将创建输出表以存储返回的结果。 因此，与输出凭据关联的数据库主体至少必须具有以下权限：数据库上的 CREATE TABLE、输出表或其架构上的 ALTER、SELECT、INSERT、DELETE，以及 sys.indexes 目录视图上的 SELECT。
如果要提前手动创建表，则该表需要具有以下属性：
- 具有结果集的正确名称和数据类型的列。
- 数据类型为 uniqueidentifier 的
internal_execution_id的其他列。 internal_execution_id列上名为IX_<TableName>_Internal_Execution_ID的非聚集索引。- 之前列出的所有权限（数据库的
CREATE TABLE权限除外）。
连接到作业数据库，然后运行以下命令：
--Connect to the job database specified when creating the job agent
-- Add a job to collect perf results
EXEC jobs.sp_add_job @job_name ='ResultsJob', @description='Collection Performance data from all customers'
-- Add a job step w/ schedule to collect results
EXEC jobs.sp_add_jobstep
@job_name = 'ResultsJob',
@command = N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());',
@target_group_name = 'PoolGroup',
@output_type = 'SqlDatabase',
@output_server_name = 'server1.database.chinacloudapi.cn',
@output_database_name = '<resultsdb>',
@output_table_name = '<output_table_name>';
--Create a job to monitor pool performance
--Connect to the job database specified when creating the job agent
-- Add a target group containing elastic job database
EXEC jobs.sp_add_target_group 'ElasticJobGroup';
-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'ElasticJobGroup',
@target_type = 'SqlDatabase',
@server_name = 'server1.database.chinacloudapi.cn',
@database_name = 'master';
-- Add a job to collect perf results
EXEC jobs.sp_add_job
@job_name = 'ResultsPoolsJob',
@description = 'Demo: Collection Performance data from all pools',
@schedule_interval_type = 'Minutes',
@schedule_interval_count = 15;
-- Add a job step w/ schedule to collect results
EXEC jobs.sp_add_jobstep
@job_name='ResultsPoolsJob',
@command=N'declare @now datetime
DECLARE @startTime datetime
DECLARE @endTime datetime
DECLARE @poolLagMinutes datetime
DECLARE @poolStartTime datetime
DECLARE @poolEndTime datetime
SELECT @now = getutcdate ()
SELECT @startTime = dateadd(minute, -15, @now)
SELECT @endTime = @now
SELECT @poolStartTime = dateadd(minute, -30, @startTime)
SELECT @poolEndTime = dateadd(minute, -30, @endTime)
SELECT elastic_pool_name , end_time, elastic_pool_dtu_limit, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, max_worker_percent, max_session_percent,
avg_storage_percent, elastic_pool_storage_limit_mb FROM sys.elastic_pool_resource_stats
WHERE end_time > @poolStartTime and end_time <= @poolEndTime;
',
@target_group_name = 'ElasticJobGroup',
@output_type = 'SqlDatabase',
@output_server_name = 'server1.database.chinacloudapi.cn',
@output_database_name = 'resultsdb',
@output_table_name = '<output_table_name>';
运行作业
以下示例显示如何通过手动的计划外操作立即启动作业。
连接到 job_database，然后运行以下命令：
--Connect to the job database specified when creating the job agent
-- Execute the latest version of a job
EXEC jobs.sp_start_job 'CreateTableTest';
-- Execute the latest version of a job and receive the execution ID
declare @je uniqueidentifier;
exec jobs.sp_start_job 'CreateTableTest', @job_execution_id = @je output;
select @je;
-- Monitor progress
SELECT * FROM jobs.job_executions WHERE job_execution_id = @je;
计划作业的执行
以下示例显示如何将作业安排在未来每 15 分钟重复执行一次。
连接到 job_database，然后运行以下命令：
--Connect to the job database specified when creating the job agent
EXEC jobs.sp_update_job
@job_name = 'ResultsJob',
@enabled=1,
@schedule_interval_type = 'Minutes',
@schedule_interval_count = 15;
查看作业定义
以下示例演示了如何查看当前的作业定义。
连接到 job_database，然后运行以下命令：
--Connect to the job database specified when creating the job agent
-- View all jobs
SELECT * FROM jobs.jobs;
-- View the steps of the current version of all jobs
SELECT js.* FROM jobs.jobsteps js
JOIN jobs.jobs j
ON j.job_id = js.job_id AND j.job_version = js.job_version;
-- View the steps of all versions of all jobs
SELECT * FROM jobs.jobsteps;
监视作业执行状态
以下示例演示如何查看所有作业的执行状态详细信息。
连接到 job_database，然后运行以下命令：
--Connect to the job database specified when creating the job agent
--View top-level execution status for the job named 'ResultsPoolJob'
SELECT * FROM jobs.job_executions
WHERE job_name = 'ResultsPoolsJob' and step_id IS NULL
ORDER BY start_time DESC;
--View all top-level execution status for all jobs
SELECT * FROM jobs.job_executions WHERE step_id IS NULL
ORDER BY start_time DESC;
--View all execution statuses for job named 'ResultsPoolsJob'
SELECT * FROM jobs.job_executions
WHERE job_name = 'ResultsPoolsJob'
ORDER BY start_time DESC;
-- View all active executions
SELECT * FROM jobs.job_executions
WHERE is_active = 1
ORDER BY start_time DESC;
取消作业
以下示例演示如何检索作业执行 ID，然后取消作业执行。
连接到 job_database，然后运行以下命令：
--Connect to the job database specified when creating the job agent
-- View all active executions to determine job execution ID
SELECT * FROM jobs.job_executions
WHERE is_active = 1 AND job_name = 'ResultPoolsJob'
ORDER BY start_time DESC;
GO
-- Cancel job execution with the specified job execution ID
EXEC jobs.sp_stop_job '01234567-89ab-cdef-0123-456789abcdef';
删除旧的作业历史记录
以下示例演示如何删除特定日期之前的作业历史记录。
连接到 job_database，然后运行以下命令：
--Connect to the job database specified when creating the job agent
-- Delete history of a specific job's executions older than the specified date
EXEC jobs.sp_purge_jobhistory @job_name='ResultPoolsJob', @oldest_date='2016-07-01 00:00:00';
--Note: job history is automatically deleted if it is >45 days old
删除作业及其所有历史记录
以下示例演示如何删除作业以及所有相关的作业历史记录。
连接到 job_database，然后运行以下命令：
--Connect to the job database specified when creating the job agent
EXEC jobs.sp_delete_job @job_name='ResultsPoolsJob';
EXEC jobs.sp_purge_jobhistory @job_name='ResultsPoolsJob';
--Note: job history is automatically deleted if it is >45 days old
作业存储过程
以下存储过程位于作业数据库中。 它们虽具有相似的名称，但与用于 SQL Server 代理服务的系统存储过程明显不同。
| 存储过程 | 说明 |
|---|---|
| sp_add_job | 添加新的作业。 |
| sp_update_job | 更新现有的作业。 |
| sp_delete_job | 删除现有的作业。 |
| sp_add_jobstep | 向作业添加步骤。 |
| sp_update_jobstep | 更新作业步骤。 |
| sp_delete_jobstep | 删除作业步骤。 |
| sp_start_job | 开始执行作业。 |
| sp_stop_job | 停止作业的执行。 |
| sp_add_target_group | 添加目标组。 |
| sp_delete_target_group | 删除目标组。 |
| sp_add_target_group_member | 向目标组添加一个或一组数据库。 |
| sp_delete_target_group_member | 从目标组中删除目标组成员。 |
| sp_purge_jobhistory | 删除作业的历史记录。 |
作业视图
以下视图在作业数据库中提供。
| 查看 | 说明 |
|---|---|
| job_executions | 显示作业执行历史记录。 |
| jobs | 显示所有作业。 |
| job_versions | 显示所有作业版本。 |
| jobsteps | 显示每项作业的当前版本中的所有步骤。 |
| jobstep_versions | 显示每项作业的所有版本中的所有步骤。 |
| target_groups | 显示所有目标组。 |
| target_group_members | 显示所有目标组的所有成员。 |