Azure SQL — Elastic Job Agent to configure maintenance jobs for ALL Azure Single pool database or elastic pool

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 below septs need o follow the follow configure Elastic job agent

1. Create and configure the 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

Create and configure the 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 ,As per our polices provide naming convention to job name.

Creating database scoped credentials on the agent database:

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 “jobuser” is used to connect the Azure SQL target database and execute the script

CREATE MASTER KEY ENCRYPTION BY PASSWORD=’Password@111';
CREATE DATABASE SCOPED CREDENTIAL Agentuser WITH IDENTITY = ‘JobAgent’,
SECRET = ‘Password@111’;
GO
CREATE DATABASE SCOPED CREDENTIAL MasterCred WITH IDENTITY = ‘MasterUser’,
SECRET =’Password@111';
GO

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, master database to create the logins .

CREATE LOGIN MasterUser
WITH PASSWORD = ‘Password@111’; 
 
CREATE LOGIN JobAgent
WITH PASSWORD = ‘Password@111’; 
 
CREATE USER MasterUser
FROM LOGIN MasterUser

Now on each target database to create a user and provide he required permission to run the scrip.

create user JobAgent
from login JobAgent
 
ALTER ROLE db_owner 
ADD MEMBER [JobAgent] ; 
GO

Creating a target group and members

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

EXEC jobs.sp_add_target_group ‘DatabaseGroup1’
GO
 
EXEC jobs.sp_add_target_group_member
‘DatabaseGroup1’,
@target_type = N’SqlDatabase’,
 — @refresh_credential_name=’MasterCred’,
@server_name=’azure2212.database.windows.net’,
@database_name =N’Aj’
GO

Creating job and job steps.

Now on Agent database, execute the following script to create a job and add the steps to the job.

EXEC jobs.sp_add_job @job_name='Rebuild_Index_Test', @description='Improve performance'
 
EXEC jobs.sp_add_jobstep @job_name='Rebuild_Index_Test',
@command=N'Index_Rebuild',
@credential_name='Agentuser',
@target_group_name='DatabaseGroup1'

I have created one SP in Target Database.

Scheduling the job on Elastic agent database

EXEC jobs.sp_update_job
@job_name = ‘index_rebuild’,
@enabled=1,
@schedule_interval_type = ‘weeks’,
@schedule_interval_count = 1,
@schedule_start_time = ‘2022–04–18 01:00:0000’

Manually Start the job for below scrip

EXEC jobs.sp_start_job 'Rebuild_index_test'

To view the jobs history and logs :

SELECT * FROM jobs.job_executions

To view the jobs schedule time:

SELECT * FROM jobs.job_executions

If we can create use same process o create the jobs on ALL other server as well ,but need o create the target_group and target_group_member and also create the Job and schedule into elastic job agent server.

Delete a job and all its job history

EXEC jobs.sp_delete_job @job_name=’Rebuild_index’;

Cancel a job

View all active executions to determine job execution id
SELECT * FROM jobs.job_executions
WHERE is_active = 1 AND job_name = ‘Rebuild_index’
ORDER BY start_time DESC;
GO

Cancel job execution with the specified job execution id
EXEC jobs.sp_stop_job ‘9989780–0089-cdef-0123–789ADCFRMK’;