Behandeln von Timeouts bei gespeicherten Prozeduren im SQL-Connector für Azure Logic Apps

Gilt für: Azure Logic Apps (Verbrauch)

Wenn Ihre Logik-App mit derart großen Resultsets arbeitet, dass der SQL-Connector nicht alle Ergebnisse gleichzeitig zurückgibt, oder wenn Sie mehr Kontrolle über die Größe und Struktur Ihrer Resultsets wünschen, können Sie eine gespeicherte Prozedur erstellen, die die Ergebnisse nach Ihren Wünschen organisiert. Der SQL-Connector bietet viele Back-End-Funktionen, auf die Sie über Azure Logic Apps zugreifen können, sodass Sie Geschäftsaufgaben, die mit SQL-Datenbanktabellen arbeiten, einfacher automatisieren können.

Beispiel: Beim Abrufen oder Einfügen mehrerer Zeilen kann Ihre Logik-App diese Zeilen mithilfe einer Until-Schleife innerhalb dieser Grenzwerte durchlaufen. Wenn Ihre Logik-App jedoch mit Tausenden oder Millionen von Zeilen arbeiten muss, sollten Sie die Kosten aufgrund von Aufrufen der Datenbank minimieren. Weitere Informationen finden Sie unter Verarbeiten von Massendaten mit dem SQL-Connector.

Zeitlimit bei Ausführung gespeicherter Prozeduren

Beim SQL-Connector gilt für gespeicherte Prozeduren ein Zeitlimit unter 2 Minuten. Einige gespeicherte Prozeduren benötigen möglicherweise mehr Zeit, was zu einem 504 Timeout-Fehler führt. Mitunter sind zu diesem Zweck einige zeitintensive Prozesse explizit als gespeicherte Prozeduren programmiert. Wenn Sie diese Prozeduren aus Azure Logic Apps aufrufen, können aufgrund dieses Zeitlimits Probleme entstehen. Obwohl der SQL-Connector einen asynchronen Modus nicht nativ unterstützt, können Sie dieses Problem umgehen und diesen Modus simulieren, indem Sie einen SQL-Abschlussauslöser, eine native SQL-Passthrough-Abfrage, eine Zustandstabelle und serverseitige Aufträge verwenden. Für diese Aufgabe können Sie den Azure-Agent für elastische Aufträge für Azure SQL-Datenbank verwenden. Für lokales SQL Server und Azure SQL Managed Instance können Sie den SQL Server-Agent nutzen.

Angenommen, Sie haben die folgende zeitintensive gespeicherte Prozedur, deren Ausführungsdauer das Zeitlimit überschreitet. Wenn Sie diese gespeicherte Prozedur in einer Logik-App unter Verwendung des SQL-Connectors ausführen, erhalten Sie als Ergebnis den Fehler HTTP 504 Gateway Timeout.

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

Anstatt die gespeicherte Prozedur direkt aufzurufen, können Sie sie asynchron im Hintergrund ausführen, indem Sie einen Auftrags-Agent verwenden. Sie können die Ein- und Ausgaben in einer Zustandstabelle speichern, mit der Sie dann über Ihre Logik-App interagieren können. Wenn Sie die Ein- und Ausgaben nicht benötigen oder die Ergebnisse bereits in der gespeicherten Prozedur in eine Tabelle schreiben, können Sie diesen Ansatz vereinfachen.

Im nächsten Abschnitt wird beschrieben, wie Sie den Azure-Agent für elastische Aufträge für Azure SQL-Datenbank verwenden können. Für SQL Server und Azure SQL Managed Instance können Sie den SQL Server-Agent nutzen. Einige Verwaltungsdetails unterscheiden sich, aber die grundlegenden Schritte bleiben die gleichen wie beim Einrichten eines Auftrags-Agents für Azure SQL-Datenbank.

Auftrags-Agent für Azure SQL-Datenbank

Verwenden Sie zum Erstellen eines Auftrags, der die gespeicherte Prozedur für Azure SQL-Datenbank ausführen kann, den Azure-Agent für elastische Aufträge. Erstellen Sie Ihren Auftrags-Agent im Azure-Portal. Bei diesem Ansatz werden mehrere gespeicherte Prozeduren zur Datenbank hinzugefügt, die vom Agent verwendet und auch als Agent-Datenbank bezeichnet wird. Sie können dann einen Auftrag erstellen, der Ihre gespeicherte Prozedur in der Zieldatenbank ausführt und am Ende die Ausgabe erfasst.

Bevor Sie den Auftrag erstellen können, müssen Sie Berechtigungen, Gruppen und Ziele einrichten, wie in der vollständigen Dokumentation für den Azure-Agent für elastische Aufträge beschrieben. Sie müssen auch, wie in den folgenden Abschnitten beschrieben, eine unterstützende Tabelle in der Zieldatenbank erstellen.

Erstellen einer Zustandstabelle zum Registrieren von Parametern und Speichern von Eingaben

