Scheduled Refresh Fails Intermittantly

[Anonymous]

We have an issue with scheduled refreshes not working occasionally.  This is ocurring with two different on-premise data gateways that connect to two different SQL Servers and databases which are part of an Availability Group.  The error is basically the same each time.  The Table value changes to one of the queries we have in the dataset file.  The Pre-Login and Handshake values change as well sometimes.   

Part of Error message:

Underlying error code: -2147467259 Table: "TableName".  
Underlying error message: Microsoft SQL: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=133; handshake=14961;

We have added connection timeout of 10 minutes to each of the queries in the dataset file. but the issue persists.

This article below seems close to what we are encountering but is not in context of PowerBI. 

https://stackoverflow.com/questions/15488922/connection-to-sql-server-works-sometimes

I have scanned the web and this forum, but I have not found a fix. Has anyone encountered the same issue?   

[venal]

@Anonymous 

Is the gateway is latest version. (please try to open the Gateway and sign-in with the right credentials)

If it is the latest Gateway version, please check the dataset settings with Gateway Connection and Data Source Credentials.

If this post helps, then please consider Accept it as the solution to help the other members find it more

If this post was helpful may I ask you to mark it as solution and give it some kudos?

[ibarrau]

Hi, can you please try adding a timeout like 120 minutes for each query in edit queries. That is the power bi refresh limit from service. If that much doesn't work then you have a problem because 10 minutes is not so much for big tables. The engine could be busy at that time and work upload the data under 10 minutes.

I don't think is a power bi problem it looks like an engine problem getting the query results very slow.

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for the suggestion.   The failure error occurs within 20-30 seconds so the 10 minute timeout should suffice I would think. 

[ibarrau]

If the error is in 20-30 seconds is a connection problem. The gateway is having trouble to connect to the data source. Please be sure you have the gateway output ports opened and to test a connection from the gateway administration and from the gateway computer to garantee you can actually connect the data.

Regards,

[Anonymous]

Thanks, but have run the gateway port test.  All came back fine.  I have additional logging turned on as well.  Unfortunately it the gateway logs show the same information as the Power BI "Refresh History" modal.  There is no rhyme or reason to the failures and am thinking at this point it is something at the network layer or an AV monitor/scanner on the server blocking communication out.