Time-outs van opgeslagen procedures verwerken in de SQL-connector voor Azure Logic Apps

Van toepassing op: Azure Logic Apps (verbruik)

Wanneer uw logische app werkt met resultatensets die zo groot zijn dat de SQL-connector niet alle resultaten tegelijk retourneert, of als u meer controle wilt over de grootte en structuur van uw resultatensets, kunt u een opgeslagen procedure maken die de resultaten op de gewenste manier ordent. De SQL-connector biedt veel back-endfuncties waartoe u toegang hebt met behulp van Azure Logic Apps , zodat u zakelijke taken die werken met SQL-databasetabellen eenvoudiger kunt automatiseren.

Wanneer u bijvoorbeeld meerdere rijen ophaalt of invoegt, kan uw logische app deze rijen doorlopen met behulp van een until-lus binnen deze limieten. Wanneer uw logische app echter met duizenden of miljoenen rijen moet werken, wilt u de kosten van aanroepen naar de database minimaliseren. Zie Bulkgegevens verwerken met behulp van de SQL-connector voor meer informatie.

Time-outlimiet voor uitvoering van opgeslagen procedures

De SQL-connector heeft een time-outlimiet voor opgeslagen procedures van minder dan 2 minuten. Sommige opgeslagen procedures kunnen langer duren dan deze limiet, waardoor een 504 Timeout fout ontstaat. Soms worden deze langlopende processen expliciet voor dit doel gecodeerd als opgeslagen procedures. Vanwege de time-outlimiet kan het aanroepen van deze procedures vanuit Azure Logic Apps problemen veroorzaken. Hoewel de SQL-connector geen systeemeigen ondersteuning biedt voor een asynchrone modus, kunt u dit probleem omzeilen en deze modus simuleren met behulp van een SQL-voltooiingstrigger, een systeemeigen SQL-passthrough-query, een statustabel en taken aan de serverzijde. Voor deze taak kunt u de Azure Elastic Job Agent voor Azure SQL Database gebruiken. Voor SQL Server on-premises en Azure SQL Managed Instance kunt u de SQL Server Agent gebruiken.

Stel dat u de volgende langlopende opgeslagen procedure hebt, die langer duurt dan de time-outlimiet om de uitvoering te voltooien. Als u deze opgeslagen procedure uitvoert vanuit een logische app met behulp van de SQL-connector, krijgt u een HTTP 504 Gateway Timeout foutmelding.

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

In plaats van de opgeslagen procedure rechtstreeks aan te roepen, kunt u de procedure asynchroon op de achtergrond uitvoeren met behulp van een taakagent. U kunt de invoer en uitvoer opslaan in een statustabel die u vervolgens kunt gebruiken via uw logische app. Als u de invoer en uitvoer niet nodig hebt of als u de resultaten al naar een tabel in de opgeslagen procedure schrijft, kunt u deze benadering vereenvoudigen.

Belangrijk

Zorg ervoor dat uw opgeslagen procedure en alle taken idempotent zijn, wat betekent dat ze meerdere keren kunnen worden uitgevoerd zonder dat dit van invloed is op de resultaten. Als de asynchrone verwerking mislukt of een time-out optreedt, kan de taakagent de stap, en dus de opgeslagen procedure, meerdere keren opnieuw proberen. Om te voorkomen dat uitvoer wordt gedupliceerd, raadpleegt u deze aanbevolen procedures en benaderingen voordat u objecten maakt.

In de volgende sectie wordt beschreven hoe u de Azure Elastic Job Agent kunt gebruiken voor Azure SQL Database. Voor SQL Server en Azure SQL Managed Instance kunt u de SQL Server Agent gebruiken. Sommige beheerdetails verschillen, maar de basisstappen blijven hetzelfde als het instellen van een taakagent voor Azure SQL Database.

Taakagent voor Azure SQL Database

Als u een taak wilt maken waarmee de opgeslagen procedure voor Azure SQL Database kan worden uitgevoerd, gebruikt u de Azure Elastic Job Agent. Maak uw taakagent in de Azure Portal. Met deze methode worden verschillende opgeslagen procedures toegevoegd aan de database die wordt gebruikt door de agent, ook wel de agentdatabase genoemd. Vervolgens kunt u een taak maken waarmee de opgeslagen procedure in de doeldatabase wordt uitgevoerd en de uitvoer wordt vastgelegd wanneer u klaar bent.

Voordat u de taak kunt maken, moet u machtigingen, groepen en doelen instellen, zoals beschreven in de volledige documentatie voor de Azure Elastic Job Agent. U moet ook een ondersteunende tabel maken in de doeldatabase, zoals beschreven in de volgende secties.

