Controlar grandes conjuntos de resultados do SQL e tempos limite durante os procedimentos armazenados nos fluxos de trabalho dos Aplicativos Lógicos do Azure

Aplica-se a: Aplicativos Lógicos do Azure (Consumo + Standard)

Para automatizar mais facilmente tarefas comerciais que funcionam com bancos de dados SQL, seu fluxo de trabalho pode usar operações de conector do SQL Server, que fornece muitos recursos de back-end para fluxos de trabalho a serem usados nos Aplicativos Lógicos do Azure.

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, talvez você queira apenas mais controle sobre o tamanho e a estrutura dos conjuntos de resultados. Para organizar os resultados da maneira desejada, você pode criar um procedimento armazenado.

Por exemplo, quando uma ação do SQL Server obtém ou insere várias linhas, seu fluxo de trabalho pode iterar por essas linhas usando um loop Until nesses limites. Se seu fluxo de trabalho precisar lidar com milhares ou milhões de linhas, você quer 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 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 duração são definidos como procedimentos armazenados explicitamente para essa finalidade. Devido ao limite de tempo, chamar esses procedimentos dos Azure Logic Apps pode criar problemas.

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

• Gatilho de conclusão do SQL
• Consulta de passagem do SQL nativo
• Tabela de estado
• Trabalhos do lado do 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 de um fluxo de trabalho usando a ação do conector do SQL Server chamada Executar procedimento armazenado, você receberá o erro de Tempo limite do Gateway HTTP 504 .

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 executar o procedimento 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 por meio do fluxo de trabalho. Caso você não precise das entradas e saídas ou já esteja registrando os resultados em uma tabela no procedimento armazenado, pode simplificar este método.

Para executar o procedimento de forma assíncrona em segundo plano com o agente de trabalho do SQL Server baseado em nuvem, siga as etapas para criar e usar o Agente de Trabalho Elástico do Azure para o Banco de Dados SQL do Azure.

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

Criar um 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 Agente de Trabalho Elástico do Azure. 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 concluída.

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

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 em que você registre os parâmetros e armazene as entradas a serem usadas para chamar os procedimentos armazenados. Todas as etapas de 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 estado, 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 SMSS (SQL Server Management Studio):

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

Criar um trabalho de nível superior para executar o procedimento armazenado

Para executar o procedimento armazenado de execução longa, crie este agente de trabalho de nível superior no banco 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 parametrize, execute e conclua 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 desejar que o procedimento tenha um tempo limite mais cedo, você poderá alterar o step_timeout_seconds parâmetro para outro valor especificado em segundos. Por padrão, uma etapa tem 10 repetições internas com um tempo limite de retirada entre cada repetição, que você pode usar a seu favor.

Estas são as etapas a serem adicionadas:

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

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

   Se o seu procedimento armazenado não precisar de parâmetros, chame o procedimento armazenado diretamente. 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'
   

Iniciar o trabalho e passar 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 informações ao atributo jobid na tabela de destino, os Aplicativos Lógicos do Azure adicionam um loop Para cada que itera através da 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 desempacotar e passar para o procedimento armazenado de destino.

Quando o trabalho for concluído, o trabalho atualizará a tabela LongRunningState. De um fluxo de trabalho diferente, você pode acionar o evento 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, poderá ignorar essa parte.

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

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

Próxima etapa

• Conectar-se a bancos de dados SQL de fluxos de trabalho no Azure Logic Apps