Управление большими результирующими наборами данных и тайм-аутами SQL при выполнении хранимых процедур, связанных с рабочими процессами для Azure Logic Apps.

Применяется к: Azure Logic Apps (Расход + Стандарт)

Чтобы упростить автоматизацию бизнес-задач, работающих с базами данных SQL, рабочий процесс может использовать операции соединителя SQL Server, которые предоставляют множество внутренних функций для рабочих процессов, используемых в Azure Logic Apps.

В некоторых ситуациях ваш рабочий процесс может потребоваться для обработки больших наборов данных. Эти результирующие наборы могут быть настолько большими, что операции соединителя SQL Server не возвращают все результаты одновременно. В других ситуациях может потребоваться просто больше контроля над размером и структурой результирующих наборов. Чтобы упорядочить результаты таким образом, можно создать хранимую процедуру.

Например, если действие соединителя SQL Server получает или вставляет несколько строк, ваш рабочий процесс может последовательно обрабатывать эти строки с помощью цикла 'До тех пор пока', который работает в пределах этих ограничений. Если рабочий процесс должен обрабатывать тысячи или миллионы строк, необходимо свести к минимуму затраты, вызванные вызовами действий соединителя SQL Server в базу данных SQL. Дополнительные сведения см. в разделе "Обработка массовых данных с помощью соединителя SQL".

В этом руководстве показано, как управлять размером, структурой и временем ожидания при обработке больших результирующих наборов с помощью действий соединителя SQL Server .

Ограничение времени ожидания при выполнении хранимой процедуры

Соединитель SQL Server имеет операцию "Выполнение хранимой процедуры" с ограничением времени ожидания менее двух минут. Некоторые хранимые процедуры могут занять больше времени, чем предполагает это ограничение, что приводит к ошибке 504 Timeout. Иногда длительные процессы кодируются как хранимые процедуры явным образом для этой цели. Из-за ограничения времени ожидания вызов таких процедур из Azure Logic Apps может привести к проблемам.

Операции соединителя SQL Server изначально не поддерживают асинхронный режим. Чтобы обойти это ограничение, имитируйте этот режим с помощью следующих элементов:

• Триггер завершения SQL
• Нативный сквозной SQL-запрос
• Таблица состояний
• Задания на стороне сервера

Например, предположим, что у вас есть следующая длительная хранимая процедура. Чтобы завершить выполнение, процедура превышает ограничение времени ожидания. Если выполнить эту хранимую процедуру из рабочего процесса с помощью действия соединителя SQL Server с именем Execute stored procedure, вы получите ошибку времени ожидания шлюза 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 Azure.

Для локального экземпляра SQL Server и Управляемого экземпляра SQL Azure создайте и используйте агент SQL Server . Основные шаги остаются такими же, как настройка агента заданий для базы данных SQL Azure.

Создание агента задания для Базы данных SQL Azure

Чтобы создать агент задания, который может выполнять хранимые процедуры для базы данных SQL Azure, создайте и используйте агент заданий Эластичных баз данных 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):

Чтобы обеспечить хорошую производительность и убедиться, что агент задания может найти связанную запись, в таблице используется идентификатор выполнения задания (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 таблицу, которая происходит вскоре после запуска задания. Если идентификатор выполнения задания (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 добавляет цикл Для каждого, который проходит по результатам таблицы из предыдущего действия. Для каждого идентификатора выполнения задания выполните действие insert row , которое использует динамические выходные данные с именем ResultSets JobExecutionId , чтобы добавить параметры для задания для распаковки и передачи целевой хранимой процедуры.

По завершении задания задание обновляет таблицу LongRunningState . В другом рабочем процессе можно активировать результат с помощью триггера с именем "При изменении элемента". Если выходные данные отсутствуют или у вас уже есть триггер, отслеживающий выходную таблицу, можно пропустить эту часть.

Создать агента заданий для SQL Server или управляемого экземпляра Azure SQL.

Для локального экземпляра SQL Server и Управляемого экземпляра SQL Azure создайте и используйте агент SQL Server. По сравнению с облачным агентом заданий для Базы данных SQL Azure некоторые сведения об управлении отличаются, но основные шаги остаются неизменными.

Следующий шаг

• Подключение к базам данных SQL из рабочих процессов в Azure Logic Apps