Rollback strategy for Cloud migration: SQL databases

Question

Hi,
we are planning a cloud migration of our current on-prem Sql server db.
our on-prem is a SQL 2012 system.

we are exploring the option to migrate to either a) SQL Managed Instance 2019 or b) Sql Server 2019 on Azure VM

One of the factors to decide on the options, is which one supports easier rollback.
On go-live, if for some reason we have to roll-back after a few hours of go-live,
will i able to take a backup from Sql MI or SQl Server 2019 VM and restore it back to our on-prem 2012.

Please can anyone share their experiences.

Thanks

Answer 1

You can't take a native backup of Azure SQL Managed Instance and restore it to a SQL Server 2012 instance. You better add one step in your migration plan, to upgrade first to SQL Server 2019, make sure all works well and then plan to migrate to Azure Managed instance, So in case of a rollback you can rollback to SQL Server 2019 only,

The same with the bacpacs, you can restore them to SQL Server 2019 with some adjustments needed sometimes. You cannot restore them on SQL 2012.

Answer 2

I have been tasked the same by customers because there is always some opposition inside organizations to migrate databases from SQL Server to PaaS databases.

The rollback plan for SQL Server on-premises to SQL Server VM on Azure (IaaS) is easier, just have them on the same SQL Server version/build and backup to URL/restore could make it easy.

However, with PaaS is not that easy as it may not be possible to restore native Azure Managed Instances backups on SQL Server instances as mentioned here. So you may want to rely on bacpacs as a way to "backup" data on a Managed Instance and restore it on a SQL Server instance, but that SQL Server instance should be version 2019 with all cumulative updates applied. Test it because even with bacpacs surprises may occurs based on my own experience as Azure Managed Instances and PaaS databases builds are way ahead of the latest cumulative available for SQL Server.

One suggestion if you finally decide to migrate to Azure Managed Instance, make sure the log rate of your databases on SQL Server premises is not higher than the log rate limit for Managed Instances.

Hope this helps.

Answer 3

Thanks Alberto.

Just so i am clear on your response, let me rephrase what you are saying:

Our current on prem is SQL 2012 and the requirement is to migrate to SQL 2019 on Azure VM or SQL MI.

So if we go down the path of using SQL 2019 Azure VM IaaS and decide to rollback the migration after a few hours of going live, you are saying we can't take a backup from the SQL 2019 VM and restore it to SQl 2012 on-prem because of the sql version mismatch?

Similarly if we choose MI, the bacpac from MI will not be consistent enough to be restorable onto the onprem 2012?

Apologies if i misunderstood, but please clarify.

Thanks