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.
Best Answer
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.