Need to configure the Maintenance plan for themultiple databases in azure automation accounts using powershell.

Question

HI Team,

Need to configure the Maintenance plan for the multiple databases in azure automation accounts using powershell.

Note : I am able to configure for the single databases and it is working fine (rebuild index and update stats)

Service : SQL Databases.

Answer 1

The way I do that is I create Ola Hallengren scripts on each database, then I create one PowerShell Workflow Runbook on Azure Automation that execute stored procedures on databases, after that I just need to create the different schedules on Azure Automation that will perform all maintenance tasks by executing Ola's scripts (stored procedures) or any other stored procedure created for maintenance purposes.

 $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
 $DatabaseCommand.CommandType = [System.Data.CommandType]::StoredProcedure
 $DatabaseCommand.Connection = $DatabaseConnection
 # A value of 0 indicates no limit
 $DatabaseCommand.CommandTimeout = 0
 $DatabaseCommand.CommandText = $SP

Set the time out to zero to avoid timeout errors.