The content of this blog post was originally presented at Data:Scotland 2023 in the session ‘How Can I Run My Agent Jobs In Azure SQL’

It’s the 21^st century. On-Prem is dead. Cloud is king. All workloads must now run on someone elses hardware.

We all know that’s not true, but more and more workloads are being migrated into the various cloud providers and particularly into Platform-as-a-Service (PaaS) offerings – before we go any further, just take a moment to remind yourself about the different flavours of Microsoft SQL Server currently available in Azure:

SQL Server on Azure VM – the SQL Server we all know and love; installed on a virtual machine (usually running Windows, but not always) with the full range of features we’ve become accustomed to. As it runs on a virtual machine that you manage from an OS and Software perspective, this is commonly referred to as Infrastructure-as-a-Service (IaaS).

Azure SQL Managed Instance – a half way house between the options above and below, Azure SQL Managed Instance is a PaaS offering that gives you an almost fully fledged SQL Server instance which almost has the full feature set available if installing on a VM or a physical host.

Azure SQL Server/Database – a full PaaS offering, built entirely using the concept of contained databases on shared hardware. If you’ve used contained databases before you’ll be familiar with some of the limitations, but to pick a few there’s no concept of cross-database queries or (crucially for this topic) a SQL Server Agent

Both SQL Server on Azure VM and Azure SQL Managed Instance provide you with SQL Server Agent and therefore the capability to run scheduled tasks against your databases, so when we’re talking about being able to run jobs we’re only considering Azure SQL Database as needing guidance – some of the suggestions  in the following paragraphs can also apply to all these options of SQL Server, but perhaps not as necessary.

We’re going to provide you with four options for how you might be able to still run your favourite SQL Agent Jobs against an Azure SQL Database, each of which come with their own advantages and disadvantages – one not mentioned is Data Factory, sometimes referred to as SSIS in the cloud, and this is because we’re trying to focus on some options that may be more comfortable to people who have never built an SSIS package before.

1. Azure Elastic Jobs
2. Azure Logic Apps
3. Azure Function Apps
4. Azure Automation Runbooks

Elastic Jobs

Azure Elastic Jobs are the ‘official’ Microsoft solution to the problem of being able to run jobs against Azure SQL Databases, however it’s still In Preview – as such it can’t be recommended for use in Production.

It consists of 4 components:

• Elastic Job Agent
  A serverless Azure resource that performs the actions defined in the job
• Elastic Job Database (which needs to itself be an Azure SQL Database in the S0 tier or higher)
  MSDB in the sky, stores the definitions of the jobs and target groups
• Target Group
  A group of databases that a Job should be run against
• Job
  The job itself!

Despite being Azure native currently the only available authentication method is SQL Authentication, which would be fine if Microsoft’s Best Practice recommendations for Azure SQL only recommend SQL Authentication in the case of legacy applications…and this one is still In Preview.*

The jobs themselves:

• Can be manged via Powershell or directly in the Elastic Job Database using T-SQL; however these are the only options available, there’s no GUI for this in either the Azure Portal or elsewhere.
• Can be scheduled, or initiated programmatically
• Can, while in preview, have 100 concurrent instances
• Can’t run against an Azure SQL Database that uses a Private Endpoint i.e. there’s no way to integrate Elastic Jobs with a vNET currently*

*Update: 9th November 2023

The latest preview release for Azure SQL has included a number of improvements for Elastic Jobs, including support for Microsoft Entra ID authentication and Private Links, Azure Alerts on job execution and enhanced Azure Portal capabilities to track and monitor your jobs.

Azure Logic Apps

Azure Logic Apps are the best solution if you prefer a low/no-code approach to creating your jobs – apart from the T-SQL you’ll need to write, the entire job can be created within the Azure Portal or VS Code.

Logic Apps consist of ‘Actions’ and ‘Connectors’ – actions do things, connectors connect to things. Sounds fairly ‘logical’. A typical Logic App starts with it’s entry point, usually a scheduled reoccurrence or a webhook, from which you’ll use a connector to initiate a connection to another resource and, in most cases, carry out some specific task which returns some response and then an action (or multiple actions) that use that response to perform some other task.

The SQL Connector which comes as standard will connect to any accessible SQL Server Instance and can be used to perform a number of actions, including running a specific query or instantiating a stored procedure – including passing any necessary parameters. It can use either SQL Authentication or Azure AD Authentication, which also means you can use a Service Principal/App Registration or Managed Identity in a workflow rather than any one accounts credentials.

