Migrate on-prem SQL Server database to Azure SQL database

Question

We're in the process of a server migration from an on-prem server (Win2008R2) to Azure PaaS.

To move the DBs, we used the Microsoft Data Migration Assistant (DMA) tool, which worked great and we can connect to the migrated Azure DB via SQL Server Management Studio.

Considering:

• Made quite a few changes to the migrated Azure DB (tables, stored procedures, indexes) to work with the apps in Azure
• Combined multiple on-prem DBs into one DB in Azure via DMA to save costs
• On-prem DB is continually being modified by insert/update operations (multiple tables) during the migration process

Question: what is the best and fastest way to migrate data (all vs missing/updated) considering the above?

Answer 1

I disagree with all the answers here.

If you are running on Win2008R2 there is a high chance that you are on an old SQL Server (2008? 2012?) which are both deprecated and unsuitable for Azure SQL Database. And probably the application is also old and not suitable for the Cloud in general. I suggest you a good testing phase.

Here my to do list:

1. Upgrade SQL Server to SQL Server 2016 on-prem and test if all your queries are still running correctly
2. Test how ready is your SQL Server to go to Azure SQL Database through Microsoft Data Migration Assistant (DMA) tool or the new Azure SQL Migration extension for Azure Data Studio (came out his month).
3. Don even think for a second that merging databases will reduce your overall costs. Decide if going multi-tenant or single-tanant not because of the price of the database.
4. Plan for hours of downtime based on the size of the migration. Don't migrate while your database is modified. Expect downtime. The best way is to take a backup of the day before and then resume the logs.

and test like crazy. This is not gonna be easy because the app is old.

Good luck.

Answer 2

Visual Studio also has a great tool for comparing both schema and data between two databases on different servers.
It can then update the target database with any changes after which you can switch over to use the Azure DB.

This method would require downtime of around 5-30 minutes depending on amount of data, but that might be acceptible depending on your requirements.

Answer 3

I would recommend you to migrate first only the schema of your on-premises databases to Azure SQL Databases and then let Azure SQL Data Sync to migrate the data to Azure and keep it updated on Azure SQL Database.

My suggestion to start with an empty schema on the Azure SQL Database side is because when SQL data Sync finds data on-premises and on Azure it start comparing both databases and that consumes a lot of resources.

On the initial sync SQL Data Sync may consume a lot of resources on the on-premises database server even when having an empty schema on the Azure side, for that you can use SQL Server Resource Governor to cap the CPU used by the data sync sessions in your on premises SQL Server, and this way avoid big performance impact possibly affecting database users.

When you are ready, you can switch your users (gradually or not if SQL Data Sync is on bi-directional mode) to Azure. Once your users have been migrated, you can then remove the member database (the on-premises database) from the SQL Data Sync configuration and stop SQL Data Sync operation.