Obsługa limitów czasu procedury składowanej w łączniku SQL dla usługi Azure Logic Apps

Dotyczy: Azure Logic Apps (zużycie)

Gdy aplikacja logiki współpracuje z zestawami wyników tak dużymi, że łącznik SQL nie zwraca wszystkich wyników jednocześnie lub jeśli chcesz mieć większą kontrolę nad rozmiarem i strukturą zestawów wyników, możesz utworzyć procedurę składowaną , która organizuje wyniki w żądany sposób. Łącznik SQL udostępnia wiele funkcji zaplecza, do których można uzyskać dostęp przy użyciu usługi Azure Logic Apps , dzięki czemu można łatwiej zautomatyzować zadania biznesowe, które współpracują z tabelami bazy danych SQL.

Na przykład podczas pobierania lub wstawiania wielu wierszy aplikacja logiki może iterować te wiersze przy użyciu pętli Until w tych limitach. Jednak gdy aplikacja logiki musi pracować z tysiącami lub milionami wierszy, chcesz zminimalizować koszty wynikające z wywołań do bazy danych. Aby uzyskać więcej informacji, zobacz Obsługa danych zbiorczych przy użyciu łącznika SQL.

Limit czasu wykonywania procedury składowanej

Łącznik SQL ma limit limitu czasu procedury składowanej, który jest krótszy niż 2 minuty. Niektóre procedury składowane mogą potrwać dłużej niż ten limit, powodując 504 Timeout błąd. Czasami te długotrwałe procesy są kodowane jako procedury składowane jawnie w tym celu. Ze względu na limit czasu wywołanie tych procedur z usługi Azure Logic Apps może powodować problemy. Mimo że łącznik SQL nie obsługuje natywnie trybu asynchronicznego, można obejść ten problem i symulować ten tryb przy użyciu wyzwalacza uzupełniania SQL, natywnego zapytania przekazywanego SQL, tabeli stanu i zadań po stronie serwera. W tym zadaniu można użyć agenta zadań elastycznych platformy Azure dla usługi Azure SQL Database. W przypadku SQL Server lokalnych i Azure SQL Managed Instance można użyć agenta SQL Server.

Załóżmy na przykład, że masz następującą długotrwałą procedurę składowaną, która trwa dłużej niż limit czasu do zakończenia działania. Jeśli uruchomisz tę procedurę składowaną z aplikacji logiki przy użyciu łącznika SQL, zostanie wyświetlony HTTP 504 Gateway Timeout błąd w wyniku.

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

Zamiast bezpośrednio wywoływać procedurę składowaną, można asynchronicznie uruchomić procedurę w tle przy użyciu agenta zadań. Dane wejściowe i wyjściowe można przechowywać w tabeli stanów, z którą można korzystać za pośrednictwem aplikacji logiki. Jeśli nie potrzebujesz danych wejściowych i wyjściowych lub jeśli już piszesz wyniki do tabeli w procedurze składowanej, możesz uprościć to podejście.

Ważne

Upewnij się, że procedura składowana i wszystkie zadania są idempotentne, co oznacza, że mogą być uruchamiane wiele razy bez wpływu na wyniki. Jeśli przetwarzanie asynchroniczne kończy się niepowodzeniem lub upłynął limit czasu, agent zadania może ponowić próbę wykonania kroku, a tym samym procedurę składowaną wiele razy. Aby uniknąć duplikowania danych wyjściowych, przed utworzeniem jakichkolwiek obiektów zapoznaj się z tymi najlepszymi rozwiązaniami i podejściami.

W następnej sekcji opisano sposób używania agenta zadań elastycznych platformy Azure dla bazy danych Azure SQL Database. W przypadku SQL Server i Azure SQL Managed Instance można użyć agenta SQL Server. Niektóre szczegóły zarządzania będą się różnić, ale podstawowe kroki pozostają takie same jak konfigurowanie agenta zadań dla usługi Azure SQL Database.

Agent zadań dla bazy danych Azure SQL

Aby utworzyć zadanie, które może uruchomić procedurę składowaną dla bazy danych Azure SQL, użyj agenta zadań elastycznych platformy Azure. Utwórz agenta zadań w Azure Portal. Takie podejście spowoduje dodanie kilku procedur składowanych do bazy danych używanej przez agenta, nazywanej również bazą danych agentów. Następnie można utworzyć zadanie uruchamiające procedurę składowaną w docelowej bazie danych i przechwytywać dane wyjściowe po zakończeniu.

Przed utworzeniem zadania należy skonfigurować uprawnienia, grupy i cele zgodnie z opisem w pełnej dokumentacji agenta zadań elastycznych platformy Azure. Należy również utworzyć tabelę pomocniczą w docelowej bazie danych zgodnie z opisem w poniższych sekcjach.

