topic Re: Copy data for on-prem SQL server in Pipelines

Copy data for on-prem SQL server

ScottPowellECU — Tue, 06 Jun 2023 18:47:14 GMT

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 hoping copy data is significantly faster.

Thanks!

Scott

Re: Copy data for on-prem SQL server

AlexPowers — Tue, 06 Jun 2023 20:16:21 GMT

Hey Scott! Saw your comment in another thread with respect to being able to support on-prem connectivity via pipelines, this is a top priority to bring to Fabric as part of the team's path to General Availability. There will be some improvements brought to Dataflows to help with ingestion (leveraging the same underlying copy activity / fast copy infrastructure of pipelines) there are still few items to resolve to get us there but if you can help the team out by voting over on Fabric ideas so they can best prioritize the necessary work: https://aka.ms/fabricideas

Re: Copy data for on-prem SQL server

Scott_Powell — Sun, 11 Jun 2023 20:32:48 GMT

Hi AlexPowers , appreciate the reply - thank you very much! I'll definitely vote in the fabric ideas area, but I'll also throw out there: wihtout this capability, it's really limiting your customers to ones that are already buying Azure from you, and doesn't help attract new customers who (like us) can't move our on-prem SQL server to the cloud easily.

I appreciate the response - thank you!

Scott

Re: Copy data for on-prem SQL server

bcdobbs — Mon, 12 Jun 2023 17:22:09 GMT

What sort of transfer rates ar you getting in gen2 dataflows? Mine just took an hour to load a 2gb table from On Prem SQL. Trying to work out if it's our environment or just how it is.

Re: Copy data for on-prem SQL server

Scott_Powell — Tue, 13 Jun 2023 13:22:55 GMT

I'll send you some info once we get the firewalls opened. What I can say is this - if I look at Gen1 dataflow performance, it's nowhere even close to what the "COPY" utility is supposed to do. Based on this documentation ( https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-performance ) I should be able to move our entire DW over in under an hour. That's orders of magnitude faster than what Gen1 dataflows feels like.

Hope this helps!

Scott

Re: Copy data for on-prem SQL server

Scott_Powell — Wed, 21 Jun 2023 19:29:43 GMT

FYI bcdobbs - the 2GB / hr seems roughly in line with what we get with stuff going through the gateway server. That's with a 3 gateway cluster but I haven't spent any time getting fancy with things like trying to partition the data (via incremental refresh) to get multiple loads running in parallel.

Hope this helps,

Scott

Re: Copy data for on-prem SQL server

MisterSmith — Fri, 14 Jul 2023 14:45:18 GMT

Hi Alex

Do you know if the copy data activity in pipelines to on prem systems is available yet? And if not are there any timescales as to when it will be?

Thanks

Neil

Re: Copy data for on-prem SQL server

makromer — Fri, 14 Jul 2023 15:50:43 GMT

We are working on enabling OPDG for Copy Activity to provide the same capabilities in Fabric data pipelines as already existing today in ADF with SHIR. The plan is to bring this capability to Fabric very soon during public preview.

Re: Copy data for on-prem SQL server

ScottPowellECU — Fri, 18 Aug 2023 21:27:34 GMT

hi AlexPowers , just wanted to follow up and see if there's been any progress on using pipelines and the copy activity to bring on-prem SQL server data into Fabric. Been a couple of months, hoping we're getting close. Gen2 dataflow performance is just too slow for the amounts of data we're hoping to move to Fabric.

Thanks!

Scott

Re: Copy data for on-prem SQL server

Scott_Powell — Sat, 19 Aug 2023 01:15:45 GMT

hi makromer , just wanted to touch base and see if there are any updates on when the fast copy capability to move data from on-prem SQL server to Fabric is. Coming soon?

Thanks!

Scott

Re: Copy data for on-prem SQL server

rwolburg — Wed, 23 Aug 2023 23:15:16 GMT

Hi,

Can anyone can share the steps to make a "copy" of a table to the lakehose using dataflow while the feature in datapipe is available?

thanks in advanced

Re: Copy data for on-prem SQL server

Trystan — Wed, 13 Sep 2023 03:23:14 GMT

Hey makromer, any updates on when this might be available?

Thanks.

Re: Copy data for on-prem SQL server

Trystan — Wed, 13 Sep 2023 03:24:41 GMT

Hey AlexPowers, any updates on when this might become available?

Thanks

Re: Copy data for on-prem SQL server

Anonymous — Mon, 18 Sep 2023 21:03:40 GMT

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.

Re: Copy data for on-prem SQL server

Scott_Powell — Tue, 19 Sep 2023 17:56:37 GMT

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

Re: Copy data for on-prem SQL server

Anonymous — Tue, 19 Sep 2023 18:07:51 GMT

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 ?

Re: Copy data for on-prem SQL server

Anonymous — Fri, 29 Sep 2023 13:58:00 GMT

Hi AlexPowers ,

Thank you for this statement as it makes me feel much better. "this is a top priority to bring to Fabric as part of the team's path to General Availability."

As-of today, is-there any news on when on-prem connectivity via pipelines would become available?

Re: Copy data for on-prem SQL server

Dhicks — Mon, 20 Nov 2023 15:57:05 GMT

Hi @AlexPowers

Fabric has now gone General Availability, do you have any idea of the timeframe to support Copy Data activity for pipelines, through a On Premise Data gateway?

Thanks

Re: Copy data for on-prem SQL server

Scott_Powell — Tue, 21 Nov 2023 16:21:13 GMT

Hi all, quick updates on this topic.

1. Per the Fabric roadmap ( https://learn.microsoft.com/en-us/fabric/release-plan/data-factory#opdg ), the ability to "fast" copy from on prem to the Fabric service is estimated for Q1 2024. Note however this talks about requiring that the gateway servers be "behind a vnet". No clue exactly what this means or how difficult this is to set up

2. During Ignite, Microsoft announced database mirroring ( https://blog.fabric.microsoft.com/en-us/blog/introducing-mirroring-in-microsoft-fabric?ft=All ), a kind of next-gen change data capture. SQL server on-prem is not ready yet but is one of the next databases they'll be targetting.

Hope this helps,

Scott

Re: Copy data for on-prem SQL server

Dhicks — Wed, 22 Nov 2023 10:34:29 GMT

Hi just for clarity I think the above "Note however this talks about requiring that the gateway servers be "behind a vnet" " is referenced in the Fabric link above:

"This feature will enable data pipelines to use Fabric data gateways to access data that is on-premises and behind a VNet." I have bolded the and because I think this should be an OR ie this is two different scenarios where gateways can be used.