Azure Logic Apps のワークフローでストアド プロシージャ中に大きな SQL 結果セットとタイムアウトを制御する

適用対象: Azure Logic Apps (従量課金 + 標準)

SQL データベースで動作するビジネス タスクをより簡単に自動化するために、ワークフローでは SQL Server コネクタ操作を使用できます。この操作により、 Azure Logic Apps で使用するワークフローに多くのバックエンド機能が提供されます。

状況によっては、ワークフローで大きな結果セットを処理する必要がある場合があります。 これらの結果セットは非常に大きいため、 SQL Server コネクタの操作ではすべての結果が同時に返されるわけではありません。 その他の状況では、結果セットのサイズと構造をより詳細に制御したい場合があります。 結果を目的の方法で整理するには、 ストアド プロシージャを作成します。

たとえば、SQL Server コネクタ アクションが複数の行を取得または挿入する場合、ワークフローでは、これらの制限内で機能する Until ループを使用して、これらの行を反復処理できます。 ワークフローで数千行または数百万行を処理する必要がある場合は、 SQL Server コネクタ アクションの SQL データベース呼び出しによるコストを最小限に抑える必要があります。 詳細については、「 SQL コネクタを使用した一括データの処理」を参照してください。

このガイドでは、 SQL Server コネクタアクションを使用して大きな結果セットを処理する際のサイズ、構造、タイムアウトを制御する方法について説明します。

ストアド プロシージャの実行のタイムアウト制限

SQL Server コネクタには、タイムアウト制限が 2 分未満のストアド プロシージャの実行アクションがあります。 一部のストアド プロシージャは、この制限を超えて完了するまでに時間がかかる場合があり、 504 タイムアウト エラーが発生します。 この目的のために、実行時間の長いプロセスがストアド プロシージャとして明示的にコーディングされる場合があります。 タイムアウトの制限により、Azure Logic Apps からこのようなプロシージャを呼び出すと問題が発生する可能性があります。

SQL Server コネクタの操作では、非同期モードはネイティブにサポートされていません。 この制限を回避するには、次の項目を使用してこのモードをシミュレートします。

• SQL 完了操作トリガー
• ネイティブ SQL パススルー クエリ
• 状態テーブル
• サーバー側ジョブ

たとえば、次の長時間実行されるストアドプロシージャがあると仮定します。 実行を完了するには、プロシージャがタイムアウト制限を超えています。 ストアド プロシージャの実行という名前の SQL Server コネクタ アクションを使用してワークフローからこの ストアド プロシージャを実行すると、 HTTP 504 ゲートウェイ タイムアウト エラーが発生します。

CREATE PROCEDURE [dbo].[WaitForIt]
   @delay char(8) = '00:03:00'
AS
BEGIN
   SET NOCOUNT ON;
   WAITFOR DELAY @delay
END

ストアド プロシージャを直接呼び出す代わりに、 ジョブ エージェントを使用してバックグラウンドでプロシージャを非同期的に実行できます。 入力と出力は状態テーブルに格納でき、ワークフローを通じてアクセスして管理できます。 入力と出力が不要な場合、またはストアド プロシージャのテーブルに結果を既に書き込んでいる場合は、このアプローチを簡略化できます。

クラウドベースの SQL Server のジョブ エージェントを使用してバックグラウンドでプロシージャを非同期に実行するには、Azure SQL Database 用 Azure Elastic Job Agent を作成して使用する手順に従います。

オンプレミスの SQL Server と Azure SQL Managed Instance の場合は、代わりに SQL Server エージェントを作成して使用 します。 基本的な手順は、Azure SQL Database のジョブ エージェントの設定と同じです。

Azure SQL Database のジョブ エージェントを作成する

Azure SQL Database のストアド プロシージャを実行できるジョブ エージェントを作成するには、 Azure エラスティック ジョブ エージェントを作成して使用します。 ただし、このジョブ エージェントを作成する前に、 Azure Elastic Job Agent のドキュメントで説明されているように、アクセス許可、グループ、およびターゲットを設定する必要があります。 また、次のセクションで説明するように、ターゲット データベースにサポート状態テーブルを作成する必要があります。

ジョブ エージェントを作成するには、Azure portal でこのタスクを実行します。 この方法では、エージェントによって使用されるデータベース (エージェント データベースとも呼ばれます) にいくつかのストアド プロシージャが追加 されます。 その後、ターゲット データベースでストアド プロシージャを実行し、完了時に出力をキャプチャするジョブ エージェントを作成できます。

パラメーターを登録して入力を格納するための状態テーブルを作成する

SQL エージェント ジョブでは、入力パラメーターは受け入れられません。 代わりに、ターゲット データベースで、パラメーターを登録し、ストアド プロシージャの呼び出しに使用する入力を格納する状態テーブルを作成します。 すべてのエージェント ジョブ ステップはターゲット データベースに対して実行されますが、ジョブのストアド プロシージャはエージェント データベースに対して実行されます。

状態テーブルを作成するには、次のスキーマを使用します。

