处理适用于 Azure 逻辑应用的 SQL 连接器中的存储过程超时

项目
06/02/2023

适用于：Azure 逻辑应用（消耗）

如果逻辑应用处理的结果集很大，导致 SQL 连接器不会同时返回所有结果，或者如果你希望更好地控制结果集的大小和结构，则可以创建一个存储过程，按照想要的方式组织结果。 SQL 连接器提供了许多可以使用 Azure 逻辑应用访问的后端功能，以便你更轻松地自动执行处理 SQL 数据库表的业务任务。

例如，获取或插入多个行时，逻辑应用可以在这些限制中使用 Until loop 来循环访问这些行。但是，当逻辑应用必须处理数千或数百万行内容时，你希望将调用数据库的成本降到最低。有关详细信息，请参阅使用 SQL 连接器处理批量数据。

存储过程执行的超时限制

SQL 连接器的存储过程超时限制是小于 2 分钟。某些存储过程可能需要超过此限制才能完成，从而导致 504 Timeout 错误。有时，出于此目的，这些长时间运行的进程被显式编码为存储过程。由于超时限制，从 Azure 逻辑应用调用这些过程可能会出现问题。尽管 SQL 连接器不本机支持异步模式，但你可以使用 SQL 完成触发器、本机 SQL 传递查询、状态表和服务器端作业来绕过此问题并模拟此模式。对于此任务，可使用适用于 Azure SQL 数据库的 Azure 弹性作业代理。对于本地 SQL Server 和 Azure SQL 托管实例，可使用 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 数据库的 Azure 弹性作业代理。对于 SQL Server 和 Azure SQL 托管实例，可以使用 SQL Server 代理。某些管理详细信息会有所不同，但基本步骤与为 Azure SQL 数据库设置作业代理相同。

适用于 Azure SQL 数据库的作业代理

若要创建为 Azure SQL 数据库运行存储过程的作业，请使用 Azure 弹性作业代理。在 Azure 门户中创建作业代理。此方法将若干存储过程添加到代理使用的数据库（也称为代理数据库）。然后，可以创建在目标数据库中运行存储过程并在完成后捕获输出的作业。

在创建作业之前，需要设置权限、组和目标，如 Azure 弹性作业代理的完整文档所述。还需要在目标数据库中创建支持表，如以下部分所述。

创建用于注册参数和存储输入的状态表

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) 中的生成表如下所示：

Screenshot that shows created state table that stores inputs for stored procedure.

为了确保良好的性能并确保代理作业可以找到关联的记录，该表使用作业执行 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 属性提供输入，逻辑应用添加了一个 For each 循环，该循环从前面的操作中循环访问表输出。对于每个作业执行 ID，运行一个“插入行”操作，该操作使用动态数据输出 ResultSets JobExecutionId 添加作业的参数以解包并传递给目标存储过程。

Screenshot that shows actions to use for starting the job and passing parameters to the stored procedure.

当作业完成时，作业将更新 LongRunningState 表，以便你可以使用“当修改项时”触发器来轻松对结果进行触发。如果不需要输出，或者已有监视输出表的触发器，则可以跳过此部分。

Screenshot that shows the SQL trigger for when an item is modified.

SQL Server 或 Azure SQL 托管实例的作业代理

对于同一场景，可以将 SQL Server 代理用于本地 SQL Server 和 Azure SQL 托管实例。尽管某些管理详细信息会有所不同，但基本步骤与为 Azure SQL 数据库设置作业代理相同。

后续步骤

连接到 SQL Server、Azure SQL 数据库或 Azure SQL 托管实例