Controle grandes conjuntos de resultados SQL e tempos limite durante procedimentos armazenados em fluxos de trabalho para Aplicativos Lógicos do Azure

Aplica-se a: Azure Logic Apps (Consumo e Standard)

Para automatizar mais facilmente tarefas empresariais que funcionam com bases de dados SQL, o seu fluxo de trabalho pode utilizar operações do conector SQL Server, que disponibiliza muitos recursos de backend para os fluxos de trabalho utilizarem nos Azure Logic Apps.

Em algumas situações, seu fluxo de trabalho pode ter que lidar com grandes conjuntos de resultados. Esses conjuntos de resultados podem ser tão grandes que as operações do conector do SQL Server não retornam todos os resultados ao mesmo tempo. Em outras situações, você pode querer apenas mais controle sobre o tamanho e a estrutura de seus conjuntos de resultados. Para organizar os resultados da maneira desejada, você pode criar um procedimento armazenado.

Por exemplo, quando uma ação de conector do SQL Server obtém ou insere várias linhas, seu fluxo de trabalho pode iterar através dessas linhas usando um loop Until que funciona dentro desses limites. Se o seu fluxo de trabalho precisar lidar com milhares ou milhões de linhas, procure minimizar os custos resultantes das chamadas de ação do conector do SQL Server para o banco de dados SQL. Para obter mais informações, consulte Manipular dados em massa usando o conector SQL.

Este guia mostra como controlar o tamanho, a estrutura e os tempos limite ao processar grandes conjuntos de resultados usando as ações do conector do SQL Server .

Limite de tempo limite na execução do procedimento armazenado

O conector do SQL Server tem uma ação Executar procedimento armazenado com um limite de tempo inferior a dois minutos. Alguns procedimentos armazenados podem levar mais tempo do que esse limite para serem concluídos, o que causa um erro de tempo limite 504 . Às vezes, processos de longa execução são codificados como procedimentos armazenados explicitamente para essa finalidade. Devido ao limite de tempo, invocar esses procedimentos dos Azure Logic Apps pode criar problemas.

As operações do conector do SQL Server não oferecem suporte nativo a um modo assíncrono. Para contornar essa limitação, simule esse modo usando os seguintes itens:

• Gatilho de conclusão SQL
• Consulta de passagem SQL nativa
• Tabela de estados
• Trabalhos no servidor

Por exemplo, suponha que você tenha o seguinte procedimento armazenado de longa execução. Para concluir a execução, o procedimento excede o limite de tempo limite. Se você executar esse procedimento armazenado a partir de um fluxo de trabalho usando a ação do conector do SQL Server chamada Executar procedimento armazenado, obterá o erro HTTP 504 Gateway Timeout .

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, você pode executá-lo de forma assíncrona em segundo plano usando um agente de trabalho. Você pode armazenar as entradas e saídas em uma tabela de estado que você pode acessar e gerenciar através do seu fluxo de trabalho. Se você não precisar das entradas e saídas, ou se já estiver gravando os resultados em uma tabela no procedimento armazenado, poderá simplificar essa abordagem.

Para executar de forma assíncrona o procedimento em segundo plano com o agente de trabalho para SQL Server baseado em nuvem, siga as etapas para criar e usar o Azure Elastic Job Agent for Azure SQL Database.

Para SQL Server local e Instância Gerenciada SQL do Azure, crie e use o SQL Server Agent em vez disso. As etapas fundamentais permanecem as mesmas que configurar um agente de trabalho para o Banco de Dados SQL do Azure.

Criar agente de trabalho para o Banco de Dados SQL do Azure

Para criar um agente de trabalho que possa executar procedimentos armazenados para o Banco de Dados SQL do Azure, crie e use o Agente de Trabalho Elástico do Azure. No entanto, antes de criar esse agente de trabalho, você deve configurar as permissões, grupos e destinos conforme descrito na documentação do Azure Elastic Job Agent. Você também deve criar uma tabela de estado de suporte no banco de dados de destino, conforme descrito nas seções a seguir.

