Steuern großer SQL-Resultsets und Timeouts während gespeicherter Prozeduren in Workflows für Azure Logic Apps

Gilt für: Azure Logic Apps (Verbrauch + Standard)

Um Geschäftsaufgaben, die mit SQL-Datenbanken arbeiten, einfacher zu automatisieren, kann Ihr Workflow SQL Server-Connectorvorgänge verwenden, die viele Back-End-Features für Workflows bereitstellen, die in Azure Logic Apps verwendet werden können.

In einigen Situationen muss Ihr Workflow möglicherweise große Resultsets behandeln. Diese Resultsets können so groß sein, dass SQL Server-Connectorvorgänge nicht alle Ergebnisse gleichzeitig zurückgeben. In anderen Situationen möchten Sie möglicherweise nur mehr Kontrolle über die Größe und Struktur ihrer Resultsets. Um die Ergebnisse auf die gewünschte Weise zu organisieren, können Sie eine gespeicherte Prozedur erstellen.

Wenn beispielsweise eine SQL Server-Connectoraktion mehrere Zeilen abruft oder einfügt, kann Ihr Workflow diese Zeilen mithilfe einer Until-Schleife durchlaufen, die innerhalb dieser Grenzwerte funktioniert. Wenn Ihr Workflow Tausende oder Millionen von Zeilen verarbeiten muss, möchten Sie die Kosten minimieren, die sich aus SQL Server-Connectoraktionsaufrufen an die SQL-Datenbank ergeben. Weitere Informationen finden Sie unter Verarbeiten von Massendaten mithilfe des SQL-Connectors.

In diesem Handbuch wird gezeigt, wie Sie die Größe, Struktur und Timeouts beim Verarbeiten großer Resultsets mithilfe der SQL Server-Connectoraktionen steuern.

Timeout-Limit für die Ausführung gespeicherter Prozeduren

Der SQL Server-Connector verfügt über eine Execute-Aktion für gespeicherte Prozeduren mit Einem Timeoutlimit, das weniger als zwei Minuten beträgt. Einige gespeicherte Prozeduren dauern möglicherweise länger als dieser Grenzwert, was zu einem Timeoutfehler von 504 führt. Manchmal werden lang andauernde Prozesse explizit als gespeicherte Prozeduren codiert. Aufgrund des Zeitlimits kann das Aufrufen solcher Prozeduren aus Azure Logic Apps Probleme verursachen.

Die SQL Server-Konnektor-Vorgänge unterstützen standardmäßig keinen asynchronen Modus. Um diese Einschränkung zu umgehen, simulieren Sie diesen Modus mithilfe der folgenden Elemente:

• SQL-Abschlusstrigger
• Native SQL Pass-Through-Abfrage
• Statustabelle
• Serverseitige Aufträge

Angenommen, Sie haben die folgende lang andauernde gespeicherte Prozedur. Um die Ausführung abschließen zu können, überschreitet die Prozedur das Zeitimit. Wenn Sie diese gespeicherte Prozedur aus einem Workflow mithilfe der SQL Server-Connectoraktion namens " Gespeicherte Prozedur ausführen" ausführen, erhalten Sie den HTTP 504-Gatewaytimeoutfehler .

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 die Prozedur mithilfe eines Auftrags-Agents asynchron im Hintergrund ausführen. Sie können die Eingaben und Ausgaben in einer Zustandstabelle speichern, auf die Sie dann über Ihren Workflow zugreifen und diese verwalten können. Wenn Sie die Eingaben und Ausgaben nicht benötigen oder wenn Sie die Ergebnisse bereits in eine Tabelle in der gespeicherten Prozedur schreiben, können Sie diesen Ansatz vereinfachen.

Führen Sie zum asynchronen Ausführen der Prozedur im Hintergrund mit dem Auftrags-Agent für cloudbasierte SQL Server die Schritte zum Erstellen und Verwenden des Azure Elastic Job Agent für Azure SQL-Datenbank aus.

Erstellen und verwenden Sie stattdessen den SQL Server-Agent für lokale SQL Server- und Azure SQL Managed Instance. Die grundlegenden Schritte bleiben identisch mit dem Einrichten eines Auftrags-Agents für Azure SQL-Datenbank.

Erstellen eines Auftrags-Agents für Azure SQL-Datenbank

Um einen Auftrags-Agent zu erstellen, der gespeicherte Prozeduren für Azure SQL-Datenbank ausführen kann, erstellen und verwenden Sie den Azure Elastic Job Agent. Bevor Sie diesen Auftragsagent erstellen können, müssen Sie jedoch die Berechtigungen, Gruppen und Ziele einrichten, wie in der Dokumentation zu Azure Elastic Job Agent beschrieben. Sie müssen auch eine unterstützende Statustabelle in der Zieldatenbank erstellen, wie in den folgenden Abschnitten beschrieben.

