question

ImranMaqsood-9535 avatar image
0 Votes"
ImranMaqsood-9535 asked shahid646 answered

User Configuration - Data Factory is giving error of Database is Pause but synapse DW database is running

Hi...

I have created pipeline to load data from blob storage to Azure Synapse Workspace (Sql Pool).
I am using Copy Data Activity within ForEach Activity. For some of table data is copied successfully and for few it is failing with following error how Next Iteration completes successfully.

Everything was working fine from last 2 months. Today pipeline is failing again and again.

Activity is giving following error:
Error code 2402
Failure type User configuration issue
Details Execution fail against sql server. Sql error number: 40892. Error Message: Cannot connect to database when it is paused.


and Pipeline is giving following error:
Error code : 2200

Failure type User configuration issue
Details : ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: 'xyz.sql.azuresynapse.net', Database: 'EDW_01', User: 'sqladminuser'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Cannot connect to database when it is paused.,Source=.Net SqlClient Data Provider,SqlErrorNumber=40892,Class=20,ErrorCode=-2146232060,State=1,Errors=[{Class=20,Number=40892,State=1,Message=Cannot connect to database when it is paused.,},],'
Source
Pipeline
MASTER_DATA_EXTRACTION


azure-data-factoryazure-synapse-analytics
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ImranMaqsood-9535,

Welcome to Microsoft Q&A forum and thanks for reaching out.
Could you please confirm if your ForEach activity has sequential property enabled or disabled? If it is disabled could you please try to enable and see if that helps to avoid this issue? It looks likes an intermittent network issue with your sink data source.

If the above suggestion doesn't help, please do share the failed pipeline runID and activity runID for further investigation.

Thank you

0 Votes 0 ·

Hi @KranthiPakala-MSFT,

I will give it a try... right now I unable to start Synapse SQL Pool.

It is showing resuming but no response.....

Is there anyway you can help?

BR,
Imran

0 Votes 0 ·
KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered KranthiPakala-MSFT commented

Hi @ImranMaqsood-9535,

Thanks for your response and sorry for the delay.

To start a database, use the Resume Database REST API. The following example starts a database named Database02 hosted on a server named Server01. The server is in an Azure resource group named ResourceGroup1.

 POST https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-name}/resume?api-version=2020-08-01-preview HTTP/1.1

For more info please refer to this doc: Resume compute

84645-image.png

In the past I have noticed the above issue occurs when two simultaneous jobs starts immediately after resuming the DW. One of the jobs executed successfully, however the other one fails with the following error: Execution fail against sql server. Sql error number: 40892. Error Message: Cannot connect to database when it is paused.

Please try with sequential processing in your ForEach activity and hopefully that should help avoid this issue.

Do let us know how it goes.

Thank you



Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.




image.png (22.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ImranMaqsood-9535,

Just checking in to see if the above suggestion was helpful. If this answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·
ImranMaqsood-9535 avatar image
0 Votes"
ImranMaqsood-9535 answered KranthiPakala-MSFT commented

Hi @KranthiPakala-MSFT ,

I reached out to Microsoft support and they figured out this issue was from backend and they rectified it.

thank you for your support.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the update @ImranMaqsood-9535. Could you please share the SR# here for reference.

Thank you

0 Votes 0 ·
ImranMaqsood-9535 avatar image
0 Votes"
ImranMaqsood-9535 answered

Here is the tracking id no idea about about SR #. @KranthiPakala-MSFT
TrackingID#2104040060000273

BR

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

shahid646 avatar image
0 Votes"
shahid646 answered

@ImranMaqsood-9535 is the issue been resolved? and could you please share the solution? thanks

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.