Zpracování časových limitů uložených procedur v konektoru SQL pro Azure Logic Apps

  • Článek

Platí pro: Azure Logic Apps (Consumption)

Pokud vaše aplikace logiky pracuje se sadami výsledků dotazu tak velkými, že konektor SQL nevrací všechny výsledky najednou, nebo pokud chcete mít větší kontrolu nad velikostí a strukturou sad výsledků výsledků, můžete vytvořit uloženou proceduru , která výsledky uspořádá požadovaným způsobem. Konektor SQL poskytuje mnoho back-endových funkcí, ke kterým můžete přistupovat pomocí Azure Logic Apps , abyste mohli snadněji automatizovat obchodní úlohy, které pracují s tabulkami databáze SQL.

Například při získávání nebo vkládání více řádků může vaše aplikace logiky iterovat těmito řádky pomocí smyčky Until v rámci těchto limitů. Pokud ale vaše aplikace logiky musí pracovat s tisíci nebo miliony řádků, chcete minimalizovat náklady na volání databáze. Další informace najdete v tématu Zpracování hromadných dat pomocí konektoru SQL.

Časový limit pro provádění uložených procedur

Konektor SQL má limit časového limitu uložené procedury, který je kratší než 2 minuty. Dokončení některých uložených procedur může trvat déle, než je tento limit, což způsobí 504 Timeout chybu. Někdy jsou tyto dlouhotrvající procesy kódované jako uložené procedury explicitně pro tento účel. Vzhledem k limitu časového limitu může volání těchto procedur z Azure Logic Apps způsobovat problémy. I když konektor SQL nativně nepodporuje asynchronní režim, můžete tento problém obejít a tento režim simulovat pomocí triggeru dokončení SQL, nativního předávacího dotazu SQL, stavové tabulky a úloh na straně serveru. Pro tuto úlohu můžete použít agenta elastických úloh Azure pro Azure SQL Database. Pro SQL Server v místním prostředí a Azure SQL Managed Instance můžete použít agenta SQL Server.

Předpokládejme například, že máte následující dlouho běžící uloženou proceduru, jejíž dokončení trvá déle, než je limit časového limitu. Pokud tuto uloženou proceduru spustíte z aplikace logiky pomocí konektoru SQL, zobrazí se HTTP 504 Gateway Timeout jako výsledek chyba.

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 asynchronně spustit proceduru na pozadí pomocí agenta úloh. Vstupy a výstupy můžete uložit do stavové tabulky, se kterou pak můžete pracovat prostřednictvím aplikace logiky. Pokud nepotřebujete vstupy a výstupy nebo pokud už zapisujete výsledky 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 se můžou spustit vícekrát, aniž by to mělo vliv na výsledky. Pokud asynchronní zpracování selže nebo vyprší jeho časový limit, agent úloh může zkusit krok, a tedy i uloženou proceduru, několikrát. Abyste zabránili duplikování výstupu, před vytvořením jakýchkoli objektů si projděte tyto osvědčené postupy a přístupy.

Další část popisuje, jak používat agenta elastických úloh Azure pro službu Azure SQL Database. Pro SQL Server a Azure SQL Managed Instance můžete použít agenta SQL Server. Některé podrobnosti správy se budou lišit, ale základní kroky zůstávají stejné jako nastavení agenta úloh pro Azure SQL Database.

Agent úloh pro Azure SQL Database

Pokud chcete vytvořit úlohu, která může spustit uloženou proceduru pro Azure SQL Database, použijte agenta elastických úloh Azure. Vytvořte agenta úloh v Azure Portal. Tento přístup přidá do databáze, kterou používá agent, několik uložených procedur, označovaných také jako databáze agenta. Pak můžete vytvořit úlohu, která spustí uloženou proceduru v cílové databázi a po dokončení zachytí výstup.

Než budete moct vytvořit úlohu, musíte nastavit oprávnění, skupiny a cíle, jak je popsáno v úplné dokumentaci k agentu elastických úloh Azure. V cílové databázi je také potřeba vytvořit podpůrnou tabulku, jak je popsáno v následujících částech.

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 tabulku stavů, ve které zaregistrujete parametry a uložíte vstupy, které se mají použít pro volání uložených procedur. Všechny kroky úlohy agenta se spouští pro cílovou databázi, ale uložené procedury úlohy se spouští v 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 v SQL Server Management Studio (SMSS) vypadá takto:

Snímek obrazovky znázorňující vytvořenou tabulku stavů, která ukládá vstupy pro uloženou proceduru

Aby se zajistil dobrý výkon a aby úloha agenta našla přidružený záznam, používá tabulka jako primární klíč 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 zpracovávat více parametrů, ale je omezené na velikost vypočítanou pomocí NVARCHAR(MAX).

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

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

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

Teď do úlohy přidejte kroky, které parametrizují, 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íce času nebo pokud chcete, aby vypršel časový limit procedury dříve, můžete změnit step_timeout_seconds parametr na jinou hodnotu, která je zadaná v sekundách. Ve výchozím nastavení má krok 10 předdefinovaných opakování s časovým limitem mezi jednotlivými opakováními, které můžete využít ve svůj prospěch.

Tady je postup přidání:

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

    Tento první krok čeká na přidání parametrů do LongRunningState tabulky, k čemuž dojde brzy po spuštění úlohy. Pokud se ID spuštění úlohy (jobid) nepřidá do LongRunningState tabulky, krok pouze selže a výchozí časový limit opakování nebo zpochybnění provede č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í proceduru na pozadí.

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

    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 zaznamenejte 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 spustit úlohu, použijte předávací nativní dotaz s akcí Spustit dotaz SQL a okamžitě nasdílejte parametry úlohy do stavové tabulky. Aby služba Logic Apps poskytla vstup atributu jobid v cílové tabulce, přidá smyčku For each , která iteruje výstupem tabulky z předchozí akce. Pro každé ID spuštění úlohy spusťte akci Vložit řádek , ResultSets JobExecutionIdkterá pomocí výstupu dynamických dat přidá parametry pro úlohu pro rozbalení a předání do cílové uložené procedury.

Snímek obrazovky znázorňující akce, které se mají použít ke spuštění úlohy a předání parametrů uložené proceduře

Po dokončení úlohy úloha aktualizuje LongRunningState tabulku, takže můžete snadno aktivovat výsledek pomocí triggeru Při změně 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.

Snímek obrazovky znázorňující trigger SQL při změně položky

Agent úloh pro SQL Server nebo Azure SQL Managed Instance

Pro stejný scénář můžete použít agenta SQL Server pro místní SQL Server a Azure SQL Managed Instance. I když se některé podrobnosti správy liší, základní kroky zůstávají stejné jako nastavení agenta úloh pro Azure SQL Database.

Další kroky

Připojení k SQL Server, Azure SQL Database nebo Azure SQL Managed Instance