Para criar o agente de trabalho, execute essa tarefa no portal do Azure. Essa abordagem adiciona vários procedimentos armazenados ao banco de dados usado pelo agente, também conhecido como banco de dados do agente. Em seguida, você pode criar um agente de trabalho que executa o procedimento armazenado no banco de dados de destino e captura a saída quando terminar.

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

Os trabalhos do SQL Agent não aceitam parâmetros de entrada. Em vez disso, no banco de dados de destino, crie uma tabela de estado onde você registre os parâmetros e armazene as entradas a serem usadas para chamar seus procedimentos armazenados. Todas as etapas do trabalho do agente são executadas no banco de dados de destino, mas os procedimentos armazenados do trabalho são executados no banco de dados do agente.

Para criar a tabela de estados, use 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]

Veja a aparência da tabela resultante no SQL Server Management Studio (SMSS):

Para garantir um bom desempenho e garantir que o agente de trabalho possa localizar o registro associado, a tabela usa o ID de execução do trabalho (jobid) como chave primária. Se desejar, você também pode adicionar colunas individuais para os parâmetros de entrada. O esquema descrito anteriormente pode mais geralmente lidar com vários parâmetros, mas é limitado ao tamanho calculado pela NVARCHAR(MAX) função.

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

Para executar o procedimento armazenado de longa duração, 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

Adicione etapas ao trabalho que parametrizam, executam e concluem o procedimento armazenado. Por padrão, uma etapa de trabalho expira após 12 horas. Se o procedimento armazenado precisar de mais tempo, ou se você quiser que o procedimento atinja o tempo limite mais cedo, você pode alterar o step_timeout_seconds parâmetro para outro valor especificado em segundos. Por padrão, uma etapa tem 10 tentativas internas com um tempo limite de retorno entre cada nova tentativa, que você pode usar a seu favor.

Aqui estão as etapas para adicionar:

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

   Esta primeira etapa aguarda que os parâmetros sejam adicionados na LongRunningState tabela, o que acontece logo após o início do trabalho. Se o ID de execução do trabalho (jobid) não for adicionado à LongRunningState tabela, a etapa simplesmente falhará. O tempo limite de repetição ou backoff padrão é responsável pela 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 estados e passe-os para o procedimento armazenado. Esta etapa também executa o procedimento em segundo plano.

   Se o procedimento armazenado não precisar de parâmetros, execute diretamente o procedimento armazenado. Caso contrário, para passar o @timespan parâmetro, use o @callparams, que você também pode estender para passar mais parâmetros.

   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 o trabalho e registre 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'
   

Inicie o trabalho e passe os parâmetros

Para iniciar o trabalho, use uma consulta nativa de passagem com a ação Executar uma consulta SQL e envie imediatamente os parâmetros do trabalho para a tabela de estados. Para fornecer entrada para o jobid atributo na tabela de destino, o Azure Logic Apps adiciona um loop Para cada que percorre a saída da tabela da ação anterior. Para cada ID de execução de trabalho, execute uma ação Inserir linha que usa a saída de dados dinâmicos nomeada ResultSets JobExecutionId para adicionar os parâmetros para o trabalho a ser descompactado e passado para o procedimento armazenado de destino.

Quando o trabalho é concluído, o trabalho atualiza a tabela LongRunningState. A partir de um fluxo de trabalho diferente, você pode acionar o resultado usando o gatilho chamado Quando um item é modificado. Se você não precisar da saída ou se já tiver um gatilho que monitore uma tabela de saída, ignore esta parte.

Criar agente de trabalho para SQL Server ou Instância Gerenciada SQL do Azure

Para SQL Server local e Instância Gerenciada SQL do Azure, crie e use o SQL Server Agent. Em comparação com o agente de trabalho baseado em nuvem para o Banco de Dados SQL do Azure, alguns detalhes de gerenciamento diferem, mas as etapas fundamentais permanecem as mesmas.

Próximo passo

• Conectar-se a bancos de dados SQL a partir de fluxos de trabalho nos Aplicativos Lógicos do Azure