Processar tempos limite de procedimentos armazenados no conector SQL para o Azure Logic Apps

Aplica-se a: Azure Logic Apps (Consumo)

Quando a aplicação lógica funciona com conjuntos de resultados tão grandes que o conector SQL não devolve todos os resultados ao mesmo tempo ou se quiser ter mais controlo sobre o tamanho e a estrutura dos conjuntos de resultados, pode criar um procedimento armazenado que organize os resultados da forma que pretende. O conector SQL fornece muitas funcionalidades de back-end às quais pode aceder com o Azure Logic Apps para que possa automatizar mais facilmente tarefas empresariais que funcionam com tabelas de bases de dados SQL.

Por exemplo, ao obter ou inserir várias linhas, a sua aplicação lógica pode iterar através destas linhas através de um ciclo Until dentro destes limites. No entanto, quando a sua aplicação lógica tem de funcionar com milhares ou milhões de linhas, quer minimizar os custos resultantes das chamadas para a base de dados. Para obter mais informações, veja Processar dados em massa com o conector SQL.

Limite de tempo limite na execução de procedimentos armazenados

O conector SQL tem um limite de tempo limite de procedimento armazenado inferior a 2 minutos. Alguns procedimentos armazenados podem demorar mais tempo do que este limite a serem concluídos, o que causa um 504 Timeout erro. Por vezes, estes processos de execução prolongada são codificados como procedimentos armazenados explicitamente para esta finalidade. Devido ao limite de tempo limite, chamar estes procedimentos do Azure Logic Apps pode criar problemas. Embora o conector SQL não suporte nativamente um modo assíncrono, pode contornar este problema e simular este modo com um acionador de conclusão SQL, uma consulta pass-through sql nativa, uma tabela de estado e tarefas do lado do servidor. Para esta tarefa, pode utilizar o Agente de Tarefa Elástica do Azure para SQL do Azure Base de Dados. Para SQL Server no local e Azure SQL Managed Instance, pode utilizar o SQL Server Agent.

Por exemplo, suponha que tem o seguinte procedimento armazenado de execução prolongada, que demora mais tempo do que o limite de tempo limite para concluir a execução. Se executar este procedimento armazenado a partir de uma aplicação lógica com o conector SQL, obtém um HTTP 504 Gateway Timeout erro como resultado.

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

Em vez de chamar diretamente o procedimento armazenado, pode executar de forma assíncrona o procedimento em segundo plano com um agente de trabalho. Pode armazenar as entradas e saídas numa tabela de estado com a qual pode interagir através da sua aplicação lógica. Se não precisar das entradas e saídas ou se já estiver a escrever os resultados numa tabela no procedimento armazenado, pode simplificar esta abordagem.

Importante

Certifique-se de que o procedimento armazenado e todas as tarefas são idempotentes, o que significa que podem ser executados várias vezes sem afetar os resultados. Se o processamento assíncrono falhar ou exceder o limite de tempo, o agente de tarefas poderá repetir o passo e, por conseguinte, o procedimento armazenado, várias vezes. Para evitar duplicar a saída, antes de criar objetos, reveja estas melhores práticas e abordagens.

A secção seguinte descreve como pode utilizar o Agente de Tarefas Elásticas do Azure para a Base de Dados SQL do Azure. Para SQL Server e Azure SQL Managed Instance, pode utilizar o SQL Server Agent. Alguns detalhes de gestão serão diferentes, mas os passos fundamentais permanecem os mesmos que configurar um agente de tarefas para SQL do Azure Base de Dados.

Agente de tarefas da Base de Dados SQL do Azure

Para criar uma tarefa que possa executar o procedimento armazenado para SQL do Azure Base de Dados, utilize o Agente de Tarefa Elástica do Azure. Crie o agente de tarefas no portal do Azure. Esta abordagem irá adicionar vários procedimentos armazenados à base de dados utilizada pelo agente, também conhecida como base de dados do agente. Em seguida, pode criar uma tarefa que execute o procedimento armazenado na base de dados de destino e capture a saída quando terminar.

