Správa velkých sad výsledků SQL a limitů času během uložených procedur v rámci pracovních toků pro Azure Logic Apps

Platí pro: Azure Logic Apps (Consumption + Standard)

Pro snadnější automatizaci obchodních úloh, které pracují s databázemi SQL, může pracovní postup používat operace konektoru SQL Serveru, které poskytují mnoho back-endových funkcí pro pracovní postupy, které se dají použít v Azure Logic Apps.

V některých situacích může pracovní postup zpracovávat velké sady výsledků. Tyto sady výsledků můžou být tak velké, aby operace konektoru SQL Serveru nevracely všechny výsledky najednou. V jiných situacích můžete chtít mít větší kontrolu nad velikostí a strukturou sad výsledků. Pokud chcete výsledky uspořádat požadovaným způsobem, můžete vytvořit uloženou proceduru.

Například když akce konektoru SQL Serveru získá nebo vloží více řádků, může pracovní postup vašeho systému procházet tyto řádky pomocí smyčky Until, která funguje v rámci těchto limitů. Pokud váš pracovní postup musí zpracovávat tisíce nebo miliony řádků, chcete minimalizovat náklady vyplývající z volání akcí konektoru SQL Serveru do databáze SQL. Další informace najdete v tématu Zpracování hromadných dat pomocí konektoru SQL.

Tento průvodce ukazuje, jak řídit velikost, strukturu a časové limity při zpracování velkých sad výsledků pomocí akcí konektoru SQL Serveru .

Omezení časového limitu při spouštění uložených procedur

Konektor SQL Serveru má akci Spustit uloženou proceduru s časovým limitem, který je kratší než dvě minuty. Dokončení některých uložených procedur může trvat déle, než je tento limit, což způsobí chybu 504 časového limitu . V některých případech jsou dlouhotrvající procesy kódované jako uložené procedury explicitně pro tento účel. Kvůli časovému limitu může volání takových procedur z Azure Logic Apps způsobit problémy.

Operace konektoru SQL Serveru nativně nepodporují asynchronní režim. Pokud chcete toto omezení obejít, simulujte tento režim pomocí následujících položek:

• Trigger dokončení SQL
• Nativní předávací dotaz SQL
• Tabulka stavů
• Úlohy na straně serveru

Předpokládejme například, že máte následující dlouho trvající uloženou proceduru. Pokud chcete dokončit spuštění, tento postup překročí limit časového limitu. Pokud spustíte tuto uloženou proceduru z pracovního postupu pomocí akce konektoru SQL Serveru s názvem Spustit uloženou proceduru, zobrazí se chyba časového limitu brány HTTP 504 .

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

Místo přímého volání uložené procedury můžete proceduru asynchronně spustit na pozadí pomocí agenta úlohy. Vstupy a výstupy můžete uložit do stavové tabulky, ke které pak můžete přistupovat a spravovat prostřednictvím pracovního postupu. Pokud nepotřebujete vstupy a výstupy nebo pokud už výsledky zapisujete do tabulky v uložené proceduře, můžete tento přístup zjednodušit.

Důležité

Ujistěte se, že uložená procedura a všechny úlohy jsou idempotentní, což znamená, že mohou běžet několikrát, aniž by to mělo vliv na výsledky. Pokud asynchronní zpracování selže nebo vyprší časový limit, agent úlohy může opakovat uloženou proceduru několikrát. Než vytvoříte jakékoli objekty, abyste se vyhnuli duplikování výstupu, podívejte se na tyto osvědčené postupy a přístupy.

Pokud chcete asynchronně spustit proceduru na pozadí s agentem úloh pro cloudový SQL Server, postupujte podle kroků k vytvoření a použití agenta elastických úloh Azure pro Azure SQL Database.

Pro místní SQL Server a spravovanou instanci Azure SQL vytvořte a použijte místo toho agenta SQL Serveru . Základní kroky zůstávají stejné jako nastavení agenta úloh pro Azure SQL Database.

Vytvoření agenta úloh pro Azure SQL Database

Pokud chcete vytvořit agenta úloh, který může spouštět uložené procedury pro Azure SQL Database, vytvořte a použijte agenta elastických úloh Azure. Než ale budete moct vytvořit tohoto agenta úloh, musíte nastavit oprávnění, skupiny a cíle, jak je popsáno v dokumentaci agenta elastických úloh Azure. V cílové databázi musíte také vytvořit podpůrnou stavovou tabulku, jak je popsáno v následujících částech.

