How to use Azure Elastic Jobs to deal with long-running SQL Stored Procedures triggered from Logic Apps or Power Automate

If you have worked with automating data exports or imports, there is a high chance you have heard about Logic Apps and/or Power Automate. With these tools it is quite easy to insert data from e.g. a csv-file to a SQL database, while also making the desired data manipulations. This is thanks to the built in SQL Server connector’s actions, which allow us to run SQL queries in a database.

However there is a catch – The query execution has a timeout limit of only 110 seconds, which means anything longer will get cut off. Of course it is possible to try to split the query between multiple actions in Logic Apps / Power Automate, but that’s not a great approach either.

Long-running queries are often handled with so called Stored Procedures, which are typically more complex SQL queries that are saved in the SQL database. There is even an action in the SQL connector to trigger them, but unfortunately it has the same 110 second timeout limit as normal SQL queries.

Thankfully there is a solution to this problem called Azure Elastic Jobs.

Why Azure Elastic Jobs?

Azure Elastic Jobs give us the option to trigger long-running Stored Procedures from Logic Apps or Power Automate, without the timeout-related issues. Once the setup has been configured, it works as specified in the image below.

Basically with Elastic Job Agent we create Jobs that can be executed on demand or scheduled against one or many Azure SQL databases. These Jobs are running our desired SQL queries in the form of Stored Procedures.

Now that we have a basic understanding of what we are doing, we can start the installation. Microsoft provides a few good resources for this, first one being the documentation on creating and managing elastic jobs using PowerShell and the second one the Microsoft blog series written by Kate Smith. The first one gives concrete steps to follow for setting up the job database with PowerShell, and the other one a similar tutorial + other useful stuff, like explanation of the core concepts and troubleshooting of common issues.

I assume that you already have an existing resource group that has an Azure SQL server with at least one database (your target database where you want to run Stored Procedures) configured, as this is in my opinion a likely scenario.

Installing Azure Elastic Job Agent with PowerShell

We start by creating the job database, and installing Elastic Job Agent on it with PowerShell. Let’s fire up Windows PowerShell and enter the following code. Remove page breaks before parameters in steps 6 and 7.

# 1. install the latest PackageManagement and PowerShellGet packages
Find-Package PackageManagement | Install-Package -Force
Find-Package PowerShellGet | Install-Package -Force

# 2. Restart your powershell session with administrative access

# 3. Install and import the Az.Sql module
Install-Module -Name Az.Sql
Import-Module Az.Sql
Get-Module Az.Sql

# 4. Install SqlServer module
Install-Module -Name SqlServer

# 5. Sign in to Azure account
Connect-AzAccount

# 6. Create the job database
New-AzSqlDatabase 
-ResourceGroupName "Your existing resource group (DEMO-Azure-Elastic-Jobs)" 
-ServerName "Your existing server name (elastic-jobs-demo)" 
-DatabaseName "Your job database name (job-database)" 
-RequestedServiceObjectiveName "S0" #Job database must be at least S0 capacity

# 7. Install Elastic Job Agent in job database
New-AzSqlElasticJobAgent -Name "Your Elastic Job Agent name (elastic-job-agent)"
-ResourceGroupName "Your existing resource group (DEMO-Azure-Elastic-Jobs)" 
-ServerName "Your existing server name (elastic-jobs-demo)" 
-DatabaseName "Your job database name (job-database)" 

IMPORTANT! In Microsoft’s documentation there are two ways of setting up the Elastic Job Agent, one with PowerShell and one with T-SQL and Azure Portal. As of the writing of this blog post only the PowerShell option seems to be working, as the other method contains broken references.

You should now have a designated job database in your server, with the Elastic Job Agent installed:

Configuring credentials for Job execution in SQL Server

After this step, we need to configure the credentials for Job execution. The different components of authentication are specified in more detail in the links I provided, but basically you only need to do the following steps:

--In the master database
CREATE LOGIN mastercredential WITH PASSWORD='YourPassword1';

CREATE LOGIN jobcredential WITH PASSWORD='YourPassword2';

CREATE USER mastercredential FROM LOGIN mastercredential;

--In the job database
CREATE USER mastercredential FROM LOGIN mastercredential;

--In the target database
CREATE USER jobcredential FROM LOGIN jobcredential;

-- In the job database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourPassword3';

