Allow dataflows to refresh data from a Azure SQL Database read-only replica (using [MultiSubnetFailover=True]) without using an on-premises data gateway

[paul_knapper]

Allow dataflows to refresh data from a Azure SQL Database read-only replica (using [MultiSubnetFailover=True]) without using an on-premises data gateway. Currently this is only possible when using an on-premises data gateway (which is undesirable for Azure SQL DB).

Steps to reproduce:

1. Create an Azure SQL Database on the Business Critical tier
2. Create a dataflow with the M query code as below, replacing [DatabaseServer] and [DatabaseName] with the details of your Business Critical Azure SQL database. Ensure the connection is not using the on-premises data gateway.

let

 Source = Sql.Database("[DatabaseServer]", "[DatabaseName]", [CommandTimeout = #duration(0, 2, 0, 0), Query = "SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS Updateability", MultiSubnetFailover = true]),

 #"Transform columns" = Table.TransformColumnTypes(Source, {{"Updateability", type text}}),

 #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Updateability", null}})

in

 #"Replace errors"

1. Note that the query returns “READ_ONLY” in the PowerQuery preview
2. Save and refresh the dataflow
3. Connect to the dataflow in PBI Desktop or Excel. Note that the dataflow now returns “READ_WRITE”, indicating that it has refreshed using the primary instance and not the read only replica as would be expected
4. Edit the dataflow and change the data source so that it connects through an on-premises data gateway
5. Save and refresh the dataflow
6. Connect to the dataflow in PBI Desktop or Excel. Note that the query now returns “READ_ONLY”, indicating that it is using the read only replica as it should do.

I have been advised by Microsoft this is by design behaviour for dataflows as they can write data as well as read it. The gateway operations use different settings and thus don't reflect the same behaviour as the Power BI Service. Hence, when you use gateway it gives you read-only replicas. I have been asked to raise an idea on the ideas forum where the idea may be taken into consideration for the future if it receives sufficient votes.