Contrôler les jeux de résultats SQL volumineux et les délais d’expiration pendant les procédures stockées dans les flux de travail pour Azure Logic Apps

S’applique à : Azure Logic Apps (Consommation + Standard)

Pour automatiser plus facilement les tâches métier qui fonctionnent avec des bases de données SQL, votre flux de travail peut utiliser des opérations de connecteur SQL Server, ce qui fournit de nombreuses fonctionnalités principales pour les flux de travail à utiliser dans Azure Logic Apps.

Dans certaines situations, votre flux de travail peut avoir à gérer des jeux de résultats volumineux. Ces jeux de résultats peuvent être si volumineux que les opérations de connecteur SQL Server ne retournent pas tous les résultats en même temps. Dans d’autres situations, vous souhaiterez peut-être mieux contrôler la taille et la structure de vos jeux de résultats. Pour organiser les résultats de la façon souhaitée, vous pouvez créer une procédure stockée.

Par exemple, lorsqu’une action de connecteur SQL Server obtient ou insère plusieurs lignes, votre flux de travail peut itérer au sein de ces lignes à l’aide d’une boucle Until qui fonctionne dans ces limites. Si votre flux de travail doit gérer des milliers ou des millions de lignes, vous souhaitez réduire les coûts résultant des appels d’action du connecteur SQL Server à la base de données SQL. Pour plus d’informations, consultez Gérer les données en bloc à l’aide du connecteur SQL.

Ce guide montre comment contrôler la taille, la structure et les délais d’expiration lors du traitement de jeux de résultats volumineux à l’aide des actions du connecteur SQL Server .

Limite du délai d'exécution de la procédure stockée

Le connecteur SQL Server a une action d’exécution de procédure stockée avec une limite de délai d’expiration inférieure à deux minutes. Certaines procédures stockées peuvent prendre plus de temps que cette limite, ce qui provoque une erreur de délai d’attente 504 . Parfois, les processus de longue durée sont codés en tant que procédures stockées explicitement à cet effet. En raison de la limite de délai d’expiration, l’appel de ces procédures à partir d’Azure Logic Apps peut créer des problèmes.

Les opérations de connecteur SQL Server ne prennent pas en charge un mode asynchrone de manière native. Pour contourner cette limitation, simulez ce mode à l’aide des éléments suivants :

• Déclencheur d’achèvement SQL
• Requête directe SQL native
• Table d’état
• Travaux côté serveur

Par exemple, supposons que vous disposiez de la procédure stockée de longue durée suivante. La procédure dépasse la limite de délai d’expiration pour achever l’exécution. Si vous exécutez cette procédure stockée à partir d’un flux de travail à l’aide de l’action de connecteur SQL Server nommée Exécuter la procédure stockée, vous obtenez l’erreur http 504 Gateway Timeout .

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

Au lieu d’appeler directement la procédure stockée, vous pouvez exécuter de façon asynchrone la procédure en arrière-plan à l’aide d’un agent de travail. Vous pouvez stocker les entrées et sorties dans une table d’état que vous pouvez ensuite accéder et gérer via votre flux de travail. Si vous n’avez pas besoin des entrées et sorties, ou si vous écrivez déjà les résultats dans une table de la procédure stockée, vous pouvez simplifier cette approche.

Pour exécuter de façon asynchrone la procédure en arrière-plan avec l’agent de travail pour SQL Server basé sur le cloud, suivez les étapes de création et d’utilisation de l’agent de travail élastique Azure pour Azure SQL Database.

Pour SQL Server local et Azure SQL Managed Instance, créez et utilisez l’agent SQL Server à la place. Les étapes fondamentales restent identiques à la configuration d’un agent de travail pour Azure SQL Database.

Créer un agent de travail pour Azure SQL Database

Pour créer un agent de travail qui peut exécuter des procédures stockées pour Azure SQL Database, créez et utilisez l’agent de travail élastique Azure. Toutefois, avant de pouvoir créer cet agent de travail, vous devez configurer les autorisations, les groupes et les cibles, comme décrit dans la documentation de l’Agent de travail élastique Azure. Vous devez également créer une table d’état de prise en charge dans la base de données cible, comme décrit dans les sections suivantes.

