Data Flow and Failover Support

[MichaelDoig]

Hi all,

Earlier this year our DBA's enabled failover support in our Azure SQL Server and thus allowed us to take advantage of Power BI's Failover Support through the Enable SQL Server failover support feature on Datasets. 

We've enjoyed this change and the fact we're no longer contending with the Data Warehouse ETL by hitting our own Read Only Instance. 

However, we have recently started exploring Data Flows and have configured a few early Data Flows by "migrating" M Code out of some key Datasets into Data Flows. Everything has been working swimmingly so far, the teams are enjoying the capability of Data Flows.

Until the DBA's asked us why we're hitting the Read Write instance again? We discovered the reason was the new Data Flows we have setup. So my question is this? Does Data Flows support SQL Server Failover the same way Datasets do? Or are we missing a configuration somewhere? 

The M Code contains the requisite MultiSubNetFailover=true condition, sample snippet below. 

let
  Source = Sql.Database("sql-server-here.database.windows.net", "database-here", [Query="SELECT * FROM DIM_Calendar", CommandTimeout=#duration(0, 0, 15, 0), MultiSubnetFailover=true])
in
  Source

We're keen to make use of Data Flows, but not at the expense of not being able to hit the Read Only instance. Any recommendations welcome.

Thank you,
Michael

[MichaelDoig]

Thank you.

I have engaged with Microsoft Support who did understand the question. There is an issue that can be worked around if you make use of an on-premise gateway with failover support.

The Microsoft Product team has been made aware of the issue via our contact with Microsoft. If anyone faces this issue, let me know and I'll guide you through what we did.

Regards,

Michael 

[PaulKn]

I am experimenting with an Azure SQL Database running on the Business Critical compute tier and trying to force queries to run against the read-only replica. I have been using the test query detailed here Connecting Power BI to Azure SQL Read Scale Out REPLICA – Kloudspro to verify if Power BI is connecting to the read only replica.

It seems to work ok with data sets and data marts, but (as per with the original post from 2.5 years ago)  not with dataflows unless I connect through our on-premises data gateway (which seems a bit of an unnecessary thing to have to do with an Azure SQL database). 

This is an old post so I won't hold out hope for a reply, but were you given any explanation from Microsoft as to why you have to use a gateway @MichaelDoig? 

[Anonymous]

Hello Michael, can I please know what you did to get the read-only replica for dataflows?

[v-lionel-msft]

Hi @MichaelDoig ,

I don't particularly understand, please refer to the link below.

'Power BI high availability, failover, and disaster recovery FAQ 

Best regards,
Lionel Chen

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