All we need is an easy explanation of the problem, so here it is.
Accessing our new SQL Managed Instance via an Azure VM/SSMS, the Maintenance Plan folder is missing from the Object Explorer pane. Reading other posts (that all relate to other versions of SQL Server) I’ve checked I have the valid permissions:
Is there a way to enable maintenance plans in SQLMI?
If not (assuming that SQLMI doesn’t use maintenance plans), what is the modern day Azure equivalent to perform automated backups/deletion/index maintenance?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Azure Managed Instance Doesn’t Include GUI SQL Maintenance
Unfortunately, SQL Maintenance folder and SQL Maintenance is not included in Managed Instance. It’s part of the Integration Services stack which has been also excluded from Managed Instance.
You’ll need to roll your own T-SQL maintenance using the MI SQL Agent or as some have mentioned, Ola Hallengren’s scripts. You can also use PowerShell and Runbooks, Azure Logic Apps, or Azure Functions. However, Logic Apps have a built-in 2 minute time out and Azure Functions have a 10 minute time out. Runbook timeouts can be configured with PowerShell as needed. My preferred method is still using the SQL Agent in MI.
Azure Data Factory
Azure Data Factory is the replacement for SSIS but doesn’t include SQL Maintenance plans at this time.
Traditional Backups Are Not Available
Using someone’s baked maintenance scripts will probably not be the total answer either as the T-SQL COPY_ONLY clause needs to be used with BACKUP DATABASE. This is because backups in MI, like Azure SQL Database, are automatic–and an ad hoc backup would interfere with the backup continuity. With database backups out, that leaves statistics, re-indexing, and integrity checks for your maintenance.
From Microsoft, “Both SQL Database and SQL Managed Instance use SQL Server technology to create full backups every week, differential backups every 12-24 hours, and transaction log backups every 5 to 10 minutes. The frequency of transaction log backups is based on the compute size and the amount of database activity.”
Writing about Azure database maintenance and backups are tricky because with Azure, Microsoft could change these behaviors or improve them over time.
Please refer to this other thread on DBA.StackExchange about how to schedule maintenance of indexes and statistics. As you can read on that thread you can use Ola Hallengren‘s maintenance scripts, and schedule them using SQL Agent on Azure Managed Instance.
You can use also Azure Automation to schedule any maintenance task as described on this article.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