question

ROHITHJOGIPARTHI-4030 avatar image
0 Votes"
ROHITHJOGIPARTHI-4030 asked ROHITHJOGIPARTHI-4030 edited

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

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-data-factoryazure-sql-database
image.png (60.6 KiB)
image.png (63.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ROHITHJOGIPARTHI-4030,

Just checking in to see if the below suggestion/information from @AlbertoMorillo was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·

1 Answer

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered ROHITHJOGIPARTHI-4030 edited

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.






· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AlbertoMorillo - Thanks for the response. But, when the same amount of Data is transfered from On PRem DB to Staging DB with 125P1 config. wE were able to achieve the data load in 30 minutes. Whereas here, for data transfer within cloud from an Azure SQL DB(staging) to another Azure SQL DB(master) it is taking close to 1hr 12 minutes with 125P1 or 35 minutes with 250P2 Configs on source and sink. And, does the number of Data Movement Units/Cloud Movement Units utilized have affect, where can we find them and configure.

0 Votes 0 ·