Azure Data Migration Service - source and target database sync before cut off

Question

Hi,

I have used the SQL Azure Data Sync service before and I was reasonable happy with what it does.

However, this time I need to look into on-prem migration of a SQL Server instance with deployed CLR's. The DMA tool clearly pinpointed the presence of the CLR in the source DB and recommended me to after reviewing certain CLR configuratuions go with SQL Managed Instnace or SQL Server on VM.

The Sync service I used before only works with Azure SQL henceforth I started investigating options for DMS. IT all looks pretty good yet there is one thing I don't understand and the documentation does not mention anything about it - and that is how changes are propagated to the tyarget database after the migration is "completed" yet the "cutoff" is not started yet.

What I am really curious about is that with DMS is it possible to run the source and the target DB in paralell and te DMS will take care of periodic update/sync of the target DB or when the DMS says "completed" then it will not be doing anything anymore just sitting there waiting for the cutoff to start?

If this is the case I wonder what would be the best option to establish a sort of sync between the source and target DB? I was thinking of perhaps setting up the two DB instance in a clustered/slave-master/etc manner and the SQL engine would then take care of the sync - but as I am not a SQL DBA expert I am not sure what functionality of the server I should be looking at in order to establish such option. Also there is a significant version difference between the source (2008) and the target (latest/2019).

I also looked into the other Azure Data offerings (DataBricks/DataFactory) but their use case is not really for such scenarios. I am also sort of aware that with SSIS such logic can be implemented to ensure that the source is matched up - but the database is lrather large with plenty of tables and routines.

If someone could shed some light on this matter and perhaps point me to the right direction that would be great.

Tamas

Answer 1

Hi, @tamasoz Thanks for the ask and using the Microsoft Q&A platform.

As we understand the ask here is to know if it is possible to run the source and the target DB in parallel will DMS take care of periodic update/sync of the target DB.

DMS service uses a backup and restore method for online migration that minimizes the downtime to the cutover point. It works below.

1. Use FULL database backup for the initial load.
2. Start uploading and restoring log backups as supplied.
3. This process continues until you decide on the cutover.
4. When you are ready to do the cutover expect to below
   a. Stop all incoming traffic to the source database
   b. Take a final log backup
   c. Wait until DMS restores that
   d. Call cutover to complete the migration.
5. During the logs applied state the target database is not read/writable, it will be in restore state until the cutover completes

Lets us know for any further questions

Regards
Geetha

Answer 2

Thank you for the answer Geetha!