locked
Data Load from Azure SQL Data Warehouse to SQL Server on Azure VM RRS feed

  • Question

  • We need to load data from Azure SQL Datawarehouse to SQL SErver in Azure VM (D14 or D15). We are currently using Azure Data Factory pipelines (Copy activity) for this. We observe that the performance of the copy activities is not up to the mark. I need your inputs on other potential options to load data faster. Are there some settings/configurations that we should double check to make sure what we have is the most optimum if we go ahead with Data factory (We are using the max DWU and the best VM configuration that our budget allows)?

    Please share the results if anybody has evaluated Data Factory against SSIS for the above scenario.

    Thanks in advance

    Saturday, July 29, 2017 9:30 AM

All replies

  • SSIS might be faster but I would leave this up to your testing. 

    Whether you go for SSIS or ADF is up to your overall architecture and how often you are going to load data.

    for SQL DW the recommended Import/Export interface is polybase as it is the only one that runs in parallel. However, It could be harder to import the data again into your SQL VM

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, July 31, 2017 9:28 AM
  • We did a few POCs on the above and the below are our observations:For a single entity, our observation is that the performances of SSIS and ADF are comparable. However for multiple entities, SSIS performs better as the ADF gateway becomes a bottleneck. If one among the multiple entities being loaded in parallel is voluminous, the Gateway chokes while loading it and puts all other loads on hold. In SSIS, the parallelism works better and we observed that the overall performance is better in our case for SSIS.

    Thursday, August 10, 2017 11:01 AM