PowerBI connect to readonly replica of Azure sql database

[Anonymous]

Hi

We are struggling to ensure that our PowerBI reports are connected to ReadOnly Replica on our Azure sql database.

Setting "Enable SQL Server Failover Support" in Power BI Desktop does not send all queries to ReadOnly replica

We have noticed that this settings is actually setting MultiSubnetFailover = true on the M query sql.database(....,options)
We have tried to add ApplicationIntent = ReadOnly in to the same connection as well but it seems that the M sql.database option doesnt support it.

= Sql.Database ("<serveren>", "<the database>", [Query = "SELECT * FROM <the table", MultiSubnetFailover = true,ApplicationIntent = ReadOnly])

Does PowerBI support reading a Azure sql database replica?

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

The Sql.Database() function does not support "ApplicationIntent " parameter in current version( 2.88.721.0 64-bit (December 2020)).

If you could use the OLE DB connector to realize the idea.

Please refer to the blog.

Refreshing data in Power BI from SQL Server Read-only or Secondary Replica’s 

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.