Data copy from onprem sql server database to azure sql database

Question

Hi Team, I am seeking recommendations on transferring certain tables from an on-premises Microsoft SQL Server to an Azure SQL Database. Some of these tables contain a substantial amount of data (ranging from approximately 80 million to 150 million records) and require a full refresh daily. My initial approach involved creating a pipeline with the copy data activity, but it appears to be taking 35 minutes for a single table with around 78 million records. Can you please provide guidance on the most efficient method for copying these tables? Thank you for your assistance. Thanks, Jaganath

Answer 1

@Jaganath Kumar Thank you for reaching out. My understanding is that you want to copy specific tables from your on-premises SQL Server database to an Azure SQL Database.Azure SQL data sync is an option too to. Set up Azure Data Sync between your on-premises SQL Server and Azure SQL Database.

• Configure sync groups for specific tables.
• It synchronizes data bidirectionally.

https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-sql-server-configure?view=azuresql

Please note that SQL Data sync does not support Azure SQL managed instance and synapse.

Please do also check the limitations https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql#sync-req-lim

Hope this helps. If not, please do comment below so we can further assist on this matter.

Regards,

Oury

Answer 2

You can use the free Microsoft tool DMA = "Data Migration Assistant", see https://learn.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver16