0

Automating Azure SQL database maintenance tasks using Elastic Job agent

In this article, I have described how to automate Azure SQL Database index and statistics maintenance tasks using Azure Elastic Job agent. You can follow the same steps to configure and execute any other job on Azure SQL databases.

Background

Azure SQL database does not have its native SQL server agent unlike SQL Server or Azure SQL Managed Instance. Microsoft has introduced Elastic Job agent managed service for scheduling jobs on Azure SQL Databases. This service is currently in preview and does not currently provide a user interface to configure jobs. However, it provides standard SQL procedures to manage jobs on Azure SQL databases.

Prerequisites

  1. Create a blank Azure SQL database for Elastic Job agent. with the name say “AgentJobs” on Azure Portal. The Minimum recommended compute for this database is S0 (10 DTU). You can create this database under the existing Azure SQL Server.
  2. Create a new Elastic Job agent on Azure Portal. Choose the database created in the above step(1).
  3. Download the latest AzureSQLMaintenance procedure code by Yochanan_MSFT. This procedure has features to update statistics & indexes. Read more about the execution options on MSDN. We need to deploy this procedure code on all the target databases where we need to automate database maintenance.

Process

If you have followed all the prerequisites, execute the below scripts to automate the maintenance job on Azure SQL databases. You would need to log in to Azure SQL Server with admin credentials using SSMS.

Master database

Execute the below script to create a login on the Master database of Azure SQL server containing target databases.

--Master
CREATE LOGIN JobUser
WITH PASSWORD = 'JobCredentials@123'
GO

Target database

Run below script on all the target databases to create a user.

--TargetDB1
CREATE USER JobUser
FROM LOGIN JobUser
GO
ALTER ROLE db_owner 
ADD MEMBER JobUser
GO

You can grant the least possible privileges to the JobUser instead of the db_owner role. Moreover, this user will be responsible for executing AzureSQLMaintenance procedure.

AgentJobs database (database for Elastic Job agent)

  1. Run the below script on AgentJobs database to create the credentials.
--AgentJobs - Creating credentials
CREATE MASTER KEY ENCRYPTION BY PASSWORD='AgeNtJob@123'
GO
CREATE DATABASE SCOPED CREDENTIAL JobCredentials WITH IDENTITY = 'JobUser',
SECRET = 'JobCredentials@123'
GO

Notice that we have used the same password as we have used to create login in master database of target server.

2. Create the target group on AgentJobs database.

--AgentJobs - Creating target group
EXEC jobs.sp_add_target_group 'DatabaseGroup1'
GO

3. Add the target database to the target group. Repeat the procedure jobs.sp_add_target_group_member for all target databases.

--AgentJobs - Add databases to the target group
EXEC jobs.sp_add_target_group_member 'DatabaseGroup1',
@target_type =  N'SqlDatabase',
@server_name='<dbserver>.database.windows.net',
@database_name =N'TargetDB1'
GO

4. Create Elastic agent job to execute AzureSQLMaintenance procedure.

--AgentJobs Create the job
EXEC jobs.sp_add_job @job_name='Database-Maintenance', @description=''
GO 
EXEC jobs.sp_add_jobstep @job_name='Database-Maintenance',
@command=N'exec [dbo].[AzureSQLMaintenance] @Operation=''all'' ,@LogToTable=1',
@credential_name='JobCredentials',
@target_group_name='DatabaseGroup1',
@step_timeout_seconds = 100000
GO

Note here that AzureSQLMaintenance provides the ability to run statistics and index update separately. If you wish to do so, instead of @Operation=’all’, you can specify the value as ‘statistics‘ or ‘index‘.

5. Schedule the job by executing the below script on AgentJobs database. The below script will configure the job to run every 2 weeks.

--AgentJobs - Execute the job manually
EXEC jobs.sp_start_job 'Database-Maintenance'
--Schedule the job (every 2 weeks)
EXEC jobs.sp_update_job
    @job_name='Database-Maintenance',
    @enabled=1,
    @schedule_interval_type='Weeks',
    @schedule_interval_count=2,
    @schedule_start_time= N'2021-05-08 10:00';  --DateTime in UTC.

6. Monitor the job logs.

--AgentJobs
SELECT * FROM [jobs_internal].[job_executions]

You can also monitor the execution status on Azure Portal -> Elastic Agent job Overview.

azure sql maintenance jobs using Elastic Job agent.

Other options

  1. AzureSQLMaintenance procedure can be scheduled to run using Azure Data Factory pipeline.
  2. AzureSQLMaintenance procedure can be run using Azure Automation account runbook. However, Azure Automation has a feature called fairshare, where any runbook that runs for 3 hours is unloaded to allow other runbooks to run. If the maintenance job runs for a longer duration, the procedure might terminate abnormally.

Pro Tips:

  1. As I mentioned earlier, the Elastic Job agent is in preview mode and may not provide the standard SLA.
  2. You need to allow Azure services and resources to access this Azure SQL server containing the Elastic Job agent database and target databases.
  3. If you have automated database deployments using Azure DevOps (dacpac), you may need to add AzureSQLMaintenance procedure code in all the target database projects.
  4. Elastic job Agent step by default times out in 12 hrs. If you want to run a procedure or script that might run for more than 12 hrs, you will need to specify @step_timeout_seconds = <Number of seconds> in the job step.
  5. It is recommended to try AzureSQLMaintenance features in the DEV environment before running it on PROD.

We have seen a step by step process to automate Azure SQL database maintenance tasks.

Spread the love

Kunal Rathi

Been working on the Microsoft BI stack for close to a decade. Aspiring Data Architect, Cloud enthusiast.

Leave a Reply