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

適用於：Azure SQL 資料庫

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

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

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

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

建立彈性工作代理程式

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

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

建立工作驗證

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

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

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

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

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

• 如果彈性工具有邏輯伺服器或集區目標，您必須在目標邏輯伺服器的 master 資料庫中建立對應至 UMI的自主使用者。
• 例如，若要根據名為 master 的使用者指派的受控識別 (UMI)，在 job-agent-UMI 資料庫中建立自主資料庫登入，並在使用者資料庫中建立使用者：

--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

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

重要

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

在 master 邏輯 SQL 伺服器的資料庫中建立登入，以及每個使用者資料庫中的使用者。

--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.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 的資料庫以外 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';

建立目標資料庫 (集區)

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

使用 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.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提供 @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。

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

1. 結果集使用正確名稱和資料類型的資料行。
2. internal_execution_id 的其他資料行 (資料類型為 uniqueidentifier)。
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.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	移除作業的記錄。

作業檢視

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

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

後續步驟

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