PowerBI Refresh Error

[Anonymous]

PowerBI App + Azure SQL Database(Serverless, Gen5, 2Vcores) with autopause after 1 hour.

PowerBI fetches the data from Azure SQL Database using a SQL view with under 15M records everyday. 

This refresh takes about 10 minutes to refresh in PowerBI desktop. 

When I refresh it manually on PowerBi web, It takes under 5 min.

But When I am trying to schedule the dataset refresh, it throws an error. 

-------------------------------------

Last refresh failed: Tue Jan 12 2021 13:44:23 GMT+0530 (India Standard Time)
There was an error when processing the data in the dataset.Hide details

Processing error:	Microsoft SQL: Server provided routing information, but timeout already expired.
Cluster URI:	WABI-UK-SOUTH-B-PRIMARY-redirect.analysis.windows.net
Activity ID:	55091bfd-ce22-4ac6-b38b-7c199de0d392
Request ID:	a3480078-1a6f-4afd-adc1-3f71cb734436
Time:	2021-01-12 08:14:23Z ---------------------------------------------------------------------- Can this be because of the serverless setup of the Azure Sql db with an autopause feature after 1 hour.  Kindly help!!!

[GilbertQ]

Hi @Anonymous 

Yes this is quite certainly the case.

If the SQL Server is paused when the initial connection is tried it will fail.

Have you tried rather using the DTU version of Azure SQL Server?

It is a lot cheaper and can be run 24/7

Another option is to have some other process which will wake up or try and connect to the SQL Serverless before Power BI refreshes.

[Anonymous]

Thanks Gilbert, Will look into it!