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.

jack
  • 379
  • 2
  • 5
  • 18
  • The answer from Kin Shah here worked for me https://dba.stackexchange.com/questions/55055/how-to-move-a-database-from-sql-server-2012-to-sql-server-2005 – jack Jan 23 '20 at 14:03

1 Answers1

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.

Jonathan Fite
  • 8,666
  • 1
  • 23
  • 30
  • 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 '19 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 '19 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 '19 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 '19 at 14:44