Führen Sie diese Aufgabe im Azure-Portal aus, um den Auftragsagenten zu erstellen. Bei diesem Ansatz werden der Datenbank, die vom Agent verwendet wird, mehrere gespeicherte Prozeduren hinzugefügt, die auch als Agentdatenbank bezeichnet werden. Anschließend können Sie einen Auftrags-Agent erstellen, der Die gespeicherte Prozedur in der Zieldatenbank ausführt und die Ausgabe erfasst, wenn sie fertig ist.

Erstellen einer Statustabelle zum Registrieren von Parametern und Speichern von Eingaben

SQL-Agentaufträge akzeptieren keine Eingabeparameter. Erstellen Sie stattdessen in der Zieldatenbank eine Statustabelle, in der Sie die Parameter registrieren und die Eingaben speichern, die zum Aufrufen Ihrer gespeicherten Prozeduren verwendet werden sollen. Alle Agent-Auftragsschritte werden für die Zieldatenbank ausgeführt, aber die gespeicherten Prozeduren des Auftrags werden für die Agentdatenbank ausgeführt.

Verwenden Sie dieses Schema, um die Statustabelle zu erstellen:

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 sicherzustellen und sicherzustellen, dass der Auftrags-Agent den zugeordneten Datensatz finden kann, verwendet die Tabelle die Auftragsausführungs-ID (jobid) als Primärschlüssel. Wenn Sie möchten, können Sie auch einzelne Spalten für die Eingabeparameter hinzufügen. Das zuvor beschriebene Schema kann im Allgemeinen mehrere Parameter behandeln, ist jedoch auf die von der NVARCHAR(MAX) Funktion 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 dem Auftrag Schritte hinzu, die die gespeicherte Prozedur parametrisieren, ausführen und abschließen werden. Standardmäßig tritt für einen Auftragsschritt nach 12 Stunden ein Timeout ein. Wenn Ihre gespeicherte Prozedur mehr Zeit benötigt oder wenn die Prozedur früher ablaufen soll, können Sie den step_timeout_seconds-Parameter auf einen anderen Wert in Sekunden ändern. Standardmäßig hat ein Schritt zehn integrierte Wiederholungsversuche mit einem Backoff-Timeout zwischen den einzelnen Versuchen, wovon Sie profitieren können.

Hier sind die Schritte zum Hinzufügen:

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

   Dieser erste Schritt wartet darauf, dass die Parameter in die LongRunningState Tabelle hinzugefügt werden, was gleich nach dem Start des Auftrags geschieht. Wenn die Auftragsausführungs-ID (jobid) der LongRunningState-Tabelle nicht hinzugefügt wird, schlägt der Schritt einfach fehl. Das standardmäßige Wiederholungs- oder Backoffzeitlimit für die Wartezeit:

   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 Statustabelle ab, und übergeben Sie sie an die gespeicherte Prozedur. In diesem Schritt wird auch die Prozedur im Hintergrund ausgeführt.

   Wenn Ihre gespeicherte Prozedur keine Parameter benötigt, rufen Sie die gespeicherte Prozedur direkt auf. Verwenden Sie andernfalls @timespan, um den @callparams Parameter zu übergeben. Sie können @timespan auch erweitern, um weitere Parameter zu übergeben.

   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. Füllen Sie den Auftrag aus, und notieren Sie die Ergebnisse.

   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

Zum Starten des Jobs verwenden Sie eine native Passthrough-Abfrage mit der Aktion SQL-Abfrage ausführen und übertragen Sie die Parameter des Jobs sofort in die Statustabelle. Um Eingaben für das jobid Attribut in der Zieltabelle bereitzustellen, fügt Azure Logic Apps eine For each-Schleife hinzu, die die Tabellenausgabe aus der vorherigen Aktion durchläuft. Führen Sie für jede Jobausführungs-ID eine Zeile einfügen-Aktion aus, die die dynamische Datenausgabe mit dem Namen ResultSets JobExecutionId verwendet, um die Parameter des Jobs hinzuzufügen, die entpackt und an die gespeicherte Zielprozedur übergeben werden sollen.

Nach Abschluss des Auftrags aktualisiert der Auftrag die Tabelle LongRunningState. Aus einem anderen Workflow können Sie das Ergebnis mithilfe des Triggers " Wenn ein Element geändert wird" auslösen. Wenn Sie die Ausgabe nicht benötigen oder bereits über einen Trigger verfügen, der eine Ausgabetabelle überwacht, können Sie diesen Teil überspringen.

Erstellen eines Job-Agents für SQL Server oder Azure SQL Managed Instance

Erstellen und verwenden Sie für lokale SQL Server- und Azure SQL Managed Instance den SQL Server-Agent. Im Vergleich zum cloudbasierten Auftrags-Agent für Azure SQL-Datenbank unterscheiden sich einige Verwaltungsdetails, aber die grundlegenden Schritte bleiben gleich.

Nächster Schritt

• Herstellen einer Verbindung mit SQL-Datenbanken über Workflows in Azure Logic Apps