Thursday, January 13, 2022

Automating Azure SQL DB index and statistics maintenance using Azure Data Factory - Single Database

Overview: As we know, it's crucial that you run Database maintenance regularly in order to keep your database performance up with the latest statistics and healthy indexes, here we provide another way to schedule this job using Azure Data Factory; this can be done on a scheduled time interval weekly/bi-weekly/monthly.

Solution: This article will show steps to schedule Database maintenance using the Azure Data Factory(ADF). ADF can use as an SQL agent to schedule and automate Azure SQL database tasks. In addition, database maintenance plans not supported in Azure can also be replaced with Azure pipelines.

 

Note: Using ADF will incur an additional cost.

 

Use case: 

  • After large data deletion from database/tables or after data purge routine.
  • Post ETL pipelines, as a final activity step to perform database update stats and rebuild indexes per business needs.
  • After database migrations or database refresh/restore from production to Dev/Test environments.
  • Use Azure Data Factory pipeline with store procedure activity replacement of Database maintenance plans, which are unsupported in Azure SQL DB.

 

This can be done via AzureSQLMaintenance store procedure (SP) to perform complete Database maintenance, Store procedure and blog written by Yochanan Rachamim are listed below.
 

Blogs:
How to maintain Azure SQL Indexes and Statistics - Microsoft Tech Community

Automating Azure SQL DB index and statistics maintenance using Azure Automation - Microsoft Tech Community

 

Code:

AzureSQLMaintenance

 

Main steps:

  1. Use existing or create an Azure Data Factory
  2. Add SQL Authentication login on database
  3. Create "AzureSQLMaintenance"  SP on the user databases
  4. Create pipeline
  5. Schedule pipeline 

Step by Step walkthrough:

  1. Please create one using the Azure portal if you do not have a data factory.
    https://docs.microsoft.com/en-us/azure/data-factory/quickstart-create-data-factory-portal#create-a-data-factory
  2. Add New login to perform maintenance tasks, which will help identify when the maintenance tasks are running on the database. You can use the existing login and user. 

 

-- Run on Master

CREATE LOGIN DBAdmin WITH password='<your-password>';

-- Run on user database

CREATE USER DBAdmin FROM LOGIN DBAdmin;

-- permission

EXEC sp_addrolemember 'db_owner', 'DBAdmin';

 

HitenBhavsar_MSFT_0-1642020908265.png

 

 3. Download and deploy AzureSQLMaintenance to a user database. Code: AzureSQLMaintenance

HitenBhavsar_MSFT_0-1642021535040.png

4. Open Azure data factory to create a pipeline and name it.

HitenBhavsar_MSFT_1-1642020956021.png

Add store procedure activity.

HitenBhavsar_MSFT_2-1642021012314.png

 

Press "+ New" and set up linked Serviced by adding a name, type, subscription, Server, database, authentication type, User, and password. Test connection for the connectivity check.
 

HitenBhavsar_MSFT_3-1642021049207.png

 

After selecting Linked Service, Select AzureSQLMaintenance store procedure

 

HitenBhavsar_MSFT_4-1642021094831.png

Import parameter, for operation parameter, provides the value "ALL".
Parameter value represents the database task it will perform.  Blog

ALL = Run through all indexes and statistics and take a smart decision about steps taken for each object
STATISTICS = run smart maintenance only for statistics
INDEX = run smart maintenance only for indexes

All other parameters can be removed from the setting tab. 
 

HitenBhavsar_MSFT_0-1642021202918.png

 

"Publish all " to publish and save the pipeline.
Note: Use trigger now to test the pipeline.
 

HitenBhavsar_MSFT_7-1641937185375.png

 

Use monitor pipeline run to check the Manual trigger.

HitenBhavsar_MSFT_8-1641937185376.png

 

5. Schedule pipeline using trigger using "New/Edit" button.

HitenBhavsar_MSFT_9-1641937185376.png

 

Enter trigger detail like name, Desc type. Then, publish the trigger to make it effective.

HitenBhavsar_MSFT_10-1641937185377.png

 

I hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below.

Hiten Bhavsar

Posted at https://sl.advdat.com/3fhZlqT