Обработка длительного времени ожидания для хранимых процедур в соединителе SQL для Azure Logic Apps

Статья
01/04/2024

Область применения: Azure Logic Apps (потребление)

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

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

Предельное время ожидания выполнения хранимой процедуры

В соединителе SQL для хранимой процедуры ограничено время ожидания: оно должно быть меньше 2 минут. Некоторые хранимые процедуры могут выполняться дольше, что приведет к ошибке 504 Timeout. Иногда эти длительно выполняемые процессы кодируются в качестве хранимых процедур конкретно для этой цели. Из-за ограничения времени ожидания вызов этих процедур из Azure Logic Apps может привести к возникновению проблем. Хотя соединитель SQL изначально не поддерживает асинхронный режим, эту проблему можно обойти и имитировать этот режим с помощью триггера завершения SQL, собственного запроса к серверу SQL, таблицы состояний и заданий на стороне сервера. Для этой задачи можно использовать агент заданий обработки эластичных баз данных Azure для Базы данных SQL Azure. Для локального экземпляра SQL Server и Управляемого экземпляра SQL Azure можно использовать агент 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 и Управляемого экземпляра SQL Azure можно использовать агент SQL Server. Некоторые детали управления будут отличаться, но основные шаги остаются теми же, что и при настройке агента заданий для базы данных SQL Azure.

Агент заданий для базы данных SQL Azure

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

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

Создание таблицы состояний для регистрации параметров и хранения входных данных

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

Чтобы создать таблицу состояний, используйте следующую схему:

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 попыток повторного выполнения с тайм-аутом задержки между попытками, который можно использовать с пользой для себя.

Ниже приведены этапы для добавления.

Дождитесь появления параметров в таблице 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'
```
Запросите параметры из таблицы состояний и передайте их в хранимую процедуру. На этом этапе процедура выполняется также в фоновом режиме.

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

Снимок экрана, на котором показаны действия, которые необходимо выполнить для запуска задания и передачи параметров в хранимую процедуру.

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

Снимок экрана, на котором показан триггер SQL для изменения элемента.

Агент задания для SQL Server или Управляемого экземпляра SQL Azure

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

Дальнейшие действия

Подключение к SQL Server, Базе данных SQL Azure или Управляемому экземпляру SQL Azure

Share via