azureazure-sql-managed-instancesql server
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?
Fixed this - VSS Writer had fallen over for some reason, and the upshot of that was that a load of stuff (BACKUP amongst them) could not complete (BlkBy column in sp_who2 showed this up). A reboot of the server fixed this issue (services.msc wouldn't let me kill VSS Writer, and neither would the KILL command)...only thing left to do is find out WHY it happened.
And sort myself a disaster recovery plan ;)
Use the process described in Add Azure Monitor solutions from the Solutions Gallery to add the Azure SQL Analytics (Preview) solution to your Log Analytics workspace.
Once you have created Azure SQL Analytics solution in your workspace, you need to configure the Managed Instance that you want to monitor to stream its diagnostics telemetry to the solution. Follow detailed instructions on this page.
When you add the solution to your workspace, the Azure SQL Analytics tile is added to your workspace, and it appears in Overview. Select View Summary link to load the tile content
Once loaded, the tile shows the number of Azure SQL databases, elastic pools, Managed Instances, and databases in Managed instances that the solution is receiving diagnostics telemetry from.
The solution provides two separate views -- one for monitoring Azure SQL Databases and elastic pools, and the other view for monitoring Managed Instance, and databases in Managed Instances.
To view Azure SQL Analytics monitoring dashboard for Azure SQL Databases and elastic pools, click on the upper part of the tile. To view Azure SQL Analytics monitoring dashboard for Managed Instance, and databases in Managed Instance, click on the lower part of the tile.
Once the Azure SQL Analytics tile for the databases is selected, the monitoring dashboard is shown.
Best Answer
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.”
Note:
Writing about Azure database maintenance and backups are tricky because with Azure, Microsoft could change these behaviors or improve them over time.