1

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.

Improve this question
1

1 Answer 1

Reset to default
4

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.

Improve this answer
4
  • 2
    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.
    – David Browne - Microsoft
    Dec 17, 2019 at 13:57
  • 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.
    – Jonathan Fite
    Dec 17, 2019 at 14:23
  • 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.
    – David Browne - Microsoft
    Dec 17, 2019 at 14:37
  • 1
    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).
    – Jonathan Fite
    Dec 17, 2019 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.