Ranga Babu
Azure SQL - Automation account

Azure SQL – Index tables using Azure Automation

June 25, 2019 by

This article provides an overview of indexing tables in Azure SQL database using Azure Automation and how to schedule indexing jobs to run at specific intervals.

There are different ways to schedule jobs in Azure. Please refer to Azure SQL- Elastic Job Agent for configuring and scheduling jobs to run on Azure SQL databases.

The following are steps involved in creating and scheduling the indexing Jobs using Azure Automation.

  1. Creating an Automation account
  2. Creating a runbook
  3. Creating credentials
  4. Schedule the runbook

Let’s go over these steps one by one.

Creating an Automation account

Login to your Azure portal. Search for automation and select the Automation Accounts from the list.

Azure Automation account

In the Automation Accounts page, Click on Add.

Enter the name of the Automation account, select the subscription, resource group, and location.

Click on Create.

Azure automation account

Once you click on Create, a deployment is created which will create a resource (Automation account) in the specified resource group and location.

Azure SQL - Automation account

Creating a runbook

Click on the Automation account which takes you to the automation account page. Click on the Runbooks gallery. The runbook gallery has runbook samples that are already created by Microsoft and the community team. Search in the runbook gallery by typing index in the search box and select Indexes tables in an Azure database if they have a high fragmentation.

Runbook gallery

Click on Import to import it to your runbook list in the Azure Automation account. Give the name of the runbook and Click Ok.

import runbook

Once the runbook is created, Navigate to Runbooks in the Automation account page. This page shows all the Runbooks that are created by you and Runbooks imported from the Runbook gallery.

We need to publish the Runbook which indexes tables in Azure SQL database to run it manually or to schedule it.

To publish the Runbook, click on the Runbook you imported and edit it. Click on Publish.

publish runbook

You can also create your own Runbook by clicking on Create a runbook in the Runbooks page.

Enter the name of the Runbook, select the Runbook type and click on Create.

custom runbook

Write your own script in the editor to automate your regular database tasks and click on publish to publish the Runbook to Azure Automation account.

publish custom runbook

Creating credentials

We need to create a credential which will be used to connect the Azure database. In the Automation account page, Click on Credentials. Click on Add a credential. Enter the name of the credential, user name, and password. Click on Create to create credential.

credentials in Azure automation

Now login to Azure SQL database using SQL Server management studio and create a login with the same username and password used in the credential. Please refer to the following T-SQL script.

Create a user from the login you created above in the database you are going index and give appropriate privileges to create or alter the index else the indexing may fail.

Running the Runbook manually

Navigate to Azure Automation account page, Click on the Runbooks. In the Runbooks page, click on the Runbook and click on Start.

start the runbook manually

Once you click on start it will ask you to enter the parameters. Enter the mandatory input parameters Azure SQL database server name, database name, and credentials which you created earlier.

SQLSERVERPORT: Enter the port number of the database if the database server is running on specific port else leave it blank which will take the default value i.e. 1433.

FRAGPERCENTAGE: Enter the fragmentation percentage to index only if the fragmentation is greater than the specified value. If left blank it considers default value i.e. 20.

REBUILDOFFLINE: The default value is false. Select true from the drop down if you want to rebuild the index online.

TABLE: By default, all the tables are re-indexed. If you want to re-index indexes on a specific table in Azure SQL database, mention the table name.

After entering the input parameters, click on Create which will create a Job. Once the indexing is completed, the status of the job is changed to Completed as shown in the below image.

job status in Azure Automation

Click on the errors tab to check if the job reported any errors or not. To check the input parameters that were passed to the job, click on the Input tab.

Scheduling the runbook

We can also schedule the Runbook to run at specific intervals. To schedule a Runbook, Navigate to Automation account page and Click on Runbooks. Click on the Runbook from the list. In this case, it is SQLIndexRunbook which will index tables in Azure SQL database. Click on Schedules and Click on Add a Schedule.

Add a schedule to runbook

Click on Configure parameters and run settings and enter the input parameters. Click OK.

input parameters to schedule runbook in Azure Automation

Click on Link a schedule to your Runbook and create a new schedule.

Schedule runbook

Enter the name of the schedule, select the time zone and the interval. Set the expiration date and time if you want to stop the schedule after the expiry date.

You can view the job statistics at the Overview section of the Azure Automation account page.

Overview of Automation account in Azure

Conclusion

In this article, we explored how to create Runbooks in Azure Automation to index tables in Azure SQL database, schedule Runbooks to run at a specific interval. In case you have any questions, please feel free to ask in the comment section below.

Please refer to SQL Azure category to learn more on Azure SQL.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
779 Views