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.
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.|
Step by Step walkthrough:
-- 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.
Open Azure data factory to create a pipeline and name it.
Name = Look for databases
Retry = 2
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.
"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.
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'
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.