Nisarg Upadhyay
Querying Azure SQL Database of Elastic agent

Automating Azure SQL Database index maintenance using Elastic Job Agents

July 14, 2020 by

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:

Connect to Azure SQL Database

Once connected, execute the following query to create a database.

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:

Create an Elastic job agent

On Elastic Job agents screen, click on Add. See the following image:

Add new Elastic job agent

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:

New Job agent

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.

Choose Azure SQL database as Agent database

Back to Elastic Job agent screen, click on Create. See the following image:

Configure Job agent

Once the agent is configured successfully, we will

  1. Create database scoped credentials on the Elastic agent database
  2. Define the target group and its members
  3. Create required logins on the master database and target database
  4. 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:

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.

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.

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).

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:

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.

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:

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.

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.

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.

You can see the execution status of the Job from the Overview page of the Elastic job agent. See the following image:

Overview of Elastic Job Agent

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.

Following is the output:

Querying Azure SQL Database of Elastic agent

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.

Nisarg Upadhyay
Azure, Indexes, Jobs, Maintenance

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. 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

168 Views