Ranga Babu
creating elastic job agent in Azure

Azure SQL – Elastic Job Agent

June 24, 2019 by

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.

  1. Creating an Elastic Job agent
  2. Creating credentials on the Agent database in Azure SQL
  3. Creating a target group and members
  4. Creating logins on target master and user databases
  5. Creating job and job steps
  6. 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.

elastic job agent in Azure

On Elastic Job agents page, click on Add.

creating elastic job agent

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).

creating elastic job agent in Azure

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.

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.

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.

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.

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.

Scheduling the job

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

To check the status of the job, query the view “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.

Azure SQL - elastic job status

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

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

  1. ‘Once’
  2. ‘Minutes’
  3. ‘Hours’
  4. ‘Days’
  5. ‘Weeks’
  6. ‘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.

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.

procedures to manage elastic jobs in Azure

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.

Ranga Babu
Jobs, SQL Azure

About Ranga Babu

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

168 Views