Fastest way to load data from SQL server to Azure synapse

sakuraime 2,321 Reputation points
2021-01-18T02:14:26.54+00:00

what's the fastest way to load data from SQL Server to Azure synapse ....
suppose the SQL Server is on-prem...

  1. Data factory , using SQL Server as Source and Azure synapse as sink will give a best performance ?? or need to SQL Server -> to a blob first ?
  2. Any incremental loading method ?
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,422 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,816 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HarithaMaddi-MSFT 10,136 Reputation points
    2021-01-18T12:28:24.937+00:00

    Hi @sakuraime ,

    Welcome to Microsoft Q&A Platform. Thanks for posting the query.

    As per my understanding, selecting Polybase option in data factory internally splits the data from source into files in storage account selected as staging and it does in a optimized parallel way. Hence, selecting this option directly in data factory is recommended when we have source as on-premise SQL. However, there are minor performance enhancements that can be done when doing the entire process in manual way depending on the data size and variety. Ex: Copy command can be used if data from SQL is copied into blob initially and then loaded into Synapse which gives better performance. This article explains few things clearly in this regard. Also, please refer to the best practices document for loading data into Azure Synapse. Various incremental loading approaches are listed in this document which can be implemented as appropriate.

    Please let us know for further queries and we will be glad to assist.

    --

    • Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification.