Rollback plan for migration from on-prem SQL server 2016(2 node AG Cluster) to Azure SQL MI Business Critical

Question

Hi,

Actually migrating from on-prem SQL server 2016(2 node AG cluster) on Window 2019 Server to Azure SQL Managed Instance Business Critical node. Configured DMS replication and syncing the db, will do until the cutover. This process includes upload of full backup and trx log backups until the cutover.

Now the question is if post cutover, lets say after 2 hours or 2 days, some issue happened and unable to fix on SQL MI. And if we are planning to go to on-prem, what would be the plan here ?

1. Will stopping all applications, taking full copy only backup from azure MI and restore to on-prem will work ? Because we changed DB version while migrating to Azure MI
2. If point 1 won't work, what is the available option in this roll back case ?
3. If point 1 work, do we have document anywhere explaining it ?

Answer 1

Azure SQL Managed instance is always running on the latest stable version of SQL Server database engine and that is currently version 2022; you can not restore a backup taken from 2022 to your on-premise SQL Server version 2016, that is not possible.

Answer 2

Hi Aakash CK(P),

Thank you for posting in the Q&A Forums.

Ensure that a full backup of the local SQL Server 2016 (2-node AG cluster) has been performed, including databases, transaction logs, configurations, etc., before the migration begins.

Before the actual migration, simulate the rollback process in a test environment to ensure that it can be executed quickly if necessary.

During the migration process, migration progress, resource utilization, and any potential issues were monitored in real-time using appropriate monitoring tools.

Ensure that all operations and steps during the migration process are documented in detail for easy reference during the rollback.

Stop the migration operation as soon as any issues or potential risks are detected during the migration process.

Using the local SQL Server 2016 (2-node AG cluster) backup data backed up before the migration, follow the backup recovery process to restore the database to its pre-migration state.

After the recovery is complete, perform integrity verification and performance testing on the database to ensure that the database can operate normally and meet business requirements.

According to the importance of the business and acceptable downtime, set a reasonable rollback time limit.

Ensure that sufficient hardware and software resources are available during the rollback process to avoid rollback failure due to insufficient resources.

After the rollback is completed, conduct an in-depth analysis of the reasons for the migration failure, identify the problem areas and formulate appropriate improvement measures.

Based on the analysis results and rollback experience, the migration solution is continuously optimized and improved to increase the success rate and efficiency of future migrations.

Best regards

NeuviJ

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.