Copying on-prem data to a warehouse fails because gateway is bypassed for staging storage account

[tsunloan]

There is a bug in the copy activity when copying data from an on-prem source to a Fabric warehouse.

BTW I have a support ticket open for this on which I'm gaining zero traction despite providing intricate detail.

My pipeline is set up as follows:

• Source: Azure Database for Postgres SQL (connecting via on-prem gateway)
• Destination: Warehouse within same workspace
• Staging: External Azure data lake gen 2 storage account (connecting via on-prem gateway)

Result:

• Data is successfully retrieved from the Azure postgres DB via the on-prem dateway
• Data is successfully copied to the staging folder in the Azure data lake gen 2 storage account via the on-prem gateway
• Data is not copied from the staging folder to the Fabric warehouse as it bypasses the on-prem gateway when connecting to the storage account. This is despite the connection being configured to use an on-prem gateway and succeeding for the step above.

Storage account logs

If there's anything in Fabric that actually works without an elaborate workaround then I'm yet to find it 🙄.

[sunil_sabat]

@tsunloan 

In may case, it appears that my gateway connection stayed up and I see my data in Fabric DW as below.  Root cause in your scenaro  seems to be connection  switch from gateway. We need to look at it further.  I will update support team on this.

I will update  support team on this. When can we talk? Let us sync up off-line.

Thanks,

Sunil

[bin_curato]

Hi, did you have any info to solve your problem? I am very curious about the possible solutions proposed.

[sunil_sabat]

Hi @tsunloan 

I am from product group. I was able to copy mysql  on-prem to Fabric DW using gateway. I had to make sure staging also connects through gateway. If needed, we can hop into call and I will go over how my setup works.  I see you  have not been able to get proper help. You rightfully asked for product group help. Here we are. 

Source settings:

Staging Settings:

[tsunloan]

HI @sunil_sabat . I would love to have a call to run through it with you. I'll send you a PM with my contact details.

The staging storgage account is configured to use the on-prem data gateway and the storage account logs show that the gateway is used to connect for most of the connections from the pipeline but at the very end of the flow, the data warehouse SQL service attempts to connect directly to the storage account without going via the gateway and fails.

Staging account config. Test connection succeeds and I can browse to select the storage path

Storage account logs for pipeline execution. 

It copies the data to the staging account successfully but then the warehouse fails to retrieve it from staging as it's connecting without using the gateway. The connections in red are from the Azure SQL service (warehouse).

[sunil_sabat]

Thanks @tsunloan  for the update. Can you quantify AT THE END?  Is it 1hr? We are curious about when it switches to connect without gateway.  

Sunil

[tsunloan]

It's within 8 seconds during the same activity run.

• 10:30:40 - Data is retrieved from the DB via the gateway and copied to the staging storage account via the gateway (success)
  These are the actions highlighted in green in the storage log screenshot
• 10:30:48 - Azure SQL Service (destination warehouse) attempts to retrieve data from the staging storage account without using the gateway (fails)
  These are the actions highlighted in red in the storage log screenshot
• 10:30:54 - Activity flagged as failed. Data is deleted from the staging storage account via the gateway

[Abhishek_Narain]

@tsunloan While the team investiagtes and fixes the issue, can you use two copy activities instead? 
Copy 1: On-prem source to LH (connections use on-premises data gateway)
Copy 2: LH to DW (connections do not use on-premises data gateway)

[tsunloan]

This doesn't work either as it compains about the data type... The source table I'm testing is as simple as it gets having nothing but two varchar columns.

I did have some luck going from Azure Postgres -> Lakehouse filesystem -> Data warehouse with staging disabled.

It works for a single test table but I'm yet to try and pump hundreds of tables through it (our target end state).
It's rather silly going from Structured -> Unstructured -> Structured.

It's also silly introducing yet another workaround to cater for another fundamental product design flaw in Fabric.

Our goal here is simplify our data arcthitecture by replacing Azure Synapse with Fabric... It's no simpler with all of these workarounds.

[tsunloan]

An update on this

By design, Fabric data warehouse does not support the on-prem data gateway 😡.

This is the advice I have received from the data warehouse product team via the support engineer and an SME assigned to MS support ticket 2404030030000776.

Despite the product clearly advertising that the use of the on-prem gateway is supported, and the support engineer confirming that data warehouse is listed as supporting the on-prem gateway, it does NOT support it BY DESIGN. 🤦‍🤦‍🤦‍

No commitment has been made to fix this issue, and instead the SME assigned to my latest ticket is requesting that they change the advertising and is also putting in a design change request rather than a critical bug report.

I have requested to speak to a product manager for Fabric data warehouse on several occations through multiple channels but the request has been either ignored or denied at every step.

@v-nikhilan-msft , perhaps you could request that a Fabric product manager contact me.

At this stage, I have no option but to remove Fabric from consideration as a data analytics and warehousing platform.

[v-nikhilan-msft]

Hi @tsunloan 
Thanks for using Fabric Community.
Can you please provide the support ticket details here? We will track it internally.

At this time, we are reaching out to the internal team to get some help on this.We will update you once we hear  from them.
Thanks 

[tsunloan]

Ticket 2404030030000776

[v-nikhilan-msft]

Thanks for the details @tsunloan 
I have reached out to you in private messages. Can you please check and respond back?
Thanks

[v-nikhilan-msft]

Hi @tsunloan 
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond back with the more details and we will try to help.
Thanks

[tsunloan]

No, there's no resolution.

I wasn't contacted again by MS support until after hours Friday night and I responded immediately with the same level of intricate detail that I supplied to the previous support engineer. I'm yet to hear back from them.

Support has been terrible from Microsoft's end. I don't hear anything for 48 hours then I get an after hours email requesting the same thing that I'd already supplied then nothing again for another full business day. Still waiting.