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:
- Add SQL Authentication login on database Master and user and permission on all user databases.
- Deploy "AzureSQLMaintenance" SP on all user database
- Create pipeline - LoopAllDatabase
- Schedule pipeline
Step by Step walkthrough:
- 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';
2. Deploy "AzureSQLMaintenance" Store Procedure(SP) on all user database
Download code AzureSQLMaintenance and deploy SP to the user databases.
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 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.
- 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
"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.
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!
Updated Jan 24, 2022
Version 3.0HitenBhavsar_MSFT
Microsoft
Joined May 17, 2020
Azure Database Support Blog
Follow this blog board to get notified when there's new activity