Automating tasks on Azure SQL Database using Azure Elastic Jobs

When using the Azure SQL Database, since we don’t have access to the instance and the SQL Server Agent, we`re gonna need to use a new service available in the Azure: Elastic Jobs, a job Scheduling services that execute custom jobs on one or many databases in Azure SQL Database.
The service is in the preview mode,so don’t expect to have full support on this, but if you need to execute maintenance tasks or T-SQLs on a schedule, it’s the most reliable option at the moment.

(https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview?view=azuresql)

1 – Create a SQL database to host the agent

For this to work, first we need to create a database to host our agent db separated from our target database.
I created a database named sqlagent in a new host to be the repository.

2 – Create an Elastic Job Agent

With the database created let’s create our Elastic Job Agent.
Here we`ll choose a name for the agent / select the subscription and select the database to be used:

Resource created:

With this done, now we can configure our first job to be scheduled.

But first, we have to execute a few steps in our agent and target databases:

3 – Create credentials

Connect to the SQLAGENT database and run the following commands:

— Create a database master key if one does not already exist, using your own password.

CREATE MASTER KEY ENCRYPTION BY PASSWORD=’xxxxxxxxx‘;
— Create two database scoped credentials.

— The credential to connect to the Azure SQL logical server and execute the jobs
CREATE DATABASE SCOPED CREDENTIAL agent_credential WITH IDENTITY = ‘agent_credential‘,
SECRET = ‘xxxxxxx‘;
GO
— The credential to connect to the Azure SQL logical server, to refresh the database metadata, if there’s a change in the database configuration, for instance: a new database is added to the server.

CREATE DATABASE SCOPED CREDENTIAL master_credential WITH IDENTITY = ‘master_credential‘,
SECRET = ‘xxxxxxxx‘;
GO

4 – Create logins and users in the target database

Now we need to create the logins in the database we`re going to use to run our jobs. It’s very important to keep the same password used to create our scoped credential in the agent database.
Connect to the target database:

In the master database :

create login agent_credential with password=’xxxxxxxxxx‘;
create login master_credential with password=’xxxxxxxxxxx‘;
create user master_credential for login master_credential;

In the labdatabasejobs database:

create user agent_credential for login agent_credential;

And here I’ll grant dbowner so the user can have full access on the database:

EXEC sp_addrolemember N’db_owner’, N’agent_credential’
GO

If I had more databases in this server, I would need to create the agent_credential in all of them.

5 – Create a target group

In the SQLAGENT database we need to create a target group and add the databases that we`ll use to run our job.
We can add all the databases hosted in the server at once, then, even if new ones are created after the setup, they will be added automatically (with our master_credential updating this), or we can include only the database where the job needs to be executed.

— Connect to the job database specified when creating the job agent

— Add a target group containing server(s)

EXEC jobs.sp_add_target_group ‘DBGROUP1’;

– If we want to add all the databases:

— Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = ‘DBGROUP1’,
@target_type = ‘SqlServer’,
@refresh_credential_name = ‘master_credential’, –credential required to refresh the databases in a server
@server_name = ‘labdatabasejobs.database.windows.net’;

– If we want to add only one database:

EXEC jobs.sp_add_target_group_member
@target_group_name = ‘DBGROUP1’,
@membership_type = ‘Include’,
@target_type = ‘SqlDatabase’,
@server_name = ‘labdatabasejobs.database.windows.net’,
@database_name = ‘labdatabasejobs’;

You see that by choosing to add a single database we don`t use the @refresh_credential_name parameter, since it doesn`t matter how many databases are in the server.

– If we want to exclude one database from the target:

EXEC jobs.sp_add_target_group_member
@target_group_name = ‘DBGROUP1’,
@membership_type = ‘Exclude’,
@target_type = ‘SqlDatabase’,
@server_name = ‘labdatabasejobs.database.windows.net’,
@database_name = ‘labdatabasejobs’;

–Views to check the recently created target group and target group members

SELECT * FROM jobs.target_groups WHERE target_group_name=’DBGROUP1;
SELECT * FROM jobs.target_group_members WHERE target_group_name=’DBGROUP1′;

6 – Create a job

For this lab I created the following table in the target database:

And now I`m gonna create a job to insert data in this table in a five minute interval daily:

–Connect to the job database

–Add job for insert

EXEC jobs.sp_add_job @job_name = ‘InsertTableTest‘, @description = ‘Job to insert current date‘;

— Add job step
EXEC jobs.sp_add_jobstep @job_name = ‘InsertTableTest‘,
@command = N’insert into test values (getdate());‘,
@credential_name = ‘agent_credential‘,
@target_group_name = ‘DBGROUP1‘;

– With the job created, I`ll add a schedule:

EXEC jobs.sp_update_job
@job_name = ‘InsertTableTest‘,
@enabled=1,
@schedule_interval_type = ‘Minutes‘,
@schedule_interval_count = 5;

Now let’s see our job created and the execution log:

In the portal we can also see information about the job:

That’s it.
Now you can run maintenance routine tasks / Run T-SQLs on a schedule in a single Azure SQL Database without the SQL Agent.
You can do the administration by T-SQL and verify in the portal the status of the jobs.
We hope that soon this service will be in general availability