Does Azure SQL Managed Instance offer SQL Server 2016?

Question

I want to create a SQL Server Managed Instance in Azure that is SQL Server 2016 so I can backup and restore databases across on prem and Azure - i.e. in case we want to back out of Azure, we can do a backup and restore rather than create scripts procedure.

I see the @@VERSION of my current managed instance is Microsoft SQL Azure (RTM) - 12.0.2000.8

If I try to restore a database from Azure to a 2016 server, I get the message '15.00.2000. That version is incompatible with this server, which is running version 13.00.5492' - so Azure is SQL Server 2019. Can it be 2016? Struggling to find clear answers when it comes to Azure Managed Instance.

• jack: The answer from Kin Shah here worked for me dba.stackexchange.com/questions/55055/…

Answer 1

You cannot restore a backup from a Managed Instance to an on-prem version of SQL Server. Even if you had an on-prem 2019, it wouldn't work. During our migration this was one of the things that gave me pause as well, that it was going to be one-way without a lot of work.

Additionally (as pointed out by David Browne) you can use Snapshot Replication between Managed Instances and On-Prem SQL. So that is a viable method of migrating as well as the horror that I had envisioned internally of doing bulk ETL from MI to on-prem.

Also, to answer the question directly asked, Managed Instances offer one version of the SQL Engine. You can select a 2016 compatibility level for your databases if you need to limit yourself to those features.

• David Browne - Microsoft: Note that Managed Instance supports Snapshot Replication, which is a reasonable method of migrating a database from Managed instance to a VM, or back on-prem.
• Jonathan Fite: That is a good note, @DavidBrowne-Microsoft, I forgot that one. It wouldn't work for our environment (1TB database, high activity, bad network), but it could certainly work for smaller databases well enough.
• David Browne - Microsoft: Yep. Should work well within Azure, but moving a large database across a slow network you'd probably need to use Transnational Replication initialized with a snapshot downloaded to alternate snapshot location.
• Jonathan Fite: it was just unappealing to us as a valid method. And the slow network was on our end. If I'd had to do it for real, I would probably stand up a SQL VM in Azure and do the snapshot replication to it, then fail to that VM, then do a traditional backup/restore with tlog and fail back to on-prem. But fortunately never had to do that a few hiccups and sub-optimal choices later and we were live in the cloud (6 months now).