Create and monitor elastic agent job for Azure SQL database.

---

This blog demonstrates the detailed steps needed to create an SQL Database backup in a blob container. To begin, with you need to list down the available backups, and restore them from a blob container to a Prem SQL Database.

Introduction

Azure SQL databases do not have an inbuilt SQL server agent like an on-prem SQL server. Azure has introduced an elastic job agent to enhance this capability to execute serverless code using a scheduler called elastic job agent.

Let us have a look at the creation and configuration of the elastic job using the below steps

1. Create an Azure SQL Job agent server and job database
2. Create Azure SQL Target server and databases
3. Create Elastic Job Agent and configure job database
4. Create Master key and database scope credential
5. Create Target Group, Elastic Job, configure and monitor job.

Create Azure SQL Job agent server and job database Go to the azure portal and Create an Azure SQL job server and database a DB with pricing tier standard S0 or higher in it. You will see DB like below after its creation.

Create Azure SQL Target server and databases

Go to the Azure portal again and Create an Azure SQL Target server and 2 databases in it. One with standard S0 and one with basic tier. Target Databases do not need to standard S0 tier here.

Create Elastic Job Agent and configure job database

Go to Azure marketplace and type “elastic agent.” create an elastic agent and chose a database where job agent metadata will be created. It will create default tables and sp’s in configured elastic agent database.

Default tables created for the Job agent database are below

Create Master key and database scope credential Use the below commands to create a master key in the job database and create a database scope credential. This credential is required to execute queries on Target databases.

Also, create a master key and scoped credential using the same command on Target databases

• a) Create Target Group

Use the below commands to create a Target group and add a server where the job needs to be executed. Also, validate if it got added successfully using internal job tables.

• b) Create table and statistics on destination DBs using the below command.

• c) c) Create an elastic job on the Job server

Use sp_add_job stored procedure on the job server DB to create a job and add a step to it.

• d) d) Job execution: Start the job using sp_start_job stored procedure also monitor the execution using job_executions table.

• e) Job monitoring: You will get success logs using the job_executions table or from the elastic job agent like below.

Conclusion:

This blog tells us we can create an elastic job on the on-Job server. We can create a target group where we can add target servers for which we can create elastic jobs. We can also exclude the database on a target server where we do not want to configure and execute the job. We can monitor the elastic job through the elastic job agent or through the system catalog tables.

Learn about Rackspace Managed SQL Databases.

Learn about Rackspace Database Services.

Use the Feedback tab to make any comments or ask questions. You can also start a conversation with us.