Pour créer l’agent de travail, effectuez cette tâche dans le portail Azure. Cette approche ajoute plusieurs procédures stockées à la base de données utilisée par l’agent, également appelée base de données de l’agent. Vous pouvez ensuite créer un agent de travail qui exécute votre procédure stockée dans la base de données cible et capture la sortie lorsque vous avez terminé.

Créer une table d’état pour inscrire des paramètres et stocker des entrées

Les tâches SQL Server Agent n'acceptent pas les paramètres d'entrée. Au lieu de cela, dans la base de données cible, créez une table d’état dans laquelle vous inscrivez les paramètres et stockez les entrées à utiliser pour appeler vos procédures stockées. Toutes les étapes du travail de l’agent s’exécutent sur la base de données cible, mais les procédures stockées du travail s’exécutent sur la base de données de l’agent.

Pour créer la table d’état, utilisez ce 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]

Voici l’apparence de la table obtenue dans SQL Server Management Studio (SMSS) :

Pour garantir de bonnes performances et vérifier que l’agent de travail peut trouver l’enregistrement associé, la table utilise l’ID d’exécution du travail (jobid) comme clé primaire. Si vous le souhaitez, vous pouvez également ajouter des colonnes individuelles pour les paramètres d’entrée. Le schéma décrit précédemment peut gérer plus généralement plusieurs paramètres, mais est limité à la taille calculée par la NVARCHAR(MAX) fonction.

Créer une tâche de niveau supérieur pour exécuter la procédure stockée

Pour exécuter la procédure stockée de longue durée, créez cet agent de travail de niveau supérieur dans la base de données de l’agent :

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

Ajoutez des étapes à la tâche qui paramètrent, exécutent et terminent la procédure stockée. Par défaut, une étape de travail expire après 12 heures. Si votre procédure stockée a besoin de plus de temps ou si vous souhaitez que la procédure expire plus tôt, vous pouvez remplacer le step_timeout_seconds paramètre par une autre valeur spécifiée en secondes. Par défaut, une étape comporte 10 nouvelles tentatives intégrées avec un délai d’interruption entre chaque nouvelle tentative, que vous pouvez utiliser à votre avantage.

Voici les étapes à ajouter :

1. Attendez que les paramètres apparaissent dans le LongRunningState tableau.

   Cette première étape attend que les paramètres s'ajoutent dans LongRunningState la table, ce qui se produit peu après le démarrage de la tâche. Si l’ID d’exécution du travail (jobid) n’est pas ajouté à la LongRunningState table, l’étape échoue simplement. Le délai d’attente de l’interruption ou de nouvelle tentative par défaut est le suivant :

   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. Interrogez les paramètres de la table d’état et transmettez-les à la procédure stockée. Cette étape exécute également la procédure en arrière-plan.

   Si votre procédure stockée n’a pas besoin de paramètres, appelez directement la procédure stockée. Sinon, pour passer le @timespan paramètre, utilisez le @callparamsparamètre , que vous pouvez également étendre pour passer d’autres paramètres.

   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. Terminez le travail et enregistrez les résultats.

   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'
   

Démarrez le travail et transmettez les paramètres

Pour démarrer le travail, utilisez une requête native directe avec l’action Exécuter une requête SQL et envoyez immédiatement les paramètres du travail dans la table d’état. Pour fournir une entrée à l'attribut jobid dans la table cible, Azure Logic Apps ajoute une boucle Pour chaque qui itère à travers la sortie de la table à partir de l’action précédente. Pour chaque ID d’exécution du travail, exécutez une action Insérer une ligne qui utilise la sortie de données dynamiques nommée ResultSets JobExecutionId pour ajouter les paramètres du travail à décompresser et passer à la procédure stockée cible.

Une fois la tâche terminée, la table LongRunningState est mise à jour. À partir d’un autre flux de travail, vous pouvez déclencher sur le résultat à l’aide du déclencheur nommé Lorsqu’un élément est modifié. Si vous n’avez pas besoin de la sortie ou si vous avez déjà un déclencheur qui surveille une table de sortie, vous pouvez ignorer cette partie.

Créer un agent de travail pour SQL Server ou Azure SQL Managed Instance

Pour SQL Server local et Azure SQL Managed Instance, créez et utilisez SQL Server Agent. Par rapport à l’agent de travail basé sur le cloud pour Azure SQL Database, certains détails de gestion diffèrent, mais les étapes fondamentales restent les mêmes.

Étape suivante

• Se connecter à des bases de données SQL à partir de flux de travail dans Azure Logic Apps