How to create a Scheduled Elastic Job in Azure SQL easily

Posted by on

A common task when you work with SQL Server is to create scheduled Jobs to execute stored procedures, functions, etc. In the Azure world, this is not straightforward since Azure SQL lacks the famous SQL Server Agent. So, you have two "easy" options for scheduling jobs:

  1. Create an Azure Function with some timer trigger or an Azure Bach Jobs. This can be more customizable, but cumbersome since you cannot edit your code without redeploying. This is easier in AWS.
  2. Create an Azure Elastic Job. This is the official solution. However, it lacks some features (in 2024) like allowing it to run:
    • daily jobs between certain hours only.
    • on certain days like Mondays, Wednesdays, and Sundays only.
    • on certain months like January or March only.
      • If you need any of these scenarios, you must add some SQL code to validate them.

Now, focusing on the Azure Elastic Jobs, the first part is to search for Elastic Job agents.



You click + Create:


Then, you configure your DB location:

Choose your Tier:

And click Review + create:


After you have created your Elastic Job agent, go to SQL Server Management Studio and log in with the Database Super Admin (Microsoft Entra admin):


And run the following query to create the Azure Credential:

USE MASTER
-- Create a db master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='YOUR_PASSWORD';

-- Create a database scoped credential.
CREATE DATABASE SCOPED CREDENTIAL YOUR_AZURE_CRED WITH IDENTITY = 'YOUR_SQL_ALIAS',
    SECRET = 'YOUR_PASSWORD';

ALTER AUTHORIZATION ON DATABASE SCOPED CREDENTIAL::YOUR_AZURE_CRED TO
jobs_resource_manager

-- Change to your DB.
USE YOUR_DB_NAME

CREATE USER YOUR_SQL_ALIAS
    FOR LOGIN YOUR_SQL_ALIAS
    WITH DEFAULT_SCHEMA = YOUR_PROC_SCHEMA
GO

-- It failed to recognize the password when I didn't add it again.
ALTER LOGIN YOUR_SQL_ALIAS WITH PASSWORD = 'YOUR_PASSWORD';

ALTER ROLE db_datareader ADD MEMBER YOUR_SQL_ALIAS;
ALTER ROLE db_datawriter ADD MEMBER YOUR_SQL_ALIAS;

Where,

  • YOUR_PASSWORD must be the same for your user and the App Credentials for Azure
  • YOUR_AZURE_CRED is the name that will appear in the Azure Portal.
  • YOUR_SQL_ALIAS is the SQL User name inside the DB.
  • YOUR_DB_NAME is your database name.
  • YOUR_PROC_SCHEMA, the location of your stored procedures.

After you have configured this, go to your Elastic Job agent and search for Target Groups:


Then, click + Create:


And give a name to your Target Group:


The next part is to create a job, you go to the section, Job definitions:


And click + Create:


Then, you will have a side menu that looks like this:

In this section, there are a couple of important parts:
  • Schedule Interval Type that can be Once or Recurring.
  • If you choose Recurring (which will be more logical for a job), you have a tricky part, what should you add to the Schedule Interval (ISO8601)?
This is an undocumented part where the provided link is wrong (May 28, 2024). For this section, it expects values like these ones:
    • PT10M, where,
      • P is the period.
      • T is the time section.
      • 10 is for the units.
      • M is minutes but can also be S for seconds or H for hours.
    • P1D, where,
      • B is the period
      • 1 is for the units.
      • D is days but can also be M for months or Y for years.

After you have created your job, you can create a step by clicking + Add Job step:


After that, you will see a screen where you will need to provide some details:

  • Step name.
  • SQL Script, the expected scripts to run.
  • Credential, the Azure credential from the script at the beginning of the article.
  • Target group, the one created before.
Then you click on Review + create.

This is all you need to run some jobs using Azure Elastic Jobs.

Comments

Post a Comment