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:
- Create an Azure SQL Database on the Business Critical tier
- 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(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"
- Note that the query returns “READ_ONLY” in the PowerQuery preview
- Save and refresh the dataflow
- 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.
Recent ideas
About "Download SQL database project" and fix the downloaded project
1. The sqlproj must be fixed, it is downloaded without the xml markup line <?xml version="1.0" encoding="utf-8"> 2. The sdk must be updated: Actual: <Sdk Name="Microsoft.Build.Sql" Version="0.1...Jarrinf4 hours agoFrequent VisitorNew48Views3likes2CommentsEventstream MQTT broker source: V3.1.1 support
The Eventstream MQTT broker source supports connections with public MQTT brokers that offer MQTT V5 support. Please offer support for V3.1.1 too, because this is still a valid and widely used ver...svelde6 hours agoSuper UserCompleted1.4KViews6likes4CommentsGet data from Apache Kafka topics via private networking
Current prerequisites limit to public Kafka instances which is not feasible for most enterprise customers. When will this limitation be lifted so that it can be used in private network setups, for ...Jugi6 hours agoMicrosoft EmployeePlanned3.5KViews2likes3CommentsSet default x axis scrolling position
Enable selection of the default scroll position for the x-axis on a chart. For example, on a line chart with dates as the x-axis, when not all of the data can display in the view at once, the scroll...Lisa_Krieg14 hours agoNew MemberNeeds Votes7.8KViews1154likes104Comments