Automating Azure SQL Database Maintenance Tasks

Azure SQL Database is a fully managed relational database service provided by Microsoft as part of the Azure cloud computing platform. It is based on Microsoft SQL Server and allows you to store, manage, and retrieve data in a scalable and secure manner. In this article, I have described automating Azure SQL database maintenance tasks using Azure Elastic Job agent and azuresqlmaintenance procedure. 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 an Azure Elastic Job agent managed service for scheduling jobs on Azure SQL Databases.

When should you use Azure Elastic Job Agent?

An Elastic Job agent is the Azure resource for creating, running, and managing jobs similar to SQL Server Agent jobs. We can also run elastic queries on multiple Azure SQL databases using a single elastic agent job.

You should consider using Azure Elastic Job Agent whenever: 

• You have an activity or script that needs to be run regularly on a schedule, targeting one or more databases. 
• You have a script that needs to be run once but across multiple databases. 
• Combine the results of the job execution from multiple databases in a shared database.

Pre-requisites:
1. Create a blank Azure SQL database for Elastic Job agent. with the name say “AgentJobs” on Azure Portal. The Minimum required compute for this database is S1 (20 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, please execute the scripts below to automate the maintenance job on Azure SQL databases. You must 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 the Azure SQL server containing target databases.

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

Target database

Run the 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 the 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 a login in the master database of the target server.

2. Create the target group on the 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 an 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:
AzureSQLMaintenance provides the ability to run statistics and index updates separately. If you wish only to automate Azure SQL rebuild index, instead of @Operation=’all’/, you can specify the value as @Operation=’index’.

5. Schedule the job by executing the below script on the 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.

Other options

We have seen how to automate Azure SQL rebuild index using Azure Elastic Job Agent. Below are some other ways to achieve this.

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 longer, the procedure might terminate abnormally.

Pro tips:
1. It would be best to allow Azure services and resources to access this Azure SQL server containing the Elastic Job agent database and target databases.
2. If you have automated database deployments using Azure DevOps (DACPAC), you may need to add AzureSQLMaintenance procedure code in all the target database projects.
3. Elastic job Agent step by default times out in 12 hrs. To run a procedure or script that might run for more than 12 hrs, specify @step_timeout_seconds = <Number of seconds> in the job step.
4. It is recommended to try AzureSQLMaintenance features in the DEV environment before running it on PROD.
5.. You can manage Elastic jobs in Azure using Azure Elastic Jobs Manager Visual Studio extension. Know more about it here.

We have seen a step-by-step process to automate Azure SQL database maintenance tasks. Feel free to connect with me should you have any questions about it.

See more

Kunal Rathi

With over a decade of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.
I am always interested in new challenges so if you need consulting help, reach me at kunalrathi55@gmail.com.