Antes de poder criar a tarefa, tem de configurar permissões, grupos e destinos, conforme descrito pela documentação completa do Agente de Tarefas Elásticas do Azure. Também tem de criar uma tabela de suporte na base de dados de destino, conforme descrito nas secções seguintes.

Criar tabela de estado para registar parâmetros e armazenar entradas

As Tarefas do SQL Agent não aceitam parâmetros de entrada. Em vez disso, na base de dados de destino, crie uma tabela de estado onde registe os parâmetros e armazene as entradas a utilizar para chamar os procedimentos armazenados. Todos os passos da tarefa do agente são executados na base de dados de destino, mas os procedimentos armazenados da tarefa são executados na base de dados do agente.

Para criar a tabela de estado, utilize este esquema:

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]

Eis o aspeto da tabela resultante no SQL Server Management Studio (SMSS):

Para garantir um bom desempenho e garantir que a tarefa do agente consegue encontrar o registo associado, a tabela utiliza o ID de execução da tarefa (jobid) como chave primária. Se quiser, também pode adicionar colunas individuais para os parâmetros de entrada. O esquema descrito anteriormente pode processar vários parâmetros de forma mais geral, mas está limitado ao tamanho calculado por NVARCHAR(MAX).

Criar uma tarefa de nível superior para executar o procedimento armazenado

Para executar o procedimento armazenado de execução prolongada, crie este agente de trabalho de nível superior na base de dados do agente:

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

Agora, adicione passos à tarefa que parametriza, executa e conclui o procedimento armazenado. Por predefinição, um passo de trabalho excede o limite de tempo após 12 horas. Se o procedimento armazenado precisar de mais tempo ou se quiser que o procedimento exceda o tempo limite mais cedo, pode alterar o step_timeout_seconds parâmetro para outro valor especificado em segundos. Por predefinição, um passo tem 10 repetições incorporadas com um tempo limite de trás entre cada repetição, que pode utilizar a seu favor.

Eis os passos a adicionar:

1. Aguarde que os parâmetros apareçam na LongRunningState tabela.

   Este primeiro passo aguarda que os parâmetros são adicionados à LongRunningState tabela, o que acontece logo após o início da tarefa. Se o ID de execução da tarefa (jobid) não for adicionado à LongRunningState tabela, o passo apenas falhará e o tempo limite de repetição ou de recuo predefinido faz a espera:

   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. Consulte os parâmetros da tabela de estado e transmita-os para o procedimento armazenado. Este passo também executa o procedimento em segundo plano.

   Se o procedimento armazenado não precisar de parâmetros, basta chamar diretamente o procedimento armazenado. Caso contrário, para transmitir o @timespan parâmetro, utilize o @callparams, que também pode expandir para transmitir parâmetros adicionais.

   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. Conclua a tarefa e registe os resultados.

   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'
   

Iniciar a tarefa e transmitir os parâmetros

Para iniciar a tarefa, utilize uma consulta nativa pass-through com a ação Executar uma consulta SQL e envie imediatamente os parâmetros da tarefa para a tabela de estado. Para fornecer entrada ao jobid atributo na tabela de destino, o Logic Apps adiciona um ciclo Para cada que itera através da saída da tabela da ação anterior. Para cada ID de execução de tarefa, execute uma ação Inserir linha que utiliza a saída de dados dinâmicos, ResultSets JobExecutionId, para adicionar os parâmetros para a tarefa descompactar e passar para o procedimento armazenado de destino.

Quando a tarefa for concluída, a tarefa atualiza a LongRunningState tabela para que possa acionar facilmente o resultado com o acionador Quando um item é modificado. Se não precisar da saída ou se já tiver um acionador que monitorize uma tabela de saída, pode ignorar esta parte.

Agente de tarefas para SQL Server ou Azure SQL Managed Instance

Para o mesmo cenário, pode utilizar o SQL Server Agent para SQL Server no local e Azure SQL Managed Instance. Embora alguns detalhes de gestão sejam diferentes, os passos fundamentais permanecem os mesmos que a configuração de um agente de tarefas para SQL do Azure Base de Dados.

Passos seguintes

Ligar a SQL Server, Base de Dados SQL do Azure ou Azure SQL Managed Instance