使用 T-SQL 建立及管理彈性作業

發行項
03/23/2025

適用於：Azure SQL 資料庫

本文提供許多範例案例，說明如何透過 T-SQL 開始使用彈性作業。彈性作業可讓您以平行方式，跨多個資料庫執行一個或多個 Transact-SQL (T-SQL) 指令碼。

這些範例使用作業資料庫中提供的預存程序和檢視。

在本端對端教學課程中，您將了解跨多個資料庫執行查詢所需的步驟：

建立彈性工作代理程式
建立作業認證，讓作業可在其目標上執行指令碼
定義您要對其執行工作的目標 (伺服器、彈性集區、資料庫)
在目標資料庫中建立資料庫範圍認證，讓代理程式可連接並執行工作
建立工作
將作業步驟新增至作業
開始執行作業
監視任務

建立彈性工作代理程式

Transact-SQL (T-SQL) 可用來建立、設定、執行和管理作業。

目前不支援以 T-SQL 建立彈性作業代理程式，因此您必須先使用入口網站或 PowerShell 建立彈性作業代理程式。

建立工作驗證

彈性工作代理程式必須能夠對每個目標伺服器或資料庫進行驗證。如建立作業代理程序驗證中所述，建議的方法是使用 Microsoft Entra 驗證（先前稱為 Azure Active Directory）搭配使用者指派的受控識別（UMI）。先前，資料庫範圍認證是唯一的選項。

使用 Microsoft Entra 驗證和 UMI 來執行作業

若要對使用者指派的受控識別 (UMI) 使用建議的 Microsoft Entra (先前稱為 Azure Active Directory) 驗證方法，請遵循下列步驟操作。彈性作業代理程式會透過 Microsoft Entra 驗證連接到所需的目標邏輯伺服器和資料庫。

除了登入和資料庫使用者之外，請留意在下列指令碼中新增的 GRANT 命令。我們為此範例作業選擇的指令碼需要這些權限。您的工作可能需要不同的權限。由於此範例會在目標資料庫中建立新的資料表，因此每個目標資料庫中的資料庫使用者都必須具備適當的權限才能成功執行。

在每個目標伺服器/資料庫中，建立對應至 UMI 的封裝使用者。

如果彈性工作有邏輯伺服器或集區目標，您必須在目標邏輯伺服器的 master 資料庫中建立對應至 UMI 的自包含使用者。
例如，若要根據名為 job-agent-UMI 的使用者指派的受控識別 (UMI)，在 master 資料庫中建立封閉資料庫登入，並在使用者資料庫中建立使用者：
若要執行這些 T-SQL 腳本，請針對您的資料庫連線使用 Microsoft Entra 驗證。

-- Connect to the master database of the Azure SQL logical instance of job agent
-- To run these T-SQL scripts, use Microsoft Entra authentication for your database connection.

-- 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 the master database mapped to a login
CREATE USER [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 job-agent-UMI;
GRANT CREATE TABLE TO job-agent-UMI;

如果在邏輯伺服器上不需要登入，請建立內部資料庫使用者：

-- 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 job-agent-UMI;
GRANT CREATE TABLE TO job-agent-UMI;

使用資料庫範圍認證來執行作業

用於資料庫範圍的認證可用來連接至執行指令碼的目標資料庫。認證必須具有適當權限 (對於目標群組所指定的資料庫)，才能成功執行指令碼。在使用邏輯 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='<password>';  

-- 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 = '<password>';
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 = '<password>';
GO

然後，在目標伺服器上建立登入帳號，或在目標資料庫上建立獨立資料庫使用者。

重要

每個目標伺服器/資料庫的登入/使用者都必須與作業使用者的資料庫範圍認證身分識別具有相同的名稱，並與作業使用者的資料庫範圍認證具有相同的密碼。

在 master 邏輯 SQL 伺服器的資料庫中建立一個登入帳戶，並在每個使用者資料庫中新增使用者。

-- Create a login on the master database
CREATE LOGIN job_credential WITH PASSWORD='<password>';

-- 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='<password>';

-- 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.windows.net';

-- 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.windows.net';
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.windows.net';
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.windows.net',
  @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';

建立目標群組 (池)

下列範例說明如何以一或多個彈性集區中的所有資料庫作為目標。

連結至 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.windows.net',
  @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 Agent 作業及作業步驟的步驟。

您不應該更新作業資料庫中 的內部目錄檢視。手動變更這些目錄檢視可能會損毀作業資料庫並導致失敗。這些檢視僅適用於唯讀查詢。您可以在 jobs 的架構中使用預存程式。

針對 Microsoft Entra 識別碼或使用者指派的受控識別使用 Microsoft Entra 驗證來驗證目標伺服器/資料庫時，不應該為或 sp_add_jobstep提供 sp_update_jobstep 自變數。同樣地，請省略選擇性 @output_credential_name 和 @refresh_credential_name 自變數。
使用資料庫範圍的認證來驗證目標伺服器/資料庫時，必須提供 @credential_name 參數給 sp_add_jobstep 和 sp_update_jobstep。
- 例如： @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，以及 SELECT 目錄檢視上的。

如果您想事先手動建立資料表，則其必須具有下列屬性：

結果集中的資料行具有正確的名稱和資料類型。
internal_execution_id 的其他資料行 (資料類型為 uniqueidentifier)。
在 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.windows.net',
  @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.windows.net',
  @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.windows.net',
  @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;

取消工作

下列範例示範如何擷取作業執行標識碼，然後取消作業執行。

連結至 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 Agent 服務所使用的系統預存程式不同。

預存程序	描述
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	移除作業的歷史記錄。

職缺檢視

作業資料庫中提供下列檢視。

檢視	描述
工作執行	顯示作業執行歷程記錄。
工作	顯示所有工作。
工作版本	顯示所有作業版本。
jobsteps	顯示每項作業的目前版本中包含的所有步驟。
jobstep_versions	顯示每項作業的所有版本中包含的所有步驟。
目標群體	顯示所有目標群組。
目標群組成員	顯示所有目標群組的所有成員。

後續步驟

使用 PowerShell 建立及管理彈性作業

共用方式為

使用 T-SQL 建立及管理彈性作業

建立彈性工作代理程式

建立工作驗證

使用 Microsoft Entra 驗證和 UMI 來執行作業

使用資料庫範圍認證來執行作業

定義目標伺服器和資料庫

排除個別資料庫

建立目標群組 (池)

建立工作和步驟

範例

將新的結構描述部署至多個資料庫

使用內建參數的資料收集

監視資料庫效能

執行作業

計畫執行作業的時間

檢視作業定義

監視作業執行狀態

取消工作

刪除舊作業歷程記錄

刪除工作及其歷程記錄

作業預存程序

職缺檢視

後續步驟

意見反應

其他資源