Azure Logic Apps での SQL コネクタのストアドプロシージャのタイムアウトを処理する

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

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

ロジックアプリで、SQL コネクタがすべての結果を同時に返さないような大きな結果セットを処理する場合、または結果セットのサイズと構造をより細かく制御する必要がある場合は、結果を必要な方法で整理するストアドプロシージャを作成できます。 SQL データベーステーブルを操作するビジネスタスクをより簡単に自動化できるように、SQL コネクタには、Azure Logic Apps を使用してアクセスできる多数のバックエンド機能が用意されています。

たとえば、複数の行を取得または挿入する場合、ロジックアプリは、こちらの制限の中で Until ループを使用することで、行を反復処理できます。ただし、ロジックアプリは、数千から数百万の行があるレコードセットを処理する場合があります。このような場合は、データベースへの呼び出しコストを最小限にする必要があります。詳細については、SQL コネクタを使用した一括データの処理に関するページを参照してください。

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

SQL コネクタには、2 分未満のストアドプロシージャのタイムアウト上限があります。一部のストアドプロシージャでは、この上限を超えることがあり、504 Timeout エラーが発生します。一部の場合、長期実行されるいくつかのプロセスは、この目的のために明示的にストアドプロシージャとしてコード化されます。このタイムアウト上限が原因で、これらのプロシージャを Azure Logic Apps から呼び出すと問題が発生する可能性があります。 SQL コネクタはネイティブで非同期モードをサポートしていませんが、SQL 補完のトリガー、ネイティブ SQL パススルークエリ、状態テーブル、およびサーバー側ジョブを使用してこの問題を回避し、このモードをシミュレートできます。このタスクでは、Azure SQL Database で Azure エラスティックジョブエージェントを使用できます。オンプレミスの SQL Server と Azure SQL Managed Instance では、SQL Server エージェントを使用できます。

たとえば、次の実行時間の長いストアドプロシージャがあるとします。この処理を完了するには、タイムアウト上限を超えてしまいます。 SQL コネクタを使用してロジックアプリからこのストアドプロシージャを実行すると、結果として HTTP 504 Gateway Timeout エラーが発生します。

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

ストアドプロシージャを直接呼び出すのではなく、 ジョブエージェントを使用して、バックグラウンドでプロシージャを非同期に実行することができます。入力と出力を状態テーブルに格納して、ロジックアプリを使用して操作できるようにすることができます。入力と出力が不要な場合、またはストアドプロシージャのテーブルに既に結果を書き込んでいる場合は、この方法を簡略化できます。

重要

ストアドプロシージャとすべてのジョブがべき等であることを確認します。これは、結果に影響を与えずに複数回実行できることを意味します。非同期処理が失敗した場合、またはタイムアウトになった場合は、ジョブエージェントがステップを再試行することがあります。この場合、ストアドプロシージャは複数回再試行されます。出力が重複しないようにするには、オブジェクトを作成する前に、これらのベストプラクティスと方法を確認してください。

次のセクションでは、Azure SQL Database で Azure エラスティックジョブエージェントを使用する方法を説明します。 SQL Server と Azure SQL Managed Instance では、SQL Server エージェントを使用できます。管理の詳細は異なりますが、基本的な手順は、Azure SQL Database のジョブエージェントの設定と同じです。

Azure SQL Database のジョブエージェント

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

ジョブを作成する前に、Azure エラスティックジョブエージェントに関する完全なドキュメントで説明されているように、アクセス許可、グループ、およびターゲットを設定する必要があります。また、次のセクションで説明するように、対象のデータベースにサポートテーブルを作成する必要があります。

パラメーターの登録と入力の格納を行う状態テーブルを作成する

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

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

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 回の組み込み再試行があり、活用することができます。

追加する手順は次のとおりです。

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'
```
状態テーブルからパラメーターをクエリし、ストアドプロシージャに渡します。さらに、この手順ではバックグラウンドでプロシージャが実行されます。

ストアドプロシージャがパラメーターを必要としない場合は、ストアドプロシージャを直接呼び出します。それ以外の場合、 @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'
```

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

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 属性に入力を提供するために、Logic Apps は前のアクションからのテーブル出力を反復処理する jobid ループを追加します。各ジョブの実行 ID に対して、動的データ出力のを使用する行挿入アクションを実行して、ジョブのパラメーターを追加してアンパックし、対象のストアドプロシージャに渡します。

ジョブを開始し、ストアドプロシージャにパラメーターを渡すために使用するアクションを示すスクリーンショット。

ジョブが完了すると、ジョブによって LongRunningState テーブルが更新されます。これにより、LongRunningStateを使用して結果を簡単にトリガーできるようになります。出力が不要な場合、または出力テーブルを監視するトリガーが既にある場合は、この部分をスキップできます。

項目が変更されたときの SQL トリガーを示すスクリーンショット。

SQL Server のジョブエージェントまたは Azure SQL Managed Instance

同じシナリオで、オンプレミスの SQL Server 用の SQL Server エージェントと Azure SQL Managed Instance を使用できます。管理の詳細は異なりますが、基本的な手順は、Azure SQL Database のジョブエージェントの設定と同じです。

次のステップ

SQL Server、Azure SQL Database、または Azure SQL Managed Instance に接続する

Azure Logic Apps での SQL コネクタのストアド プロシージャのタイムアウトを処理する

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

Azure SQL Database のジョブ エージェント