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

適用対象: Azure SQL データベース

この記事では、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='<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

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

論理 SQL サーバーの master データベースと各ユーザー データベースのユーザーにログインを作成します。

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

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

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

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

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

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

ジョブ データベースの内部カタログ ビューは更新しないでください。 これらのカタログ ビューを手動で変更すると、ジョブ データベースが破損し、エラーが発生する可能性があります。 これらのビューは、読み取り専用クエリ専用です。 ジョブ データベースの 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());'

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

次の例では、複数のデータベースからパフォーマンス データを収集する新しいジョブを作成します。

既定では、ジョブ エージェントは返された結果を格納する出力テーブルを作成します。 そのため、出力資格情報に関連付けられているデータベース プリンシパルには、少なくとも次の権限が必要です。データベースでは 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.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 エージェント サービスに使用されるシステム ストアド プロシージャとは異なります。

ジョブ ビュー

ジョブ データベースでは次のビューを使用できます。

次のステップ