In this article, I am going to explain how we can automate the index and statistics maintenance of Azure SQL Database using an Elastic Job Agent.
The Microsoft SQL Server Agent component is used to automate the various tasks including database maintenance. But in Azure, the SQL Server Agent is only available in the Azure managed instances. We cannot schedule the database maintenance tasks on a single database. To automate the database maintenance, we can use the Elastic Job agent. It gives us the ability to execute the database maintenance on a single database or a group of database servers. This article explains the process of configuring the SQL Elastic Jobs Agent.
Before we configure the Elastic job agent, we must create an Azure SQL database. The elastic job agent creates required stored procedures and tables. To create an Azure SQL Database, launch SQL Server management studio and connect to the Azure SQL Server. See the following image:
Once connected, execute the following query to create a database.
|
1 |
Create database elasticjobs |
Once the database is created, let us configure the elastic job agent. To do that, navigate to the “All resources“, Click on Databases and choose “Elastic Job Agents.” See the following image:
On Elastic Job agents screen, click on Add. See the following image:
On the next screen, provide the name of the elastic job agent, select the subscription. Now, as I mentioned above, we must configure the Agent Job database. To do that, click on “Job database” See the following image:
On the job database screen, select the azure server from the “select server” drop-down box. When you select the desired server, it populates the list of the Azure SQL databases hosted on the server. Choose the database from the list and click on OK.
Back to Elastic Job agent screen, click on Create. See the following image:
Once the agent is configured successfully, we will
- Create database scoped credentials on the Elastic agent database
- Define the target group and its members
- Create required logins on the master database and target database
- Create an Index and Statistics Maintenance Jobs
In this article, we are going to run the maintenance jobs on the AdventureWorksLT database; therefore, our target database is AdventureWorksLT, and as mentioned, the agent database is elasticJobs.
Create database scoped credentials
The database scoped credentials are used to connect to the target database. This credential must be created on the agent database. The following query creates a credential named JobExecuter. It is used to connect to the target database and execute the maintenance scripts. Execute the following T-SQL Script:
|
1 2 3 4 5 6 |
/*This script will be executed on the agent database (elasticJobs)*/ CREATE MASTER KEY ENCRYPTION BY PASSWORD='AzureSQL@123'; CREATE DATABASE SCOPED CREDENTIAL JobExecuter WITH IDENTITY = 'SQLJobUser', SECRET = 'AzureSQL@123'; GO |
Define the target group and its member
Once credentials are defined, we will create a target group. To do that, execute the following script on the agent database.
|
1 2 3 4 |
/*This script will be executed on the agent database (elasticJobs)*/ EXEC jobs.sp_add_target_group ‘AzureProductionServers’ GO |
The above script creates a target group named AzureProductionServers. Once the target group is created, execute the following script to add the server as a member of the target group. Execute following T-SQL script on agent database.
|
1 2 3 4 5 6 7 8 |
/*This script will be executed on the agent database (elasticJobs)*/ EXEC jobs.sp_add_target_group_member 'AzureProductionServers', @target_type = N'SqlDatabase', @server_name='a********.database.windows.net', @database_name =N'AdventureworksLT' GO |
Once target group and members have been created, we will create required logins on master and target database.
Create required logins on the master database and target database
Now, we will create a SQL login on the master database. The login name and password must be the same that we used as an identity to create a database scoped credential. Execute the following T-SQL script on the master database (system database).
|
1 2 3 |
/*This script will be executed on master (System database) database */ CREATE LOGIN SQLJobUser WITH PASSWORD = 'AzureSQL@123'; |
The above script creates a user named SQLJobsUser. Next, we will create a user on the target database. Make sure that the user must have appropriate permissions on the target database. Here I am granting db_owner permission to make sure that the SQL job executes successfully. Execute the following script:
|
1 2 3 4 5 6 7 8 |
/*This script will be executed on target database (AdventureWorksLT) */ Create user SQLJobUser from login SQLJobUser ALTER ROLE db_owner ADD MEMBER [SQLJobUser] ; GO |
The above script creates a user named SQLJobUser, and the db_owner permission is also granted to the user.
Create SQL Job for index maintenance of Azure SQL Database
The script to create the SQL Job must be executed on the agent database. The following code creates a SQL Job named DBA – Index Maintenance. Execute the following script.
|
1 2 |
/*This script will be executed on the agent database (elasticJobs)*/ EXEC jobs.sp_add_job @job_name='DBA – Index Maintenance', @description='This Job performs index maintenance on every sunday at 12:00 AM' |
I have created a stored procedure named sp_index_maintenance on the agent database. If the index fragmentation percentage is less than 30%, then it reorganizes the index, and index fragmentation is higher than 30%, than it rebuilds the entire index. Following is the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
/*This script will be executed on agent database (elasticjobs)*/ Create procedure sp_index_maintenance As begin DECLARE @DBName varchar(500) declare @SQLCmd nvarchar(max) declare @FregmentedIndexes int declare @i int=0 declare @TableName varchar(500) declare @indexName varchar(500) declare @SchemaName varchar(500) declare @FregmentationPercent float declare @RebuildCommand nvarchar(max) set @DBName= 'AdventureWorks_2016-TestInstall' if exists (select name from tempdb.sys.tables where name like '%#FregmentedIndexes%') drop table #FregmentedIndexes create table #FregmentedIndexes ( ID int identity (1,1), TableName varchar(500), indexName varchar(500), SchemaName varchar(500), Fregmentation_Percentage float ) truncate table #FregmentedIndexes set @SQLCmd='SELECT distinct b.name,c.name,d.name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(''' +@DBName +'''), null, null, null, null) a inner join ['+@DBName+'].sys.tables b on a.object_id=b.object_id inner join ['+@DBName+'].sys.indexes c on a.object_id=c.object_id inner join ['+@DBName+'].sys.schemas d on b.schema_id=d.schema_id Where b.schema_id>1' Print @SQLCmd insert into #FregmentedIndexes (TableName,indexName,SchemaName,Fregmentation_Percentage) exec sp_executesql @SQLCmd set @FregmentedIndexes=(select count(1) from #FregmentedIndexes) while @i<@FregmentedIndexes begin select top 1 @TableName = TableName, @SchemaName=SchemaName, @indexName = indexName, @FregmentationPercent = Fregmentation_Percentage from #FregmentedIndexes if @FregmentationPercent >30 Begin set @RebuildCommand ='Alter index ['+@indexName + '] on ['+ @SchemaName+ '].['+@TableName+'] Rebuild' exec @RebuildCommand End Else if @FregmentationPercent < 30 Begin set @RebuildCommand ='Alter index ['+@indexName + '] on ['+ @SchemaName+ '].['+@TableName+'] REORGANIZE' exec (@RebuildCommand) End set @i=@i+1 delete from #FregmentedIndexes where TableName=@TableName and indexName=@indexName End End |
Now, we will create a job step to execute the stored procedure across all the servers within the target group. Execute the following T-SQL script.
|
1 2 3 4 5 6 |
/*This script will be executed on the agent database (elasticJobs)*/ EXEC jobs.sp_add_jobstep @job_name='DBA – Index Maintenance', @command=N' exec sp_Index_Maintenance', @credential_name='JobExecuter', @target_group_name='AzureProductionServers' |
The above script creates a job step named Execute Index Maintenance in SQL Job named DBA – Index Maintenance job. We want to run this Job on every Sunday at 12:00 AM. To configure the schedule, execute the following T-SQL code.
|
1 2 3 4 5 6 7 8 |
/*This script will be executed on the agent database (elasticJobs)*/ EXEC jobs.sp_update_job @job_name='DBA – Index Maintenance', @enabled=1, @schedule_interval_type='Weeks', @schedule_interval_count=1, @schedule_start_time= N'20200706 12:00'; |
Test the SQL Job
Once the job is created, you can use [jobs].[sp_start_job] stored procedure. The following script starts the execution of the script.
|
1 2 3 |
/*This script will be executed on the agent database (elasticJobs)*/ exec [jobs].[sp_start_job] 'DBA – Index Maintenance' |
You can see the execution status of the Job from the Overview page of the Elastic job agent. See the following image:
As you can see that SQL Job is executed successfully.
You can see the execution status of the job by querying the [jobs_internal].[job_executions] and [jobs_internal].[jobs] tables. Following is the query that populates the name of the job, status of the job, start time and end time of the job.
|
1 2 3 4 5 6 |
select b.name,a.lifecycle,start_time, end_time from [jobs_internal].[job_executions] a inner join [jobs_internal].[jobs] b on a.job_id=b.job_id where b.name='DBA – Index Maintenance' |
Following is the output:
Summary
In this article, I have explained how we can use the Elastic Job Agent to automate the index maintenance of the Azure SQL Database. You can use the Elastic Job Agent to automate other database maintenance tasks.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022