Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get Fabric certified for FREE! Don't miss your chance! Learn more

Add support for Azure SQL Database read-only replica for gen2 dataflows

Allow gen 2 dataflows to refresh data from a Azure SQL Database read-only replica (using [MultiSubnetFailover=True]).

 

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. 

let

 Source = Sql.Database("[DatabaseServer]""[DatabaseName]", [CommandTimeout = #duration(0200), 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. 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.

The above behaviour occurs whether an on-premises data gateway is used or not.


Please add support for gen2 dataflows using a read-only replica irrespective of whether the gateway is used or not.

Status: New