Automating DB maintenance for all SQL Databases in a single server using Azure Data Factory pipeline
Published Jan 21 2022 07:31 AM 3,671 Views

Introduction: Run Database maintenance on all Azure SQL databases on a SQL server using Azure Data Factory on the schedule trigger. This will help DBA to perform database tasks on all DBs with a single pipeline.

Overview:  

Recently customers have requested to perform DBA tasks on all the databases on SQL Server. This solution helps customers perform tasks on the database sequentially. Using Part 1 "Automating Azure SQL DB index and statistics maintenance using Azure Data Factory - Single Database" Blog. Building upon Azure data factory pipeline with store procedure, lookup and for each Activity to loop databases and Using the ADF trigger we will schedule the job to run on time interval.

 

Note: Using ADF will incur an additional cost.

 

Use case: 

  • Performed Database admin and Database maintenance tasks on all user databases.
  • collect login/user detail, database metrics, DMV view detail using Copy activity.
  • Perform a baseline and Run compliance ad hoc query on databases and store data in a centralized DBA database.

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.

Code: AzureSQLMaintenance

 

Main steps:

  1. Add SQL Authentication login on database Master and user and permission on all user databases.  
  2. Deploy "AzureSQLMaintenance" SP on all user database
  3. Create pipeline - LoopAllDatabase
  4. Schedule pipeline

Step by Step walkthrough:
 

  1. Add SQL Authentication login/user
    Add New login/User to perform maintenance tasks Using SSMS. This will help you identify when the maintenance tasks are running on the database.
    Note - If you have an existing login and user then skip this step.

 

-- Create a login on the master database
CREATE LOGIN DBAdmin WITH password='YourPassword';

-- Create use and grant permission on master and user database. 
-- Repeat the below command for all other user databases on the server.
CREATE USER DBAdmin FROM LOGIN DBAdmin;
-- permission
EXEC sp_addrolemember 'db_owner', 'DBAdmin';

 

Ahmed_S_Mahmoud_0-1642779016783.png

 

2. Deploy "AzureSQLMaintenance" Store Procedure(SP) on all user database

Download code AzureSQLMaintenance and deploy SP to the user databases.

HitenBhavsar_MSFT_1-1642624297874.png

3. Create pipeline - LoopAllDatabase.
You can Import pipeline template using below zip file.  
https://github.com/HitenBhavsarMSFT/DataFactoryTemplate/blob/main/LoopAlldatabase.zip

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

    • Create a pipe and give name "LoopAlldatabase"
    • Add Pipeline variable
      Name = DatabaseString
      Type = String
      Default = <empty string>
      Add Pipeline variable Name "DatabaseString"Add Pipeline variable Name "DatabaseString"

       

    • Add lookup activity from the activities

General tab: 
Name = Look for databases
Retry = 2
Settings tab:
Source Dataset = Select a New "+ New" source dataset . Give dataset name. Create a New linked Service pointing to the master database
Name = YourServerName
Connect via integration runtime = AutoResolveIntegrationRuntime
Fully qualified domain name =  yourinstance.database.windows.net
Database name = Master
Authentication type = SQL Authentication 
User Name = DBAdmin
Password = your password 


User Query = Query
Query: Select [name] from sys.databases where [name] != 'master'
Preview data to check the database name results. 

AllDatabase_lookUp1.gif

    • Add forEach activity to loop all the databases 
      Review the pipeline variable for the database name. 
      Add Foreach 
      General tab: 
      Name = "ForEach databases"
      Settings tab:
      Sequential = True
      Activities tab:
      Add Set Variable and store procedure activities and connect each other. 

      Configure Set Variable: 
      General tab: 
      Name = "SetDatabase"
      Variables tab:
      Name = DatabaseString
      Value =  @item().name

      Configure Store procedure: 
      General tab: 
      Name = "Run Database Tasks"
      Retry = 2

      Settings tab:
      Linked service = "+ New" to add new link services. 
      Name = Dynamilinkservervices
      Add Parameters for FQDN and database. 
      Name Type Default Value
      ServerFQDN String YourInstance.database.windows.net.database.windows.net
      DBname String <EmptyString>

      Connect via integration runtime = AutoResolveIntegrationRuntime
      Fully qualified domain name =  @{linkedService().ServerFQDN}
      Database name = @{linkedService().DBname}
      Authentication type = SQL Authentication 
      User Name = DBAdmin
      Password = your password 

      Store procedure Name = [dbo].[AzureSQLMaintenance]
      Import Store procedure parameters

      Keep "operation" parameter and remove others, Add value = ALL

      AllDatabase_Foreach.gif

       

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

4. Schedule pipeline
Using trigger using "New/Edit" button.

SetTrigger.gif

 

Limitations:
SQLDB pool database from the Azure Synapse will not work in current setup. Please Add database in where clause display below. 
Select [name] from sys.databases where [name] != 'master' and [name] != 'SQLPoolDatabase'

Reference 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 Com...
Automating Azure SQL DB index and statistics maintenance using Azure Data Factory - Single Database 

Feedback and suggestions:

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

Thanks for your support!


@HitenBhavsar_MSFT 

 

Co-Authors
Version history
Last update:
‎Jan 24 2022 06:08 AM
Updated by: