Automating Azure SQL Database Maintenance Tasks with Elastic Jobs

Azure SQL Database is a fully managed relational database service provided by Microsoft. Unlike SQL Server and Azure SQL Managed Instance, Azure SQL Database does not provide SQL Server Agent. For recurring T-SQL maintenance across one or many Azure SQL databases, Azure Elastic Jobs is a native job-automation option. Elastic Jobs can run T-SQL in parallel across target databases, on a schedule or on demand.

Quick answer: Create an Azure Elastic Job agent, authenticate it to the target databases, add the databases to a target group, create a job step that runs your maintenance procedure, and schedule the job. Microsoft currently recommends Microsoft Entra authentication with a user-assigned managed identity for job-agent authentication.

When should you use Azure Elastic Jobs?

Elastic Jobs are useful when you need to run the same T-SQL task regularly or on demand across one or many Azure SQL Database databases. Typical use cases include index and statistics maintenance, schema deployment, reference-data updates, and collecting information from multiple databases.

• Run a T-SQL script on a schedule across multiple databases.
• Run a one-time operation across a group of databases.
• Target individual databases, servers, or elastic pools and include or exclude specific databases.
• Monitor job execution and retry failed job steps.

Prerequisites:
1. Create an empty Azure SQL Database for the Elastic Job agent. Microsoft currently documents S1 or higher for the job database.
2. Create an Azure Elastic Job agent and select the job database. The current portal experience uses the JA 100 service tier for the agent.
3. Deploy the AzureSQLMaintenance stored procedure to each target database where you want to perform index and/or statistics maintenance.
4. Decide how the Elastic Job agent will authenticate to the target databases. Microsoft recommends Microsoft Entra authentication with a user-assigned managed identity (UMI). Database-scoped credentials are also supported.

The examples below use T-SQL in the job database. The authentication section shows the current Microsoft Entra/UMI approach first, followed by the database-scoped credential approach for existing deployments.

Configure Microsoft Entra authentication with a managed identity

For new deployments, assign a user-assigned managed identity to the Elastic Job agent and use Microsoft Entra authentication to connect to the target databases. Create a database user for the managed identity in each target database and grant only the permissions required by the maintenance procedure.

In the Azure portal, create or select a user-assigned managed identity and assign it to the Elastic Job agent. Then configure Microsoft Entra authentication on the target logical SQL server and create the managed-identity database user in each target database.

<pre>-- Run in each target database
CREATE USER [<managed-identity-name>] FROM EXTERNAL PROVIDER;
-- Grant only the permissions required by your maintenance procedure.
-- Example:
ALTER ROLE [db_owner] ADD MEMBER [<managed-identity-name>];</pre>

Important: Do not automatically grant db_owner in production. Use the minimum permissions required by the maintenance procedure and your organization’s security model. The exact permissions depend on the version and implementation of the maintenance procedure you deploy.

Configure the Elastic Job agent and target group

After the job agent and authentication are configured, connect to the job database and create a target group containing the databases that should receive the maintenance command.

<pre>-- Run in the Elastic Job agent database
EXEC jobs.sp_add_target_group 'DatabaseGroup1';
GO
EXEC jobs.sp_add_target_group_member
    @target_group_name = 'DatabaseGroup1',
    @target_type = N'SqlDatabase',
    @server_name = N'<dbserver>.database.windows.net',
    @database_name = N'TargetDB1';
GO</pre>

Repeat jobs.sp_add_target_group_member for each target database. Elastic Jobs can also target servers and elastic pools, and target groups can be configured to exclude individual databases.

Create the maintenance job

Create a job and add a step that executes the AzureSQLMaintenance procedure. When using Microsoft Entra authentication with a user-assigned managed identity, do not specify @credential_name for the job step.

<pre>-- Create the job
EXEC jobs.sp_add_job
    @job_name = 'Database-Maintenance',
    @description = 'Azure SQL Database maintenance';
GO
-- Add the maintenance step
EXEC jobs.sp_add_jobstep
    @job_name = 'Database-Maintenance',
    @command = N'EXEC [dbo].[AzureSQLMaintenance] @Operation = ''all'', @LogToTable = 1',
    @target_group_name = 'DatabaseGroup1',
    @step_timeout_seconds = 100000;
GO</pre>

AzureSQLMaintenance can perform index maintenance and statistics maintenance separately. For example, use @Operation = ''index'' when the job should perform only index maintenance. Review the maintenance procedure you deploy for its supported parameters and behavior.

Schedule the maintenance job

You can run an Elastic Job manually or configure a recurring schedule. Elastic Job schedules use UTC. Use a future start time when configuring a new schedule so that a newly enabled schedule does not unexpectedly execute because its start time is already in the past.

<pre>-- Run the job manually
EXEC jobs.sp_start_job 'Database-Maintenance';
GO
-- Enable a recurring schedule.
-- Use a future UTC start time appropriate for your environment.
EXEC jobs.sp_update_job
    @job_name = 'Database-Maintenance',
    @enabled = 1,
    @schedule_interval_type = 'Weeks',
    @schedule_interval_count = 2,
    @schedule_start_time = N'<future-UTC-start-time>';
GO</pre>

Monitor job execution

Elastic Jobs records execution status in the job database. You can also monitor executions in the Azure portal and with PowerShell.

<pre>-- View recent job executions
SELECT *
FROM jobs.job_executions
WHERE job_name = 'Database-Maintenance'
ORDER BY start_time DESC;
-- View active executions
SELECT *
FROM jobs.job_executions
WHERE is_active = 1
ORDER BY start_time DESC;</pre>

Database-scoped credentials: existing deployments

Elastic Jobs also supports database-scoped credentials. This was the original authentication approach used by many Elastic Jobs deployments. If you already use it, you can continue to use it. For new deployments, Microsoft recommends Microsoft Entra authentication with a user-assigned managed identity.

<pre>-- Agent database: create a database-scoped credential
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong-password>';
GO
CREATE DATABASE SCOPED CREDENTIAL JobCredentials
WITH IDENTITY = 'JobUser',
     SECRET = '<job-user-password>';
GO
-- Job step: use the credential
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';
GO</pre>

Never publish real passwords or credentials in scripts, source control, screenshots, or documentation. Use placeholders and secure secret-management practices for any credential-based deployment.

Other options

Elastic Jobs is one option for Azure SQL Database job automation. Depending on the environment, you can also invoke a maintenance procedure from Azure Data Factory or an Azure Automation runbook. Choose the orchestration service based on scheduling, identity, networking, monitoring, and operational requirements.

Pro tips

Pro tips:
1. Prefer Microsoft Entra authentication with a user-assigned managed identity for new Elastic Job deployments.
2. Grant the job identity only the permissions required by the maintenance procedure instead of using db_owner by default.
3. Elastic Job steps have a default timeout of 12 hours and retry failed execution attempts. Increase @step_timeout_seconds when a maintenance operation legitimately needs more time.
4. Test AzureSQLMaintenance in a development environment before applying it to production databases.
5. If your databases are behind private networking, review the Elastic Jobs private endpoint option and target-server networking requirements.

Elastic Jobs provides a native way to schedule and run T-SQL maintenance tasks across Azure SQL Database targets. For production environments, combine the job schedule with appropriate identity, least-privilege permissions, monitoring, retry settings, and networking controls.

See more

Kunal Rathi

With over 15 years 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.