Hantera tidsgränser för lagrad procedur i SQL-anslutningsappen för Azure Logic Apps
Gäller för: Azure Logic Apps (förbrukning)
När logikappen fungerar med resultatuppsättningar som är så stora att SQL-anslutningsappen inte returnerar alla resultat samtidigt, eller om du vill ha mer kontroll över storleken och strukturen för dina resultatuppsättningar, kan du skapa en lagrad procedur som organiserar resultatet på det sätt du vill. SQL-anslutningsappen innehåller många serverdelsfunktioner som du kan komma åt med hjälp av Azure Logic Apps så att du enklare kan automatisera affärsuppgifter som fungerar med SQL-databastabeller.
När du till exempel hämtar eller infogar flera rader kan logikappen iterera genom dessa rader med hjälp av en Until-loop inom dessa gränser. Men när logikappen måste fungera med tusentals eller miljontals rader vill du minimera kostnaderna för anrop till databasen. Mer information finns i Hantera massdata med HJÄLP av SQL-anslutningsappen.
Tidsgräns för körning av lagrad procedur
SQL-anslutningsappen har en tidsgräns för lagrad procedur som är mindre än 2 minuter. Vissa lagrade procedurer kan ta längre tid än den här gränsen att slutföras, vilket orsakar ett 504 Timeout
fel. Ibland kodas dessa långvariga processer uttryckligen som lagrade procedurer för detta ändamål. På grund av tidsgränsen kan anrop av dessa procedurer från Azure Logic Apps skapa problem. Även om SQL-anslutningsappen inte har inbyggt stöd för ett asynkront läge kan du kringgå det här problemet och simulera det här läget med hjälp av en SQL-slutförandeutlösare, intern SQL-direktfråga, en tillståndstabell och jobb på serversidan. För den här uppgiften kan du använda Azure Elastic Job Agent för Azure SQL Database. För SQL Server lokalt och Azure SQL Managed Instance kan du använda SQL Server Agent.
Anta till exempel att du har följande långvariga lagrade procedur, som tar längre tid än tidsgränsen för att slutföra körningen. Om du kör den här lagrade proceduren från en logikapp med hjälp av SQL-anslutningsappen får du ett HTTP 504 Gateway Timeout
fel som resultat.
CREATE PROCEDURE [dbo].[WaitForIt]
@delay char(8) = '00:03:00'
AS
BEGIN
SET NOCOUNT ON;
WAITFOR DELAY @delay
END
I stället för att anropa den lagrade proceduren direkt kan du asynkront köra proceduren i bakgrunden med hjälp av en jobbagent. Du kan lagra indata och utdata i en tillståndstabell som du sedan kan interagera med via logikappen. Om du inte behöver indata och utdata, eller om du redan skriver resultatet till en tabell i den lagrade proceduren, kan du förenkla den här metoden.
Viktigt
Kontrollera att den lagrade proceduren och alla jobb är idempotent, vilket innebär att de kan köras flera gånger utan att påverka resultatet. Om den asynkrona bearbetningen misslyckas eller överskrider tidsgränsen kan jobbagenten försöka utföra steget igen, och därmed din lagrade procedur flera gånger. Om du vill undvika att duplicera utdata bör du granska de här metodtipsen och metoderna innan du skapar några objekt.
I nästa avsnitt beskrivs hur du kan använda Azure Elastic Job Agent för Azure SQL Database. För SQL Server och Azure SQL Managed Instance kan du använda SQL Server Agent. Vissa hanteringsuppgifter skiljer sig åt, men de grundläggande stegen är desamma som att konfigurera en jobbagent för Azure SQL Database.
Jobbagent för Azure SQL Database
Om du vill skapa ett jobb som kan köra den lagrade proceduren för Azure SQL Database använder du Azure Elastic Job Agent. Skapa din jobbagent i Azure Portal. Den här metoden lägger till flera lagrade procedurer i databasen som används av agenten, även kallad agentdatabasen. Du kan sedan skapa ett jobb som kör den lagrade proceduren i måldatabasen och registrerar utdata när du är klar.
Innan du kan skapa jobbet måste du konfigurera behörigheter, grupper och mål enligt beskrivningen i den fullständiga dokumentationen för Azure Elastic Job Agent. Du måste också skapa en stödtabell i måldatabasen enligt beskrivningen i följande avsnitt.
Skapa tillståndstabell för att registrera parametrar och lagra indata
SQL Agent-jobb accepterar inte indataparametrar. I måldatabasen skapar du i stället en tillståndstabell där du registrerar parametrarna och lagrar de indata som ska användas för att anropa dina lagrade procedurer. Alla agentjobbsteg körs mot måldatabasen, men jobbets lagrade procedurer körs mot agentdatabasen.
Använd det här schemat för att skapa tillståndstabellen:
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]
Så här ser den resulterande tabellen ut i SQL Server Management Studio (SMSS):
För att säkerställa bra prestanda och se till att agentjobbet kan hitta den associerade posten använder tabellen jobbets körnings-ID (jobid
) som primärnyckel. Om du vill kan du också lägga till enskilda kolumner för indataparametrarna. Det tidigare beskrivna schemat kan mer generellt hantera flera parametrar men begränsas till storleken som beräknas av NVARCHAR(MAX)
.
Skapa ett toppnivåjobb för att köra den lagrade proceduren
Om du vill köra den långvariga lagrade proceduren skapar du den här jobbagenten på den översta nivån i agentdatabasen:
EXEC jobs.sp_add_job
@job_name='LongRunningJob',
@description='Execute Long-Running Stored Proc',
@enabled = 1
Lägg nu till steg i jobbet som parameteriserar, kör och slutför den lagrade proceduren. Som standard överskrider ett jobbsteg tidsgränsen efter 12 timmar. Om den lagrade proceduren behöver mer tid, eller om du vill att proceduren ska överskrida tidsgränsen tidigare, kan du ändra parametern step_timeout_seconds
till ett annat värde som anges i sekunder. Som standard har ett steg 10 inbyggda återförsök med en tidsgräns för backoff mellan varje nytt försök, vilket du kan använda till din fördel.
Här följer stegen som du vill lägga till:
Vänta tills parametrarna visas i
LongRunningState
tabellen.Det första steget väntar på att parametrarna ska läggas till i
LongRunningState
tabellen, vilket sker strax efter att jobbet startar. Om jobbkörnings-ID:t (jobid
) inte läggs till iLongRunningState
tabellen misslyckas steget bara och standardtimeouten för återförsök eller backoff väntar: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'
Fråga parametrarna från tillståndstabellen och skicka dem till den lagrade proceduren. Det här steget kör även proceduren i bakgrunden.
Om den lagrade proceduren inte behöver parametrar anropar du bara den lagrade proceduren direkt. Om du vill skicka parametern
@timespan
använder du ,@callparams
som du också kan utöka för att skicka ytterligare parametrar.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'
Slutför jobbet och registrera resultatet.
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'
Starta jobbet och skicka parametrarna
Starta jobbet genom att använda en intern genomströmningsfråga med åtgärden Kör en SQL-fråga och skicka omedelbart jobbets parametrar till tillståndstabellen. För att ge indata till jobid
attributet i måltabellen lägger Logic Apps till en For each-loop som itererar genom tabellutdata från föregående åtgärd. För varje jobbkörnings-ID kör du en Infoga radåtgärd som använder dynamiska datautdata, ResultSets JobExecutionId
, för att lägga till parametrarna för jobbet för att packa upp och skicka till den lagrade målproceduren.
När jobbet är klart uppdaterar LongRunningState
jobbet tabellen så att du enkelt kan utlösa resultatet med utlösaren När ett objekt ändras. Om du inte behöver utdata, eller om du redan har en utlösare som övervakar en utdatatabell, kan du hoppa över den här delen.
Jobbagent för SQL Server eller Azure SQL Managed Instance
I samma scenario kan du använda SQL Server Agent för SQL Server lokalt och Azure SQL Managed Instance. Även om vissa hanteringsuppgifter skiljer sig åt förblir de grundläggande stegen desamma som att konfigurera en jobbagent för Azure SQL Database.
Nästa steg
Ansluta till SQL Server, Azure SQL Database eller Azure SQL Managed Instance