CREATE TABLE [dbo].[LongRunningState](
   [jobid] [uniqueidentifier] NOT NULL,
   [rowversion] [timestamp] NULL,
   [parameters] [nvarchar](max) NULL,
   [start] [datetimeoffset](7) NULL,
   [complete] [datetimeoffset](7) NULL,
   [code] [int] NULL,
   [result] [nvarchar](max) NULL,
   CONSTRAINT [PK_LongRunningState] PRIMARY KEY CLUSTERED
      (   [jobid] ASC
      )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
      ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SQL Server Management Studio (SMSS) で結果のテーブルがどのように表示されるかを次に示します。

良好なパフォーマンスを確保し、ジョブ エージェントが関連付けられているレコードを確実に見つけられるように、テーブルは主キーとしてジョブ実行 ID (jobid) を使用します。 必要に応じて、入力パラメーターに個別の列を追加することもできます。 前に説明したスキーマでは、複数のパラメーターをより一般的に処理できますが、 NVARCHAR(MAX) 関数によって計算されるサイズに制限されます。

ストアド プロシージャを実行する最上位のジョブを作成する

実行時間の長いストアド プロシージャを実行するには、エージェント データベースに次の最上位のジョブ エージェントを作成します。

EXEC jobs.sp_add_job 
   @job_name='LongRunningJob',
   @description='Execute Long-Running Stored Proc',
   @enabled = 1

ストアド プロシージャをパラメーター化、実行、および完了するステップをジョブに追加します。 既定では、ジョブ ステップは 12 時間後にタイムアウトします。 ストアド プロシージャにさらに時間が必要な場合、またはプロシージャを前にタイムアウトさせる場合は、 step_timeout_seconds パラメーターを秒単位で指定された別の値に変更できます。 デフォルトでは、ステップには 10 回の再試行が組み込まれており、各再試行の間にバックオフタイムアウトがあります。これをうまく活用することができます。

追加する手順を次に示します。

1. パラメーターが LongRunningState テーブルに表示されるまで待ちます。

   この最初の手順では、パラメーターが LongRunningState テーブルに追加されるのを待ちます。これはジョブの開始直後に行われます。 ジョブ実行 ID (jobid) が LongRunningState テーブルに追加されない場合、手順は失敗するだけです。 既定の再試行またはバックオフ タイムアウトでは、待機が行われます。

   EXEC jobs.sp_add_jobstep
      @job_name='LongRunningJob',
      @step_name= 'Parameterize WaitForIt',
      @step_timeout_seconds = 30,
      @command= N'
         IF NOT EXISTS(SELECT [jobid] FROM [dbo].[LongRunningState]
            WHERE jobid = $(job_execution_id))
            THROW 50400, ''Failed to locate call parameters (Step1)'', 1',
      @credential_name='JobRun',
      @target_group_name='DatabaseGroupLongRunning'
   

2. 状態テーブルからパラメーターにクエリを実行し、ストアド プロシージャに渡します。 この手順では、プロシージャもバックグラウンドで実行されます。

   ストアド プロシージャにパラメーターが必要ない場合は、ストアド プロシージャを直接呼び出します。 それ以外の場合は、 @timespan パラメーターを渡すには、 @callparamsを使用します。これを拡張して、さらに多くのパラメーターを渡すこともできます。

   EXEC jobs.sp_add_jobstep
      @job_name='LongRunningJob',
      @step_name='Execute WaitForIt',
      @command=N'
         DECLARE @timespan char(8)
         DECLARE @callparams NVARCHAR(MAX)
         SELECT @callparams = [parameters] FROM [dbo].[LongRunningState]
            WHERE jobid = $(job_execution_id)
         SET @timespan = @callparams
         EXECUTE [dbo].[WaitForIt] @delay = @timespan', 
      @credential_name='JobRun',
      @target_group_name='DatabaseGroupLongRunning'
   

3. ジョブを完了し、結果を記録します。

   EXEC jobs.sp_add_jobstep
      @job_name='LongRunningJob',
      @step_name='Complete WaitForIt',
      @command=N'
         UPDATE [dbo].[LongRunningState]
            SET [complete] = GETUTCDATE(),
               [code] = 200,
               [result] = ''Success''
            WHERE jobid = $(job_execution_id)',
      @credential_name='JobRun',
      @target_group_name='DatabaseGroupLongRunning'
   

ジョブを開始し、パラメーターを渡す

ジョブを開始するには、[ SQL クエリの実行 ] アクション でパススルー ネイティブ クエリを使用し、ジョブのパラメーターを状態テーブルにすぐにプッシュします。 ターゲット テーブルの jobid 属性に入力を提供するために、Azure Logic Apps は、前のアクションからのテーブル出力を反復処理する For each ループを追加します。 ジョブ実行 ID ごとに、 という名前の動的データ出力を使用して、ジョブのパラメーターを追加してアンパックし、ターゲット ストアド プロシージャに渡すResultSets JobExecutionIdアクションを実行します。

ジョブが完了すると、ジョブは LongRunningState テーブルを更新します。 別のワークフローから、 項目が変更されたときという名前のトリガーを使用して、結果をトリガーできます。 出力が不要な場合、または出力テーブルを監視するトリガーが既にある場合は、この部分をスキップできます。

SQL Server または Azure SQL Managed Instance のジョブ エージェントを作成する

オンプレミスの SQL Server と Azure SQL Managed Instance の場合は、SQL Server エージェントを作成して使用します。 Azure SQL Database のクラウドベースのジョブ エージェントと比較すると、一部の管理の詳細は異なりますが、基本的な手順は変わりません。

次のステップ

• Azure Logic Apps のワークフローから SQL データベースに接続する