locked
Ingest SQL Azure with millions (max 10) of Records RRS feed

  • Question

  • Hello,

    I created a pipeline in Azure Data Factory to copy 1 million of records from a local SQL Server to a Azure SQL Database (S2).

    The migration is fast at the beginning but after a while it becomes slow, I believe it's the number of insert and the LOG Io limit reached, does this mean SQL Azure is not a good candidate as a Data Warehouse Database? Is there another way I can ingest the data without hitting this limitation?

    Thanks


    Monday, February 17, 2020 6:10 PM

All replies

  • Please check if this guide on performance helps - https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-performance

    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Monday, February 17, 2020 6:40 PM
  • Thank you so much.

    I think my issue it's not with ADF performace, but with the ingesting of millions of Rows to SQL Azure.

    I'm just wondering of someone else is facing the same issues and if there is a solution.



    Monday, February 17, 2020 7:04 PM
  • You mentioned you are using a local server , I am assuming that you are using the SHIR also , please do have a look on the SHIR as it may be the reason for the bottleneck . 

    You mentioned that Azure SQL can be the bottleneck , that can be the case , but then you can try to upgrade to the next tier and see if that helps .  

    Also there is a batch setting in the copy activity which you can try and see if that helps . 

    Please let me know how it goes .


    Thanks Himanshu

    Tuesday, February 18, 2020 6:18 PM
  • I was looking into a different thread and I did played the around with 1 million records on SQL azure and I must say it worked really fast ( source and sink were on Azure in this case ) 

    https://social.msdn.microsoft.com/Forums/en-US/fb55d798-8f9a-4106-84ac-a50e92f16e2f/foreach-loop-parallel-processing-limitationbug?forum=AzureDataFactory

    Thanks Himanshu

    Wednesday, February 19, 2020 1:04 AM
  • Hi Himanshu,

    thank you so much.

    What tier did you use?

    Thanks

    Wednesday, February 19, 2020 4:45 PM
  • Hello ,

    Since moving to upper tiers is going to cost money , I could try to start with a baseline which you are using and then move to the upper tier .

     


    Thanks Himanshu

    Wednesday, February 19, 2020 11:02 PM