Tworzenie tabeli stanów na potrzeby rejestrowania parametrów i przechowywania danych wejściowych

Zadania agenta SQL nie akceptują parametrów wejściowych. Zamiast tego w docelowej bazie danych utwórz tabelę stanu, w której rejestrujesz parametry i przechowujesz dane wejściowe do użycia do wywoływania procedur składowanych. Wszystkie kroki zadania agenta są uruchamiane względem docelowej bazy danych, ale procedury składowane zadania są uruchamiane względem bazy danych agenta.

Aby utworzyć tabelę stanu, użyj następującego schematu:

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]

Oto, jak wynikowa tabela wygląda w SQL Server Management Studio (SMSS):

Aby zapewnić dobrą wydajność i upewnić się, że zadanie agenta może znaleźć skojarzony rekord, tabela używa identyfikatora wykonania zadania (jobid) jako klucza podstawowego. Jeśli chcesz, możesz również dodać poszczególne kolumny dla parametrów wejściowych. Wcześniej opisany schemat może bardziej ogólnie obsługiwać wiele parametrów, ale jest ograniczony do rozmiaru obliczonego przez NVARCHAR(MAX).

Tworzenie zadania najwyższego poziomu w celu uruchomienia procedury składowanej

Aby wykonać długotrwałą procedurę składowaną, utwórz tego agenta zadań najwyższego poziomu w bazie danych agentów:

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

Teraz dodaj kroki do zadania, które sparametryzuje, uruchom i ukończ procedurę składowaną. Domyślnie limit czasu zadania po upływie 12 godzin. Jeśli procedura składowana wymaga więcej czasu lub chcesz, aby procedura została przekroczona wcześniej, możesz zmienić step_timeout_seconds parametr na inną wartość określoną w sekundach. Domyślnie krok ma 10 wbudowanych ponownych prób z przekroczeniem limitu czasu wycofywania między poszczególnymi ponawianiami, których można użyć do twojej korzyści.

Poniżej przedstawiono kroki dodawania:

1. Poczekaj LongRunningState na wyświetlenie parametrów w tabeli.

   Ten pierwszy krok czeka na dodanie parametrów do LongRunningState tabeli, co następuje wkrótce po uruchomieniu zadania. Jeśli identyfikator wykonania zadania (jobid) nie zostanie dodany do LongRunningState tabeli, krok kończy się niepowodzeniem, a domyślny limit czasu ponawiania lub wycofywania wykonuje oczekiwanie:

   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. Wykonaj zapytanie o parametry z tabeli stanu i przekaż je do procedury składowanej. Ten krok uruchamia również procedurę w tle.

   Jeśli procedura składowana nie wymaga parametrów, po prostu bezpośrednio wywołaj procedurę składowaną. W przeciwnym razie, aby przekazać @timespan parametr, użyj @callparamspolecenia , który można również rozszerzyć, aby przekazać dodatkowe 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. Ukończ zadanie i zarejestruj wyniki.

   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'
   

Uruchamianie zadania i przekazywanie parametrów

Aby uruchomić zadanie, użyj zapytania natywnego przekazującego z akcją Wykonaj zapytanie SQL i natychmiast wypchnij parametry zadania do tabeli stanów. Aby podać dane wejściowe do atrybutu jobid w tabeli docelowej, usługa Logic Apps dodaje pętlę Dla każdej pętli, która iteruje dane wyjściowe tabeli z poprzedniej akcji. Dla każdego identyfikatora wykonania zadania uruchom akcję Wstaw wiersz , która używa dynamicznych danych wyjściowych, ResultSets JobExecutionId, aby dodać parametry zadania do rozpakowania i przekazania do docelowej procedury składowanej.

Po zakończeniu zadania zadanie aktualizuje tabelęLongRunningState, aby można było łatwo wyzwolić wynik przy użyciu wyzwalacza Po zmodyfikowaniu elementu. Jeśli nie potrzebujesz danych wyjściowych lub jeśli masz już wyzwalacz monitorujący tabelę danych wyjściowych, możesz pominąć tę część.

Agent zadań dla SQL Server lub Azure SQL Managed Instance

W tym samym scenariuszu można użyć agenta SQL Server do SQL Server lokalnie i Azure SQL Managed Instance. Chociaż niektóre szczegóły zarządzania różnią się, podstawowe kroki pozostają takie same jak konfigurowanie agenta zadań dla bazy danych Azure SQL.

Następne kroki

Nawiązywanie połączenia z SQL Server, bazą danych Azure SQL lub Azure SQL Managed Instance