使用 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());'

监视数据库性能

以下示例创建一个新作业，以便从多个数据库收集性能数据。

默认情况下，作业代理将创建输出表以存储返回的结果。 因此，与输出凭据关联的数据库主体至少必须具有以下权限：数据库上的 CREATE TABLE、输出表或其架构上的 ALTER、SELECT、INSERT、DELETE，以及 sys.indexes 目录视图上的 SELECT。

如果要提前手动创建表，则该表需要具有以下属性：

1. 具有结果集的正确名称和数据类型的列。
2. 数据类型为 uniqueidentifier 的 internal_execution_id 的其他列。
3. internal_execution_id 列上名为 IX_<TableName>_Internal_Execution_ID 的非聚集索引。
4. 之前列出的所有权限（数据库的 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	显示所有目标组的所有成员。

下一步