You can pay for Logic Apps in a few different ways; you can use the Consumption plan, which charges you only for each run of the Logic App and its constituent parts (there will be a table later with pricing for all of the options presented), or you can purchase a Standard plan where you have the equivalent of an App Service Plan that your Logic Apps run in and you aren’t charged for individual actions (still charged for connectors though). Standard plans are by far the more expensive option, but because you have dedicated compute resources you don’t run into any of the issues we’ve faced with Consumption plans when scheduled runs…don’t run.

You also require a Standard plan if you intend to access any resources integrated (and locked down to) a vNET as only it supports vNET Integration and Private Endpoints

It’s worth mentioning that there are ‘stateless’ and ‘stateful’ workflows, and that each of these have different max runtimes – a stateless workflow has a max runtime of 5 minutes before it times out; a stateful workflow can run for 90 days.

Azure Function Apps

Azure Function Apps are great for anyone who knows a coding language that isn’t Powershell, because it supports a fair few more. At time of writing, you can write Function Apps in C#, JavaScript, F#, Java (short for JavaScript, right?), Python, Powershell and TypeScript. They’re also great if you want to parameterise everything – Logic Apps have a lot of power there, but you can’t dynamically change the parameters in your SQL Connector after you’ve built it. You can in a Function App. You could conceivably create a single Function App that accepts a JSON payload that defines the stored procedure you want to run and the parameters to pass through it, and the Function App could dynamically build that.

Function Apps are the easiest of these options to build using CI/CD pipelines, including the power you have there with regards to testing – our experience is with Azure DevOps, but we’re sure your CI/CD pipeline provider of choice will have similar functionality. It also supports all authentication types (do you see a pattern emerging), and if you’re writing the Function App in C# or Powershell then you can use the SMO .NET Library to interact with your SQL instances.

You can start a Function App in a number of ways; as part of a Queue, an Event Grid, via a HTTP Webhook or a good ol’ Timer. Similar to Logic Apps, you can run them in Consumption plans (with the same drawbacks), you can attach them to an App Service Plan or you can use a Premium Function App Plan.

Azure Automation Runbooks

Azure Automation Runbooks are our personal favourite, but that’s because we do everything in Powershell (this blog would have been written in Powershell if it were possible) – they support Powershell 5.1, 7.1 and 7.2 (although the 7.x versions are in preview) and Python, and you can automatically sync on commit Runbooks from your Azure DevOps/GitHub repository of choice. You can also install Powershell Modules into the Azure Automation Account hosting the runbooks and therefore can use something like DBATools to interact with your databases via an Automation Runbook; if you’d prefer not to, then the SMO .NET Library is also available here.

For authentication you can use (you guessed it) all available authentication types, and the runbooks can be started using a webhook or a schedule – a note on the schedules, the smallest unit of time currently is an hour which can make managing runbooks that need to run more frequently a little cumbersome. You could, if you want to, also have Runbooks run against on-premise SQL Server instances via the use of a Hybrid Worker (essentially a non-Azure machine that runs an agent which connects it back to Azure and allows you to perform actions against machines in the remote network).

There is an allocation of 500 minutes of runtime per month for free, as well as 744 hours of watcher time (watchers are used if you’re watching file locations for changes before performing an action against the changed files), and although you can access the Runbooks themselves via a Private Link you can’t access resources behind a Private Link…you would need to use a convoluted process whereby you make an Azure VM in the affected vNETs a Hybrid Worker and run jobs via that.

Pricing

As promised, the prices for each of the options we’ve set out are as follows:

Elastic Jobs:

Completely free for the Jobs, Target Groups and Agent while In Preview, however you’ll need to pay the going rate for the Database

Logic Apps:

For a Consumption Plan

For a Standard Plan

Function Apps:

For a Consumption Plan

For a Standard Plan

You can also add Function Apps to App Service Plans, and it will be charged at the App Service Plan rate.

Automation Accounts/Runbooks:

So what should you choose?

Not to avoid giving anyone a firm answer, but it’s going to depend. In our opinion, you should use:

• Elastic Jobs if you’re testing against a Development environment and want to try out what is (nominally) the latest option – we can’t recommend for Production use while it’s still In Preview
• Logic Apps if you work best with a WYSIWYG editor and don’t have a strong non-TSQL background
• Function Apps if you have some Software Development under your belt and need to be able to access resources using vNET Integration with the most cost effective option
• Automation Runbooks if you’re very familiar with Powershell or Python, and want to be able to import modules and packages that aren’t included by default.

It also goes without saying that any of these options are a whole new way of thinking about SQL Agent jobs. We were only half joking at the start when we said Cloud is King now…