T-SQL を使用したエラスティックジョブの作成と管理

[アーティクル]
04/03/2024

この記事では、T-SQL を使用してエラスティックジョブを使い始めるためのチュートリアルと例を提供します。エラスティックジョブを使用すると、複数のデータベースにわたって 1 つまたは複数の Transact-SQL (T-SQL) スクリプトを同時に実行できます。

この記事の例では、"ジョブデータベース" で利用できるストアドプロシージャとビューを使います。

このエンドツーエンドのチュートリアルでは、複数のデータベースにわたってクエリを実行するために必要な手順を学習します。

エラスティックジョブエージェントを作成する
ジョブによってターゲット上でスクリプトを実行できるようにジョブ認証情報を作成する
ジョブを実行するターゲット (サーバー、エラスティックプール、データベース) を定義する
エージェントが接続してジョブを実行できるように、ターゲットデータベースにデータベーススコープ資格情報を作成する
ジョブの作成
ジョブにジョブステップを追加する
ジョブの実行を開始する
ジョブを監視する

エラスティックジョブエージェントを作成する

ジョブの作成、構成、実行、管理には、Transact-SQL (T-SQL) を使います。

エラスティックジョブエージェントの作成は T-SQL ではサポートされていないので、最初に Azure portal を使用してエラスティックジョブエージェントを作成する、または PowerShell を使用してエラスティックジョブエージェントを作成する必要があります。

ジョブ認証を作成する

エラスティックジョブエージェントは、各ターゲットサーバーまたはデータベースに対して認証可能である必要があります。ジョブエージェント認証の作成で説明されているように、推奨される方法は、ユーザー割り当てマネージド ID (UMI) で Microsoft Entra 認証 (旧称 Azure Active Directory) を使用することです。以前は、データベーススコープの資格情報が唯一のオプションでした。

ジョブの実行に UMI を使用した Microsoft Entra 認証

ユーザー割り当てマネージド ID (UMI) に対する Microsoft Entra (旧称 Azure Active Directory) 認証の推奨される方法を使用するには、次の手順に従います。エラスティックジョブエージェントは、Microsoft Entra 認証を介して目的のターゲット論理サーバー/データベースに接続します。

ログインユーザーとデータベースユーザーに加えて、次のスクリプトに GRANT コマンドが追加されていることに注意してください。これらのアクセス許可は、このサンプルジョブ用に選択したスクリプトに必要です。ジョブには、異なるアクセス許可が必要な場合があります。この例では、ターゲットデータベースに新しいテーブルを作成するため、正常に実行するには各ターゲットデータベースのデータベースユーザーに適切なアクセス許可が必要です。

各ターゲットサーバー/データベースで、UMI にマップされた包含ユーザーを作成します。

エラスティックジョブに論理サーバーまたはプールターゲットがある場合は、ターゲット論理サーバーの master データベース内の UMI にマップされた包含ユーザーを作成する必要があります。
たとえば、job-agent-UMI という名前のユーザー割り当てマネージド ID (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

次に、ターゲットサーバーにログインを作成するか、ターゲットデータベースに包含データベースユーザーを作成します。

重要

各ターゲットサーバー/データベースのログイン/ユーザーには、ジョブユーザーのデータベーススコープ資格情報の ID と同じ名前と、ジョブユーザーのデータベーススコープ資格情報と同じパスワードが必要です。

論理 SQL サーバーの 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;

論理サーバーでログインが必要ない場合は、包含データベースユーザーを作成します。通常、このエラスティックジョブエージェントで管理するデータベースが 1 つしかない場合にのみ行います。

--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 という名前のデータベースを除く、サーバー内のすべてのデータベースに対してジョブを実行する方法を示します。

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

ターゲットグループ (プール) を作成する

次の例では、1 つまたは複数のエラスティックプール内のすべてのデータベースをターゲットにする方法を示します。

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 エージェントジョブとジョブステップを作成するために必要な手順に似た構文です。

ジョブデータベースの内部カタログビューは更新しないでください。これらのカタログビューを手動で変更すると、ジョブデータベースが破損し、エラーが発生する可能性があります。これらのビューは、読み取り専用クエリ専用です。 ジョブデータベースの jobs スキーマでストアドプロシージャを使用できます。

Microsoft Entra ID またはユーザー割り当てマネージド ID に対して Microsoft Entra 認証を使用してターゲットサーバー/データベースに対して認証を行う場合、@credential_name 引数を sp_add_jobstep または sp_update_jobstep に指定しないでください。同様に、オプションの @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());'

Note

エラスティックジョブのすべての時間は、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.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;

ジョブを取り消す

次の例は、ジョブ実行 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	すべてのターゲットグループのすべてのメンバーを表示します。

次のステップ

PowerShell を使用したエラスティックジョブの作成と管理

T-SQL を使用したエラスティックジョブの作成と管理

エラスティックジョブエージェントを作成する

ジョブ認証を作成する

ジョブの実行に UMI を使用した Microsoft Entra 認証

ジョブを実行するためにデータベーススコープの資格情報を使用する

ターゲットサーバーとデータベースを定義する

データベースを個別に除外する

ターゲットグループ (プール) を作成する

ジョブとステップを作成する

サンプル

多数のデータベースに新しいスキーマをデプロイする

組み込みパラメーターを使用したデータ収集

データベースパフォーマンスの監視

ジョブの実行

ジョブの実行をスケジュールする

ジョブの定義を表示する

ジョブの実行状態を監視する

ジョブを取り消す

古いジョブの履歴を削除する

ジョブとそのすべてのジョブ履歴を削除する

ジョブのストアドプロシージャ

ジョブビュー

次のステップ

フィードバック

フィードバック

その他のリソース

T-SQL を使用したエラスティック ジョブの作成と管理

エラスティック ジョブ エージェントを作成する

ジョブ認証を作成する

ジョブの実行に UMI を使用した Microsoft Entra 認証

ジョブを実行するためにデータベース スコープの資格情報を使用する

ターゲット サーバーとデータベースを定義する

データベースを個別に除外する

ターゲット グループ (プール) を作成する

ジョブとステップを作成する

サンプル

多数のデータベースに新しいスキーマをデプロイする

組み込みパラメーターを使用したデータ収集

データベース パフォーマンスの監視

ジョブの実行

ジョブの実行をスケジュールする

ジョブの定義を表示する

ジョブの実行状態を監視する

ジョブを取り消す

古いジョブの履歴を削除する

ジョブとそのすべてのジョブ履歴を削除する

ジョブのストアド プロシージャ

ジョブ ビュー

次のステップ

フィードバック

フィードバック

その他のリソース

T-SQL を使用したエラスティックジョブの作成と管理

エラスティックジョブエージェントを作成する

ジョブを実行するためにデータベーススコープの資格情報を使用する

ターゲットサーバーとデータベースを定義する

ターゲットグループ (プール) を作成する

データベースパフォーマンスの監視

ジョブのストアドプロシージャ

ジョブビュー