@salilsingh-9961 Welcome to Microsoft Q&A and Thank you for posting your question.
- Using Microsoft Data Migration Assistant tool, what is maximum number of tables that could be migrated (with data) between on-premises SQL Server db to Azure SQL db?
There is no exact number. DMA consume CPU and memory.
- Are there any limitations/disadvantages of using Microsoft Data Migration Assistant as above?
DMA is a client tool and is dependent on the resources on the machine where it is running. Although DMA has capabilities built in to automatically take care of
foreign keys, triggers, constraints and non-clustered indexes during migration, it is not recommended for large database migrations for which we recommend DMS
that has better scaling and resiliency built in.
- What is the maximum number of tables whose data (as in #3), could be synced from on-premises SQL Server db to Azure SQL db using Azure data sync service?
Specifically for Azure Data Sync, The documented limits are 500 tables per sync group.
- What are the limitations of Azure Data sync service when migrating/syncing data from on-premises SQL Server db to Azure SQL Server db?
Data Sync isn't the preferred solution for the following scenario: Migration from SQL Server to Azure SQL Database. However, SQL Data Sync can be used after the
migration is completed, to ensure that the source and target are kept in sync. for General limitation please see this document for more information .
- Using Azure data factory while incrementally loading data between on-premises SQL Server db to Azure SQL db (as given in https://learn.microsoft.com/en-
us/azure/data-factory/tutorial-incremental-copy-multiple-tables-portal),
what are the maximum number of tables whose data from on-premises SQL Server db could be loaded to Azure SQL db? Apart from the limitations of this approach as
mentioned in above link, what other limitations this approach has (or Azure Data factory has)? ADF uses Self Hosted Integration Runtime (SHIR) to migrate data from on-premises SQL Server and the compute power of SHIR is dependent on the machine where
it is installed. When migrating large volumes of data or large number of tables, make sure to check the network throughput and the compute power utilized by SHIR.
Some of the considerations for SHIR are documented here.
If the response is helpful, please click "Accept Answer" and upvote it, thank you.