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

To move the databases, 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.


  • Made quite a few changes to the migrated Azure database (tables, SPs, indexes) to work with the apps in Azure
  • Combined multiple DBs from the on-prem server into one DB in Azure via DMA to save costs
  • One on-premise database is continually being modified by insert and update operations (multiple tables) during the migration process
  • When fully switching hosting in Azure we need to make sure all data is up-to-date. This will be done very early in the AM during which on-prem IIS websites are stopped assuring no DB operations are made, giving us a short window to migrate missing data

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

Can this be achieved by following the below steps?

  1. Create a new empty Azure SQL database (to later hold all the tables of 5 on-prem databases)
  2. Via Microsoft Data Migration Assistant (DMA), migrate only the schema of that one on-prem database which is continually being modified by insert/update operations
  3. Via SQL Data Sync Agent, set up auto data sync (e.g. every 30 minutes) between the on-prem and Azure database
  4. After data sync up and running, via Microsoft Data Migration Assistant (DMA)- migrate schema and data of the rest of the on-prem databases (no data changes during migration process) into the single Azure database previously created and used for syncing


After enabling Data Sync in the Azure portal, the sync was unsuccessful due to the below logged errors:

  1. Member Database: MyAzureDb -> Tombstone cleanup failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:18456, Message: Login failed.
  2. Member Database: MyAzureDb -> Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:18456, Message: Login failed.

To fix 1 and 2 above tried: ALTER ROLE DataSync_executor ADD MEMBER [myUser];
but got Cannot add the principal 'myUser', because it does not exist or you do not have permission.

  1. Member Database:MyOnPremDb: -> Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:325, Message: Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. SqlError Number:156, Message: Incorrect syntax near the keyword 'AS'.

The current compatibility level of the on-premise database is 80. Not sure what impact setting it to 100 will have to the production databse/apps.

1 Answer 1


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.

  • 1
    +1 Aberto for asking to migrate schema first.
    – Shanky
    Commented Oct 1, 2018 at 6:28
  • Thank you Alberto. Something that is not clear for me: do I need to recreate the schema in Azure (via a new database) of the already migrated database in Azure? Or migrate the schema of all the on-premise databases via Microsoft Data Migration Assistant (DMA) into one single Azure database? As per my post, I've done quite a few modifications to the migrated db in Azure, do these modifications need to be done again?
    – alex
    Commented Oct 3, 2018 at 11:15
  • Changes you have made to the Azure SQL Database need to be replicated on the on-premises database for SQL Data Sync to work. Database tables that will be replicated need to be identical in terms of structure on both sides. You can truncate data on all tables of the Azure SQL Database to make the initial sync of SQL Data Sync faster and less resource intensive on both ends (on-premises and on Azure). Commented Oct 3, 2018 at 14:30
  • When installing the SQL Sync Agent (microsoft.com/en-us/download/details.aspx?id=27693) on the on-premise server win2008r2, it requires SQL 2012 software to be pre-installed, see here: imgur.com/a/jrLXxII. Are those 2 prerequisites ok to install on the production server? considering we use the SQL sever 2008?
    – alex
    Commented Oct 4, 2018 at 15:07
  • Both are part of SQL Server 2012 feature pack. You need to install them no matter what version of SQL Server you have on-premises. Those 2 prerequisites should not impact your on-premises SQL Server instance. Commented Oct 4, 2018 at 18:57

Your Answer

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

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