Azure Data Factory Activities stuck in "Queued" after configuring Private Endpoints and Managed Virtual Network

Josh Fennessy 26

We've recently configured our Data Factory to connect to Azure SQL databases using private endpoints in our private virtual network. We are using an Azure-hosted IR configured with the managed virtual network preview.

Since completing that configuration, many of our activities (Lookup, Stored Proc, and Copy) are sitting at a "Queued" status for up to and exceeding 2 hours (2 hours 39 minutes is our longest queued activity currently).

What can we look for to help us find the root cause, and are there any other configuration considerations we need to know about for the private endpoint feature?

KranthiPakala-MSFT 46,592 Reputation points Microsoft Employee

2020-10-07T18:38:48+00:00
Hi @Josh Fennessy ,

Welcome to Microsoft Q&A and thanks for your query.

Could you please share few additional details to further understand the issue:

What is your source & sink?

What is the intergration runtime used source as well as sink?

Were you able to test the linked service connection to your Azure SQL DB?

Please share the pipeline runID for which the activities are in Queued status.

Looking forward to your response.
Thank you
Josh Fennessy 26 Reputation points

2020-10-07T19:10:29.633+00:00

Hi, thank you here are my answers:

1.) Not all of our activities queuing are Copy activities (we also have a Lookup activity that queued for over 3 hours), but for our Copy, the Source is Azure SQL and the sink is ADLSGen2 - both have public endpoints disabled and are joined to the same VNet. For non-Copy Activities, our source is Azure SQL

2.) We use the same integration runtime (Azure hosted/managed virtual network) for both the source and sink in the Copy Activity

3.) Yes, tested successfully, and also after queuing, the operations completed successfully

4.) 75357e18-6959-4801-a6c9-6d5c320a7a68 is a pipeline that had a Lookup Activity queue for over 7 hours
862b8c59-bf48-4397-9137-af9e1d83a17e is a pipeline that had two very long queues - one 6+hrs on a Copy Activity and one 3+hrs on a Stored Procedure activity
KranthiPakala-MSFT 46,592 Reputation points Microsoft Employee

2020-10-08T00:01:53.613+00:00

Hi @Josh Fennessy ,

Thanks much for the details. I have escalated this to internal team to dig more into internal logs and see what was the root cause for the delay. I will keep you posted as soon as I have a response from the team.

In the meantime could you please confirm how long will your stored procedure takes if it is executed in SSMS? Another thing would like to know if any of those SQL tables are locked or being used by another process when your activities were fired? Could you please confirm on this? - The reason for checking on this is that the issue is happening only with the activities that involved your Azure SQL tables (Lookup, Stored Procedure, Copy).

Thank you and we appreciate your patience
Josh Fennessy 26 Reputation points

2020-10-08T00:51:45.59+00:00

Hi. The stored procedure in question executes in less than 1 second in SSMS.

There are actually three different databases on two different servers in play in this scenario. Activities against all three databases have experienced the long queue times.

This is a brand new environment and there are no other users in the database at this time and nothing was locked.
Josh Fennessy 26 Reputation points

2020-10-08T00:52:59.867+00:00

Also, we have only experienced the long queue times with activities using our new Azure-hosted IR configured with managed private networking. Any activities using the Default IR seem to run as expected.
KranthiPakala-MSFT 46,592 Reputation points Microsoft Employee

2020-10-08T01:39:12.23+00:00

Hi @Josh Fennessy , Thank you so much for confirming and sharing additional info. It will be helpful to narrow down the analysis. I will verify based on these inputs and will get back to you as soon as I have my findings ready.

Thank you.
kks8589 106 Reputation points

2020-10-08T04:06:03.617+00:00

Hi Kranthi,
Facing the same issue with ADF Managed Virtual Network enabled.
Every time we need to enable the Interative authoring to test the connections.Its taking about 3 min to enable
And after few minutes of idle its again getting disabled.
for simple copy activity the queue time is more than 5 minutes.
After sometime when we run the pipeline its getting failed due to The default Azure IR is always going to failed to get status state

After some minutes of inactivity when the trigger runs all the pipelines are failing due to unavailability of Azure IR
KranthiPakala-MSFT 46,592 Reputation points Microsoft Employee

2020-10-08T22:27:26.263+00:00

Hi @Josh Fennessy , @kks8589

Sorry for your experience. We have escalated this to ADF product team for further investigation and awaiting response. I will keep you posted as soon as I have an update from the team.

We appreciate your patience.
Thank you
Mark Marshall 1 Reputation point

2020-10-10T10:22:55.02+00:00

Thank you KranthiPakala

We are suffering the same problem using a basic Copy Task moving a file from one Blob to another and renaming it.

