TLDR; Don’t assume that your SQL MI database is protected from deletion by using Azure
TLDR2; if you prefer video, here’s the demo
Azure SQL Managed Instance (SQL MI) falls architecturally in the sweet spot between the database-scoped with limited responsibility and flexibility Azure SQL Database and full-fledged with full responsibility Azure SQL VM.
The architectural of the SQL MI makes it understandable that the instance is the unit of deployment and management not the database However the SQL MI product group made also the database inside the the SQL MI addressable from the Azure Resource Manager. This way you can target the databases by Azure Resource Manager REST API and various SDKs and tools. It has its own resource type
This behavior makes the SQL MI databases having dual interfaces for creation and management
- TSQL: Through Create/Alter/Drop Database statement
- Azure Resource Manager: Using REST API, az cli, Powershell and all the resource manager SDKs
Now to our point, since SQL MI databases register as a resource then Azure Resource Manager Locks should affect them. Which is the case, if you lock the instance, the lock will be inherited by all databases. You can also lock individual databases.
BUT, it will only affect the Azure Resource Manager operations. So if you try to drop the database using REST API, az cli, PowerShell AZ provider or any SDK while having a delete lock on the database, the operation will fail.
However, if you tried to drop the database using TSQL, it will not pass through the lock and will simply be dropped just like any other database.
References:
| [Azure Resource Manager overview – Azure Resource Manager | Microsoft Docs](https://docs.microsoft.com/en-us/azure/azure-resource-manager/management/overview) |
| [Lock resources to prevent changes – Azure Resource Manager | Microsoft Docs](https://docs.microsoft.com/en-us/azure/azure-resource-manager/management/lock-resources?tabs=json) |
