Updated Default Settings for Azure SQL Managed Instance Databases

A common saying about Azure is that it is always changing. This is a very accurate statement, want proof, just take a look at Microsoft Build 2020. Microsoft is always improving the environment by adding new features, tweaking interfaces, tweaking features, and much more.

I’ve been working with Managed Instance since private preview and just recently came across a few recent changes. Now when you create a new database, it will have Query Store enabled by default. Previously the behavior was modeled after on-premises SQL Server which is off by default. With Query Store being a database level feature, it had to be enabled per database. If for some reason you don’t want to have Query Store enabled, you can turn it off.

Another recent change is that all newly created databases have “Encryption Enabled” set to True. This enables Transparent Data Encryption. By default, if you haven’t created your own key, it will use the service-managed key. A bug I’ve found in SSMS is that even though you set “Encryption Enabled” to false, it will still enable TDE. This is not the behavior when using T-SQL.

A side effect of TDE being enabled by default and using the service-managed key, is that you can no longer take advantage of COPY_ONLY level backups. You’ll have to remove TDE or switch to using a customer managed key in order to take your own backups. Many organizations that need longer than 35 days of backup retention will have to rely on COPY_ONLY backups to Azure Blob storage for longer term retention.

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 point to mention, is that currently this new behavior only applies for newly created databases. Any database that is migrated to Managed Instance, inherits the prior database settings. If TDE was not in use, it will not be turned on, likewise for Query Store.

Are you considering a move to Azure SQL Managed Instance or Azure SQL Database and have questions, feel free to reach out.

Check out the video below when I demonstrate how these values are on by default and the error you’ll get with trying a COPY_ONLY level backup. The video is just under 5 minutes long.