When triggered or run in debug the pipeline is queued and stays like that.

Look forward to seeing what the product team advise.

Kind Regards
Mark
KranthiPakala-MSFT 46,592 Reputation points Microsoft Employee

2020-10-12T22:24:33.91+00:00

Hi @Mark Marshall ,

Sorry for your experience and thanks for reaching out in Microsoft Q&A forum.

Could you please confirm if you are facing this issue with activities using Azure-hosted IR configured with managed private networking? Incase if it is with default IR and not related to Managed Vnet/Private endpoints, I would recommend you to please open a new thread since there could be different root cause for your issue. Also please do include your pipeline and activity runID's, source & sink data store for the pipelines/activities that are queued for longer duration.

Looking forward to your confirmation.
David Beavon 981 Reputation points

2020-12-28T17:01:01.103+00:00

This issue is impacting us as well (East US datacenter). Our stuff will stay at status "queued" for hours at a time. Nobody on the azure team seems to be watching this stuff...

Has anyone else tried to contact ADF tech support?

They repeatedly emphasize that this IR feature is in "preview" status. It almost seems like they don't want to support it for use in production. Is anyone else attempting to use this in production yet?

I wonder what factors will cause this VNET version of the IR to be so unpredictable as compared to the azure-hosted and self-hosted...

Accepted answer

KranthiPakala-MSFT 46,592 Reputation points Microsoft Employee

2020-10-13T19:24:02.677+00:00

Hi @Josh Fennessy ,

Here is an update from product team about your issue: The CentralUS region auto scale rule has some issues earlier, and product team had fixed this issue. Could you please to retry again and let us know if you are still experiencing the same issue?

@kks8589 You issue sounds a bit different than OP issue. Could you please open a new thread with your details so that the community will have better visibility about your issue and can provide possible solution/recommendations.

@Mark Marshall - Could you please confirm what is the region of your data factory and Azure IR? If it is CentralUS region, then please retry and let us know if you still have the issue. Otherwise please open a new thread with specific details about you data factory and the issue.

Thank you
Please sign in to rate this answer.

1 person found this answer helpful.
HarithaMaddi-MSFT 10,136 Reputation points

2020-10-16T08:20:29.193+00:00

Hi @Josh Fennessy and @Mark Marshall ,

We have not received a response from you. Are you still facing the issue? As suggested from insights from our product team, this is resolved now, please confirm. Otherwise, let us know and we will continue to engage with you on the issue.

Josh Fennessy 26 Reputation points

2020-10-16T12:57:48.753+00:00

Yes, thank you, we’ve retested and things are running much better now. Now more long queueing as of our last test.

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-16T13:10:42.873+00:00

Thanks @Josh Fennessy for confirmation.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-19T08:35:43.773+00:00

Hi @Josh Fennessy ,

We have not received a response from you. 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.

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-21T08:28:23.247+00:00

Hi @Josh Fennessy ,

We still have not heard back from you. Following up to check for any further queries and we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

David Beavon 981 Reputation points

2021-02-04T15:15:06.093+00:00
I opened a couple cases with ADF related to their VNET-hosted-IR. It was a struggle since the feature is still in "preview" and many of the engineers work in the shanghai timezone.

These two bugs are now fixed for us (East US region). They only affected the VNET-hosted-IR ("preview").

Copy activities between azure and azure would become hung (ones that involved storage accounts in particular). This was intermittent and affected us consistently, but less than 5% of the time.. When they became hung they would never recover on their own and it involved manual intervention on our side (or on the shanghai side). Shanghai blamed "Azure batch" for these issues.

The VNET-hosted IR wasn't displaying correctly on the "activities" monitoring screen. The screen would crash and show no results. The other IR's (azure hosted or self hosted) worked fine.

It took a bit of effort to get the ADF team to acknowledge problems. So if/when anyone else has a problem with this "preview" feature, PLEASE be persistent. At first you might hear them respond that there was a "live site" incident or that scale-out rules weren't configured properly or that azure batch was malfunctioning (another service used internally by ADF). These responses aren't helpful (... and it is likely that they aren't accurate either). We had to reopen the case a couple times to finally get to the bottom of things. The more bugs that are fixed, the faster this will become a GA feature (instead of "preview").

Another thing that seems to be resolved over the course of this case is an issue with performance. The first activity that was executed by this type of IR was delayed a long time (10 or 20 mins). This would affect us at the beginning of every day, or after a period of inactivity. But lately our VNET-hosted IR is much more responsive and usually it gets "warmed up" in less than 4 minutes (ie. comparable to the SLA of the other IR's).
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Data Factory Activities stuck in "Queued" after configuring Private Endpoints and Managed Virtual Network

1 additional answer

Your answer