Statustabel maken voor het registreren van parameters en het opslaan van invoer

SQL Agent-taken accepteren geen invoerparameters. Maak in plaats daarvan in de doeldatabase een statustabel waarin u de parameters registreert en de invoer opslaat die moet worden gebruikt voor het aanroepen van uw opgeslagen procedures. Alle stappen van de agenttaak worden uitgevoerd op de doeldatabase, maar de opgeslagen procedures van de taak worden uitgevoerd op basis van de agentdatabase.

Gebruik dit schema om de statustabel te maken:

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]

De resulterende tabel ziet er als volgt uit in SQL Server Management Studio (SMSS):

Om goede prestaties te garanderen en ervoor te zorgen dat de agenttaak de bijbehorende record kan vinden, gebruikt de tabel de taakuitvoerings-id (jobid) als primaire sleutel. Als u wilt, kunt u ook afzonderlijke kolommen toevoegen voor de invoerparameters. Het eerder beschreven schema kan meer in het algemeen meerdere parameters verwerken, maar is beperkt tot de grootte die wordt berekend door NVARCHAR(MAX).

Een taak op het hoogste niveau maken om de opgeslagen procedure uit te voeren

Als u de langlopende opgeslagen procedure wilt uitvoeren, maakt u deze taakagent op het hoogste niveau in de agentdatabase:

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

Voeg nu stappen toe aan de taak die de opgeslagen procedure parameteriseren, uitvoeren en voltooien. Standaard treedt er na 12 uur een time-out op voor een taakstap. Als uw opgeslagen procedure meer tijd nodig heeft of als u wilt dat de procedure eerder een time-out krijgt, kunt u de step_timeout_seconds parameter wijzigen in een andere waarde die in seconden is opgegeven. Standaard heeft een stap 10 ingebouwde nieuwe pogingen met een time-out voor uitstel tussen elke nieuwe poging, die u in uw voordeel kunt gebruiken.

Dit zijn de stappen om toe te voegen:

1. Wacht totdat de parameters in de LongRunningState tabel worden weergegeven.

   In deze eerste stap wordt gewacht totdat de parameters in LongRunningState de tabel worden toegevoegd. Dit gebeurt kort nadat de taak is gestart. Als de taakuitvoerings-id (jobid) niet wordt toegevoegd aan de LongRunningState tabel, mislukt de stap alleen en de standaardtime-out voor opnieuw proberen of uitstel doet het wachten:

   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. Voer een query uit op de parameters uit de statustabel en geef deze door aan de opgeslagen procedure. Met deze stap wordt de procedure ook op de achtergrond uitgevoerd.

   Als uw opgeslagen procedure geen parameters nodig heeft, roept u de opgeslagen procedure rechtstreeks aan. Als u de @timespan parameter wilt doorgeven, gebruikt u de @callparams, die u ook kunt uitbreiden om extra parameters door te geven.

   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. Voltooi de taak en noteer de resultaten.

   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'
   

Start de taak en geef de parameters door

Als u de taak wilt starten, gebruikt u een systeemeigen passthrough-query met de actie Een SQL-query uitvoeren en pusht u de parameters van de taak onmiddellijk naar de statustabel. Logic Apps voegt de lus For each toe die de tabeluitvoer van de voorgaande actie doorloopt om invoer te jobid bieden voor het kenmerk in de doeltabeltabel. Voer voor elke taakuitvoerings-id de actie Rij invoegen uit die gebruikmaakt van de dynamische gegevensuitvoer, ResultSets JobExecutionId, om de parameters toe te voegen voor de taak die moet worden uitgepakt en doorgegeven aan de opgeslagen doelprocedure.

Wanneer de taak is voltooid, wordt de LongRunningState tabel bijgewerkt, zodat u het resultaat eenvoudig kunt activeren met behulp van de trigger Wanneer een item wordt gewijzigd. Als u de uitvoer niet nodig hebt of als u al een trigger hebt waarmee een uitvoertabel wordt bewaakt, kunt u dit gedeelte overslaan.

Taakagent voor SQL Server of Azure SQL Managed Instance

Voor hetzelfde scenario kunt u de SQL Server Agent gebruiken voor SQL Server on-premises en Azure SQL Managed Instance. Hoewel sommige beheerdetails verschillen, blijven de basisstappen hetzelfde als het instellen van een taakagent voor Azure SQL Database.

Volgende stappen

Verbinding maken met SQL Server, Azure SQL Database of Azure SQL Managed Instance