Azure SQL – Elastic Job Agent

In this article, we will review on elastic job Agent in Azure SQL and how to configure elastic jobs to run scripts on Azure SQL databases. SQL Server Agent is a powerful component that is used to schedule and execute jobs in SQL server. But in Azure, SQL server agent is available only in managed instances and not in the single databases. To schedule and execute jobs on single databases we have a feature called elastic job agent. This feature is used for scheduling and execute jobs on a single database, all the databases in the server, or on all the databases in an elastic pool.

The following are the steps involved in creating an elastic job and scheduling it.

Creating an Elastic Job agent
Creating credentials on the Agent database in Azure SQL
Creating a target group and members
Creating logins on target master and user databases
Creating job and job steps
Schedule the job to execute it on the target database

Let’s go over these steps one by one.

Creating Elastic Job agent

Pre-requisite: we need to have at least one SQL database with a service level objective of S0 or above.

To create an elastic job agent, Navigate to the Azure portal and type elastic job in the search box. Select the Elastic Job agents in the list as shown in the below image.

On Elastic Job agents page, click on Add.

Enter the name of the elastic job agent, choose the subscription. Accept the terms (1). Click on OK (2).

Select the Azure SQL database for an elastic job agent (3). The database with the service level objective of S0 or above is eligible for creating an elastic job agent. Click on Create (4).

Now Elastic job agent creates few objects (table, procedures, etc) on the database you selected. Once the deployment is completed we can proceed with further steps.

Creating database scoped credentials on the agent database

On the Agent database, execute the following script by replacing the password with the password of your choice. These credentials are used to connect with the target database and execute the scripts. Here we have created two credentials in the Agent database.

Credential “JobRun” is used to connect the Azure SQL target database and execute the script. Credential “MasterCred” is used to connect the master database and enumerate all the database in the server if you are using a server or elastic pool as a target type.

CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password@123';

CREATE DATABASE SCOPED CREDENTIAL JobRun WITH IDENTITY = 'JobUser',

SECRET = 'Password@123';

CREATE DATABASE SCOPED CREDENTIAL MasterCred WITH IDENTITY = 'MasterUser',

SECRET ='Password@123';

Creating a target group and members

We need to define the target group and the members. The target type can be a single database, or server, or elastic pool.

On the Agent database, execute the following script by replacing the server name and the database name to create a target group with a single database as the target type. I commented out “@refresh_credential_name” as we are using Azure SQL single database.

EXEC jobs.sp_add_target_group 'DatabaseGroup1'

EXEC jobs.sp_add_target_group_member

'DatabaseGroup1',

@target_type = N'SqlDatabase',

--@refresh_credential_name='MasterCred',

@server_name='rbcus01.database.windows.net',

@database_name =N'TargetDB'

Creating logins on target master and user databases

We need to create logins with the same password which we used while creating credentials on the Agent database.

On the target server, execute the following script on the master database to create the logins.

CREATE LOGIN MasterUser

WITH PASSWORD = 'Password@123';

CREATE LOGIN JobUser

WITH PASSWORD = 'Password@123';

CREATE USER MasterUser

FROM LOGIN MasterUser

Now on each target database execute the following script to create a user. Please note that login must have proper permissions to execute the job successfully. For example, if you are executing a procedure using a job, the login must have sufficient permissions to execute the procedure on the target database. To avoid such permission issues, I add the user to a db_owner role on the target Azure SQL database.

create user JobUser

from login JobUser

ALTER ROLE db_owner

ADD MEMBER [JobUser] ;

Creating job and job steps.

Now on Agent database, execute the following script to create a job and add the steps to the job. Here I am just using a simple print statement in the command. Replace the job name and command and credentials as per your need.

EXEC jobs.sp_add_job @job_name='Sample T-SQL', @description='Print statement'

EXEC jobs.sp_add_jobstep @job_name='Sample T-SQL',

@command=N' print ''hi''',

@credential_name='JobRun',

@target_group_name='DatabaseGroup1'

Scheduling the job

To manually start the job, execute the following script on the Agent database. Replace the job name with your job name.

1	EXEC jobs.sp_start_job 'Sample T-SQL'

To check the status of the job, query the view “job_executions”.

1	select * from jobs.job_executions

You can also view job status in the Azure portal, Navigate All resources. Click on the agent. In agent, page click on Overview.

To schedule a job, execute the following script on the Agent database.

EXEC jobs.sp_update_job

@job_name='Sample T-SQL',

@enabled=1,

@schedule_interval_type='Minutes',

@schedule_interval_count=1

Below are the different interval types we can specify for a job.

‘Once’
‘Minutes’
‘Hours’
‘Days’
‘Weeks’
‘Months’

To add the new step to existing job execute the following script on the Agent database. If you are adding more than one steps you need to specify the step name.

EXEC jobs.sp_add_jobstep @job_name='Sample T-SQL',

@step_name ='Execue procedure',

@command=N' EXEC TM',

@credential_name='myjobcred',

@target_group_name='DatabaseGroup1'

There are few procedures that are created on the Agent database when Elastic Job agent was created. These procedures are used to manage jobs, schedules and target groups.

Conclusion

In this article, we explored how to create an elastic job agent, configure target groups and jobs to run scripts on the Azure SQL target databases. In case you have any questions, please feel free to ask in the comment section below.

Author
Recent Posts

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies