Data migration Assistant/Azure Data sync/Azure Data factory Limitations

salilsingh-9961 346 Reputation points
2021-04-14T06:20:45.783+00:00

Hi Team,

Could you please provide me with answers of below -

  1. 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?
  2. Are there any limitations/disadvantages of using Microsoft Data Migration Assistant as above?
  3. What are the limitations of Azure Data sync service when migrating/syncing data from on-premises SQL Server db to Azure SQL Server db?
  4. 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?
  5. 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)?

A link for answer of each of above questions would be really helpful.

Thanks,
Salil

Azure SQL Database
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
496 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Oury Ba-MSFT 16,636 Reputation points Microsoft Employee
    2021-04-15T00:12:41.217+00:00

    @salilsingh-9961 Welcome to Microsoft Q&A and Thank you for posting your question.

    1. 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.
    2. 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.
    3. 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.
    4. 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 .
    5. 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.