A common saying about Azure is that it is always changing. This is very true too. Microsoft is always improving the environment by adding new features, tweaking interfaces, tweaking features, and much more.
A couple of recent changes I came across is that now, any newly created database in Managed Instance have Query Store enabled by default. Previously the behavior was modeled after on-premises SQL Server which is off by default. Query Store is a database level feature, and had to be enabled per database. For any newly created databases, this is enabled by default and can be turned off if the customer doesn’t want it on.
Another recent change is enabled TDE by default on any newly created databases. By default, if you haven’t created your own key, it will use the service-managed key. A bug I’ve found is that in SSMS, even though you set ‘Encryption Enabled’ to false, it will still enable TDE. This is not the behavior using T-SQL.
A side effect of TDE being enabled by default and using the service-managed key, is that you can no longer utilize COPY_ONLY level backups. You’ll have to remove TDE or switch to using a customer managed key.
With these two changes, default behavior is becoming more in-line with Azure SQL Database where Query Store and TDE have been enable by default for several years.
A key point to mention, is that currently this new behavior is only for newly created databases. Any database that is migrated to Managed Instance, inherits the prior settings, so if TDE was not in use, it will not be turned on, likewise for Query Store as well.
Are you considering a move to Azure SQL Managed Instance or Azure SQL Database and have questions, feel free to send me an email with your thoughts or concerns.