SQL-Agent-Aufträge lassen keine Eingabeparameter zu. Erstellen Sie stattdessen in der Zieldatenbank eine Zustandstabelle, in der Sie die Parameter registrieren und Eingaben speichern, die für das Aufrufen Ihrer gespeicherten Prozeduren verwendet werden sollen. Alle Schritte des Agent-Auftrags erfolgen in der Zieldatenbank, aber die gespeicherten Prozeduren des Auftrags werden in der Agent-Datenbank ausgeführt.

Erstellen Sie die Zustandstabelle mithilfe dieses Schemas:

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]

So sieht die resultierende Tabelle in SQL Server Management Studio (SMSS) aus:

Um eine gute Leistung zu gewährleisten und sicherzustellen, dass der Agent-Auftrag den zugehörigen Datensatz finden kann, verwendet die Tabelle die Auftragsausführungs-ID (jobid) als Primärschlüssel. Wenn Sie möchten, können Sie auch für die Eingabeparameter einzelne Spalten hinzufügen. Das zuvor beschriebene Schema kommt im Allgemeinen mit mehreren Parametern zurecht, ist jedoch auf die von NVARCHAR(MAX) berechnete Größe beschränkt.

Erstellen eines Auftrags auf oberster Ebene zum Ausführen der gespeicherten Prozedur

Um die zeitintensive gespeicherte Prozedur auszuführen, erstellen Sie in der Agent-Datenbank diesen Auftrags-Agent auf oberster Ebene:

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

Fügen Sie nun Schritte zum Auftrag hinzu, die die gespeicherte Prozedur parametrisieren, ausführen und abschließen. Standardmäßig tritt für einen Auftragsschritt nach 12 Stunden ein Timeout ein. Wenn Ihre gespeicherte Prozedur mehr Zeit benötigt oder Sie möchten, dass das Timeout der Prozedur früher erfolgt, können Sie den Parameter step_timeout_seconds in einen anderen Wert ändern, der in Sekunden angegeben wird. Standardmäßig hat ein Schritt zehn integrierte Wiederholungsversuche mit einem Backoff-Timeout zwischen den einzelnen Wiederholungsversuchen, das Sie zu Ihrem Vorteil nutzen können.

Es folgen die Schritte, die Sie hinzufügen müssen:

1. Warten Sie, bis die Parameter in der Tabelle LongRunningState angezeigt werden.

   Dieser erste Schritt wartet darauf, dass die Parameter zur Tabelle LongRunningState hinzugefügt werden, was kurz nach Start des Auftrags geschieht. Wenn die Auftragsausführungs-ID (jobid) nicht zur Tabelle LongRunningState hinzugefügt wird, schlägt der Schritt lediglich fehl, und das standardmäßige Wiederholungs- oder Backoff-Timeout erledigt das Warten:

   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. Fragen Sie die Parameter aus der Zustandstabelle ab, und übergeben Sie sie an die gespeicherte Prozedur. Auch bei diesem Schritt wird die Prozedur im Hintergrund ausgeführt.

   Wenn Ihre gespeicherte Prozedur keine Parameter benötigt, rufen Sie die gespeicherte Prozedur einfach direkt auf. Andernfalls verwenden Sie zur Übergabe des Parameters @timespan den Parameter @callparams, den Sie auch zur Übergabe zusätzlicher Parameter erweitern können.

   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. Schließen Sie den Auftrag ab, und zeichnen Sie die Ergebnisse auf.

   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'
   

Starten des Auftrags und Übergeben der Parameter

Um den Auftrag zu starten, verwenden Sie eine native Passthrough-Abfrage mit der Aktion SQL-Abfrage ausführen, und übertragen Sie die Parameter des Auftrags sofort in die Zustandstabelle. Um Eingaben für das Attribut jobid in der Zieltabelle bereitzustellen, fügt Logic Apps eine For Each-Schleife hinzu, die die Tabellenausgabe der vorhergehenden Aktion durchläuft. Führen Sie für jede Auftragsausführungs-ID die Aktion Zeile einfügen aus, die die dynamische DatenausgabeResultSets JobExecutionId verwendet, um die Parameter für den Auftrag hinzuzufügen, mit denen er entpackt und an die gespeicherte Prozedur übergeben werden soll.

Wenn der Auftrag abgeschlossen ist, aktualisiert der Auftrag die Tabelle LongRunningState, sodass Sie mithilfe des Triggers Beim Ändern eines Elements einfach das Auslösen entsprechend dem Ergebnis vornehmen können. Wenn Sie die Ausgabe nicht benötigen oder bereits einen Trigger haben, der eine Ausgabetabelle überwacht, können Sie diesen Teil überspringen.

Auftrags-Agent für SQL Server oder Azure SQL Managed Instance

Im selben Szenario können Sie den SQL Server-Agent für lokales SQL Server und Azure SQL Managed Instance nutzen. Obwohl sich einige Verwaltungsdetails unterscheiden, bleiben die grundlegenden Schritte die gleichen wie beim Einrichten eines Auftrags-Agents für Azure SQL-Datenbank.

Nächste Schritte

Herstellen einer Verbindung mit SQL Server, Azure SQL-Datenbank oder Azure SQL Managed Instance