Pokud chcete vytvořit agenta úloh, proveďte tuto úlohu na webu Azure Portal. Tento přístup přidává do databáze používané agentem několik uložených procedur, označovaných také jako databáze agenta. Potom můžete vytvořit agenta úlohy, který spustí uloženou proceduru v cílové databázi a po dokončení zachytí výstup.

Vytvoření stavové tabulky pro registraci parametrů a ukládání vstupů

Úlohy agenta SQL nepřijímají vstupní parametry. Místo toho v cílové databázi vytvořte stavovou tabulku, ve které zaregistrujete parametry a uložíte vstupy pro volání uložených procedur. Všechny kroky úlohy agenta se provádějí na cílové databázi, ale uložené procedury úlohy se provádějí na databázi agenta.

K vytvoření stavové tabulky použijte toto schéma:

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]

Výsledná tabulka vypadá v aplikaci SQL Server Management Studio (SMSS):

Aby se zajistil dobrý výkon a ujistěte se, že agent úloh najde přidružený záznam, tabulka jako primární klíč používá ID spuštění úlohy (jobid). Pokud chcete, můžete také přidat jednotlivé sloupce pro vstupní parametry. Dříve popsané schéma může obecněji zpracovat více parametrů, ale je omezeno na velikost vypočítanou NVARCHAR(MAX) funkcí.

Vytvoření úlohy nejvyšší úrovně pro spuštění uložené procedury

Pokud chcete spustit dlouho běžící uloženou proceduru, vytvořte tohoto agenta úloh nejvyšší úrovně v databázi agenta:

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

Přidejte kroky do úlohy, která parametrizuje, spustí a dokončí uloženou proceduru. Ve výchozím nastavení vyprší časový limit kroku úlohy po 12 hodinách. Pokud uložená procedura potřebuje víc času nebo pokud chcete, aby dříve vypršel časový limit procedury, můžete parametr změnit step_timeout_seconds na jinou hodnotu zadanou v sekundách. Ve výchozím nastavení má krok 10 předdefinovaných opakování s časovým limitem zpětného ukončení mezi jednotlivými opakováními, které můžete použít pro vaši výhodu.

Tady jsou kroky, jak přidat:

1. Počkejte, až se parametry zobrazí v LongRunningState tabulce.

   Tento první krok čeká, až se parametry přidají do LongRunningState tabulky, což se stane brzy po spuštění úlohy. Pokud se ID provádění úlohy (jobid) nepřidá do LongRunningState tabulky, krok se pouze nezdaří. Výchozí časový limit opakování nebo zpoždění zajistí čekání:

   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. Zadejte dotaz na parametry ze stavové tabulky a předejte je uložené proceduře. Tento krok také spustí postup na pozadí.

   Pokud uložená procedura nepotřebuje parametry, zavolejte přímo uloženou proceduru. V opačném případě použijte @timespan k předání parametru @callparams, který můžete také rozšířit k předání dalších parametrů.

   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. Dokončete úlohu a poznamenejte si výsledky.

   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'
   

Spusťte úlohu a předejte parametry.

Pokud chcete úlohu spustit, použijte předávací nativní dotaz s akcí Provést dotaz SQL a okamžitě nasdílejte parametry úlohy do stavové tabulky. Aby služba Azure Logic Apps poskytla vstup atributu jobid v cílové tabulce, přidá pro každou smyčku, která iteruje výstupem tabulky z předchozí akce. Pro každé ID spuštění úlohy spusťte akci vložit řádek, která používá výstup dynamických dat nazvaný ResultSets JobExecutionId k přidání parametrů pro úlohu k rozbalení a předání do cílové uložené procedury.

Po dokončení úlohy se LongRunningState tabulka aktualizuje. Z jiného pracovního postupu můžete výsledek aktivovat pomocí triggeru s názvem Při úpravě položky. Pokud výstup nepotřebujete nebo pokud už máte trigger, který monitoruje výstupní tabulku, můžete tuto část přeskočit.

Vytvoření agenta úloh pro SQL Server nebo spravovanou instanci Azure SQL

Pro místní SQL Server a azure SQL Managed Instance vytvořte a použijte agenta SQL Serveru. V porovnání s cloudovým agentem úloh pro Azure SQL Database se některé podrobnosti správy liší, ale základní kroky zůstávají stejné.

Další krok

• Připojení k databázím SQL z pracovních postupů v Azure Logic Apps