Grote SQL-resultatensets beheren en time-outs beheersen tijdens het uitvoeren van opgeslagen procedures in werkstromen voor Azure Logic Apps.

Van toepassing op: Azure Logic Apps (Verbruik + Standard)

Om eenvoudiger zakelijke taken te automatiseren die met SQL-databases werken, kan uw werkstroom sql Server-connectorbewerkingen gebruiken. Deze biedt veel back-endfuncties voor werkstromen die in Azure Logic Apps kunnen worden gebruikt.

In sommige situaties moet uw werkstroom mogelijk grote resultatensets verwerken. Deze resultatensets kunnen zo groot zijn dat bewerkingen van SQL Server-connector niet alle resultaten tegelijk retourneren. In andere situaties wilt u mogelijk alleen meer controle over de grootte en structuur van uw resultatensets. Als u de gewenste resultaten wilt ordenen, kunt u een opgeslagen procedure maken.

Wanneer een actie van een SQL Server-connector bijvoorbeeld meerdere rijen ophaalt of invoegt, kan uw werkstroom deze rijen herhalen met behulp van een Until-lus die binnen deze limieten werkt. Als uw werkstroom duizenden of miljoenen rijen moet verwerken, wilt u de kosten minimaliseren die het gevolg zijn van actie-aanroepen van SQL Server-connectoren naar de SQL-database. Zie Bulkgegevens verwerken met behulp van de SQL-connector voor meer informatie.

Deze handleiding laat zien hoe u de grootte, structuur en time-outs kunt beheren bij het verwerken van grote resultatensets met behulp van de acties van de SQL Server-connector .

Time-outlimiet voor uitvoering van opgeslagen procedures

De SQL Server-connector heeft een opgeslagen procedureactie Uitvoeren met een time-outlimiet die minder dan twee minuten duurt. Sommige opgeslagen procedures kunnen langer duren dan deze limiet, wat een 504 Timeout fout veroorzaakt. Soms worden langlopende processen expliciet gecodeerd als opgeslagen procedures voor dit doel. Vanwege de time-outlimiet kan het aanroepen van dergelijke procedures vanuit Azure Logic Apps problemen veroorzaken.

De SQL Server-connectorbewerkingen bieden geen systeemeigen ondersteuning voor een asynchrone modus. Als u deze beperking wilt omzeilen, simuleert u deze modus met behulp van de volgende items:

• SQL-voltooiingstrigger
• Ingebouwde SQL-pass-through-query
• Statustabel
• Taken aan serverzijde

Stel dat u de volgende langlopende opgeslagen procedure hebt. Om de uitvoering te voltooien, overschrijdt de procedure de time-outlimiet. Als u deze opgeslagen procedure uitvoert vanuit een werkstroom met behulp van de actie SQL Server-connector met de naam Opgeslagen procedure uitvoeren, krijgt u de time-outfout van de HTTP 504-gateway .

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 openen en beheren via uw werkstroom. 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 aanpak vereenvoudigen.

Als u de procedure asynchroon op de achtergrond wilt uitvoeren met taakagent voor SQL Server in de cloud, volgt u de stappen voor het maken en gebruiken van de Azure Elastic Job Agent voor Azure SQL Database.

Voor on-premises SQL Server en Azure SQL Managed Instance maakt en gebruikt u in plaats daarvan de SQL Server Agent . De fundamentele stappen blijven hetzelfde als het instellen van een taakagent voor Azure SQL Database.

Taakagent maken voor Azure SQL Database

Als u een taakagent wilt maken die opgeslagen procedures voor Azure SQL Database kan uitvoeren, maakt en gebruikt u de Elastische-taakagent van Azure. Voordat u deze taakagent kunt maken, moet u echter de machtigingen, groepen en doelen instellen, zoals beschreven in de documentatie van azure Elastic Job Agent. U moet ook een ondersteunende statustabel maken in de doeldatabase, zoals beschreven in de volgende secties.

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

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

SQL Agent-jobs 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 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 taakagent de bijbehorende record kan vinden, gebruikt de tabel de taakuitvoerings-id (jobid) als primaire sleutel. Desgewenst kunt u ook afzonderlijke kolommen toevoegen voor de invoerparameters. Het eerder beschreven schema kan over het algemeen meerdere parameters verwerken, maar is beperkt tot de grootte die door de NVARCHAR(MAX) functie wordt berekend.

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 stappen toe aan de taak waarmee de opgeslagen procedure wordt geparametereerd, uitgevoerd en voltooid. 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 afloopt, kunt u de step_timeout_seconds parameter wijzigen in een andere waarde die in seconden is opgegeven. Standaard bevat 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 tot de parameters in de LongRunningState tabel worden weergegeven.

   In deze eerste stap wordt gewacht totdat de parameters worden toegevoegd aan LongRunningState de tabel. Dit gebeurt kort nadat de taak is gestart. Als de taakuitvoerings-id (jobid) niet wordt toegevoegd aan de LongRunningState tabel, mislukt de stap alleen. De standaardtime-out voor opnieuw proberen of uitstel zorgt voor 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 meer 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'
   

De taak starten en de parameters doorgeven

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. Als u invoer wilt opgeven voor het jobid kenmerk in de doeltabel, voegt Azure Logic Apps een For-lus toe die door de tabeluitvoer van de vorige actie wordt herhaald. Voer voor elke taakuitvoerings-id een rijactie Invoegen uit die gebruikmaakt van de dynamische gegevensuitvoer met de naam ResultSets JobExecutionId om de parameters voor de taak toe te voegen om de opgeslagen doelprocedure uit te pakken en door te geven.

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

Taakagent maken voor SQL Server of Azure SQL Managed Instance

Voor on-premises SQL Server en Azure SQL Managed Instance maakt en gebruikt u de SQL Server Agent. Vergeleken met de cloudtaakagent voor Azure SQL Database verschillen sommige beheerdetails, maar de fundamentele stappen blijven hetzelfde.

Volgende stap

• Verbinding maken met SQL-databases vanuit werkstromen in Azure Logic Apps