CREATE DATABASE SCOPED CREDENTIAL mastercredential
WITH IDENTITY = 'mastercredential',
SECRET = 'YourPassword1';

CREATE DATABASE SCOPED CREDENTIAL jobcredential
WITH IDENTITY = 'jobcredential',
SECRET = 'YourPassword2';

For strong password generation I like to use this tool by F-Secure.

Creating a Stored Procedure (target database)

After the logins, users and credentials have been created, its time to create the Stored Procedure that we want to run in the target database. For the purpose of this tutorial, we will use a simple example procedure that waits for X minutes before completing.

--In the target database
CREATE PROCEDURE [dbo].[WaitXMinutes]
   @delay char(8)
AS
BEGIN
   SET NOCOUNT ON;
   WAITFOR DELAY @delay
END

Creating a state table (target database)

We will also need to create a state table in the target database for registering parameters and storing inputs:

--In the target database
CREATE TABLE [dbo].[StoredProcedureStatus](
[Id] [int] IDENTITY(1,1) NOT NULL,
[rowversion] [timestamp] NOT NULL,
[jobid] [uniqueidentifier] NOT NULL,
[sql_parameters] [nvarchar](max) NULL,
[start] [datetimeoffset](7) NULL,
[complete] [datetimeoffset](7) NULL,
[code] [int] NULL,
[result] [nvarchar](max) NULL,
[logicapp_parameters] [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]

IMPORTANT! Both the id and rowversion columns are required, if we want to use the built in trigger When an item is modified (V2) in the SQL connector to start a Logic App / Power Automate flow when a Stored Procedure completes.

Defining a target group (job database)

After the state table has been created, we need to create the target group where we want to execute the Stored Procedure. Jobs are by default run against all databases in a server. In our case, we have two databases in our server: The job database and the target database. We only want to run the Stored Procedure in our target database, so we have to specifically exclude the job database from the target group.

-- In the job database
-- 1. Define target group
EXEC jobs.sp_add_target_group 'target_group';

-- 2. Add the server to the target group
EXEC jobs.sp_add_target_group_member 'target_group',
@target_type = 'SqlServer',
@refresh_credential_name='mastercredential',
@server_name='<Your existing server name here>.database.windows.net';

-- 3. Exclude job database from the target group
EXEC [jobs].sp_add_target_group_member
@target_group_name = 'target_group',
@membership_type = 'Exclude',
@target_type = 'SqlDatabase',
@server_name = '<Your existing server name here>.database.windows.net',
@database_name = '<Your job database name here>';

Creating a Job to trigger Stored Procedure (job database)

Next we will create the actual Job which will trigger the Stored Procedure in our target group:

-- In the job database
-- 1. Add the job
EXEC jobs.sp_add_job 
   @job_name='WaitXMinutes',
   @description='Executes Wait3Minutes stored procedure in target database.',
   @enabled = 1;

-- 2. Add the steps for the job
EXEC jobs.sp_add_jobstep
   @job_name='WaitXMinutes',
   @step_name= 'Parameterize WaitXMinutes',
   @step_timeout_seconds = 30,
   @command= N'
      IF NOT EXISTS(SELECT [jobid] FROM [dbo].[StoredProcedureStatus]
         WHERE jobid = $(job_execution_id))
         THROW 50400, ''Failed to locate call parameters (Step1)'', 1',
   @credential_name='jobcredential',
   @target_group_name='target_group';

EXEC jobs.sp_add_jobstep
   @job_name='WaitXMinutes',
   @step_name='Execute WaitXMinutes',
   @command=N'
      DECLARE @timespan char(8)
      DECLARE @callparams NVARCHAR(MAX)
      SELECT @callparams = [sql_parameters] FROM [dbo].[StoredProcedureStatus]
         WHERE jobid = $(job_execution_id)
      SET @timespan = @callparams
      EXECUTE [dbo].[WaitXMinutes] @delay = @timespan', 
   @credential_name='jobcredential',
   @target_group_name='target_group';

EXEC jobs.sp_add_jobstep
   @job_name='WaitXMinutes',
   @step_name='Complete WaitXMinutes',
   @command=N'
      UPDATE [dbo].[StoredProcedureStatus]
         SET [complete] = GETUTCDATE(),
            [code] = 200,
            [result] = ''Success''
         WHERE jobid = $(job_execution_id)',
   @credential_name='jobcredential',
   @target_group_name='target_group';

We also need to grant permission to edit StoredProcedureStatus -table and execute the WaitXMinutes Stored Procedure to the jobcredential user in the target database.

-- In the target database
GRANT SELECT, INSERT, UPDATE, DELETE ON StoredProcedureStatus TO jobcredential;
GRANT EXECUTE ON OBJECT::dbo.WaitXMinutes TO jobcredential;

Creating a Logic App to trigger Job

Now its time to configure the Logic App that triggers the Job. The Logic App contents are quite simple, first we start the Job execution in the job database with the following query:

And the same as code:

DECLARE @jid UNIQUEIDENTIFIER
DECLARE @result int
EXECUTE @result = jobs.sp_start_job'WaitXMinutes', @job_execution_id = @jid OUTPUT
if @result = 0
	SELECT 202[Code], 'Accepted'[Result], @jid[JobExecutionId]
ELSE
	SELECT 400[Code], 'Failed'[Result], @result[SQL Result]

And after that we provide the parameters for our Stored Procedure by inserting them in the StoredProcedureStatus table that is located in the target database. At this time we can also input the start-time of our job execution, as well as give additional parameters for a Logic App / Power Automate flow that we might want to trigger after the stored procedure completes.

Testing out the solution

Great! We should now have everything ready to run Stored Procedures that take longer than 110 seconds from Logic Apps or Power Automate. Here’s how it works in practice:

1. Start the Job by running the Logic App, and give the Stored Procedure a desired starting value as a parameter. In our case we want the stored procedure to wait 3 minutes before completing, so we need to type in 00:03:00 in the sql_parameters field within the Logic App, as specified in the image above.

2. Once the Logic App has been successfully executed, you can check that the job has been started from the Elastic Job Agent in the Azure Portal. You can also se that a new row has been added in StoredProcedureStatus -table.

-- In the target database:
select * from StoredProcedureStatus;

3. The Stored Procedure should run as expected, and after three minutes the state table should update with code 200 and result Success.

And you should be able to see successful Job execution also in the Azure Portal

IMPORTANT! If for some reason you get a timeout or a failed execution in your Job, I recommend checking the contents of job_task_executions table and job_executions view in the job database. In the task execution table especially the fields message and exception have proven to be very useful in my troubleshooting.

-- In the job database
select * from jobs_internal.job_task_executions;
select * from jobs.job_executions;

Optional step: Running a Logic App / Power Automate flow based on the Stored Procedure output

We also have the option to run Logic Apps or Power Automate flows based on the output of a Stored Procedure. This is very handy, e.g. in a situation where we want to delete or move a file once it has been processed to a database. We can feed additional parameters to the Logic App by using the logicapp_parameters field in the StoredProcedureStatus -table. This is useful when parameters need to be given in a different format to the Stored Procedure and Logic App, e.g. sql_parameters might be a complete file path with quotation marks, and logicapp_parameters only the file name.

Let’s now create a second Logic App that uses the SQL connectors When an item is modified (V2) -trigger.

And if we do the previous steps again, the new Logic App should trigger once the Stored Procedure has completed. I noticed that the trigger does not start the execution right after the Stored Procedure has finished, even though I have set it to check for new items 6 times a minute. So you can expect a little bit of delay on top of the runtime.

Final thoughts

In my opinion, the ability to run complex SQL queries without timeout-related problems directly from Logic Apps and Power Automate is a very useful feature, for which there are plenty of use cases. Although the configuration and deployment of Azure Elastic Jobs could be little bit simpler. If you need help with setting up a solution with Elastic Jobs, Stored Procedures and Logic Apps, you can reach us at hello@forwardforever.com.

References used in this post

Elastic Jobs in Azure SQL Database – What and Why – Microsoft Tech Community

Create an Elastic Job agent using PowerShell (preview) – Azure SQL Database | Microsoft Docs

Handle long-running stored procedures in the SQL connector – Azure Logic Apps | Microsoft Docs

sql server – Elastic job agent immediately times out on a job in a Logic App – Stack Overflow

Consultation by Gianluca D’Ardia (Technical Consultant, Microsoft)

Interested in reading our latest Power Platform blog posts?

You’re welcome to subscribe to our email newsletter: Forward Forever Monthly. Max 1 email per month, with a curated list of latest insights and articles on Power Apps, Power Automate, Power BI.

Leave a Reply