Need to Optimize Data Transfer from Azure SQL DB(Source DB) to Azure SQL DB(Sink DB), as throughput is very less.

ROHITH JOGIPARTHI 1 Reputation point
2021-09-27T06:59:40.373+00:00

I am trying to do the data transfer from my staging DB to Master DB where both are AZURE SQL Databases with 250 P2 configurations on source as well as on sink using Auto resolve default integration Runtime . And the Maximum throughput I could see is at max 2.2 MB/s which is very less for a cloud to cloud data transfer. I have tried for different write batch sizes and degree of parallelisms, but had no effect at all. whether Cloud unit can throttle the data transfer and check for the bandwidth??.Any Insights will be helpful....
135425-image.png

135465-image.png

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,617 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,891 Reputation points MVP
    2021-09-27T12:49:44.153+00:00

    For that workload, that is the expected performance for P2 database, run below query to know more about the hardware 

    SELECT * FROM sys.dm_user_db_resource_governance

    Check out the max_db_memory and the number of cores.

    Read this experiment to see how RAM and cores impact the performance of SQL Data Sync.

    My suggestion is to use P4 at least, and recommended P6 while Sync is taking place to improve performance. Depending if you are syncing once a day or twice, you may want to scale up tier before syncing and then scale down.