Forum Discussion

ScottPowellECU's avatar
ScottPowellECU
Advocate II
3 years ago

Copy data for on-prem SQL server

Hi, does anyone have an idea of when the "Copy data" activity might work for on-prem data sources (in our case SQL Server)? I know we can use Gen2 dataflows for this - but they are very slow. Really ...
Need Help
Pipelines
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Datamarts are you friend here  - it sounds odd but they are a great tool in the Fabric toolbox

Create a Datamart and load you data from your on prem SQL server to the Datamart

Then in a pipeline use a copy activity to move the data from the datamart ( which appears as an Azure SQL Server) to the target Warehouse.

It works really well and we use it estensively as Gen2 Dataflows do not have the reliability or performance that we need.

  • Scott_Powell's avatar
    Scott_Powell
    Advocate IV
    2 years ago

    Anonymous - curious how do you get your data into the datamart performantly? I thought those used dataflows, which is still a huge performance issue for us. I need to move just over a Terabyte nightly. Understand once it's in a datamart it might be fast, but not sure how to get the data in there quickly to start with.

     

    Thanks!

    Scott

     

    Thanks,

    Scott

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi - We use dataflows to load to datamarts  - they are reasonably fast - do have a limit of 100 GB though.

       

      Do you need to load all of that terrabyte of data each day ? - could you not just do a delta load to the datamart ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    你好，请问数据仓库从datamarts复制数据的具体配置是什么样的呢