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

[GilbertQ]

I have seen a lot of questions in the Power BI Community asking how to connect to a SQL Server Read-Only or Secondary replica, and whilst this is not currently available in the Sql.Database M connector, it is available in the OLE DB connector.

NOTE: This could potentially be used for a whole host of other OLE DB providers

NOTE II: This only applies to Imported datasets at the time of writing this. In my example i am going to be using the OLE DB Connector for SQL Server Native Client 11.0

I will explain below how to create the connection, as well as how to configure it in the Power BI On-Premise Data Gateway.

Connecting to the Data in Power BI using the OLE DB Connector

• The first thing I do, is to connect to the data, by clicking on Get Data and then finding my OLE DB Connector
  • 
  • I then click on Connect
• The great thing about Power BI, is that after I click on Connect, there is a button to Build the connection string, so I click on Build
  • 
• This brings up the Data Link Properties Window
  • I click on SQL Server Native Client 11.0
  • 
  • I click on Next
• I then put in my Server Name, select Use Windows NT Integrated Security and select my database
  • NOTE: Depending on how your Gateway authenticates to SQL Server will depend on what you put in here.
  • 
  • NOTE: I always put in the FQDN name for the SQL Server instance.
  • I then click on Test Connector to make sure that I can connect.
    • 
• I then click on All, where it says Application Intent, it will default to READWRITE
  • NOTE: This is the IMPORTANT part where I want to make sure that it will use the Read-Only replica or secondary replica
  • I clicked on Application Intent and then clicked on Edit Value
  • 
  • I then clicked on Ok to complete the changes
• And then I clicked on Ok again.
• I can now see my connection string built for me
  • 
  • NOTE: If I scroll across I can see and confirm that I have set the Application Intent to ReadOnly
  • 
  • NOTE: Make a copy of the connection string above. This will be used when creating the data source in the On-Premise Data Gateway.
• There is an option under Advanced Options, if you want to put in a SQL Statement, that is totally up to you, or you can click Ok.
• I was then prompted for the OLE DB Provider credentials.
  • As far as I understand this is so that the credentials are stored separately from the connection string and not stored in clear text.
  • I selected Windows and then "Use my current credentials"
  • 
  • I then clicked Connect.
• I was then prompted with the Navigator which allowed me to select the table that I wanted
  • 
  • I then selected the table that I wanted and clicked Edit
  • NOTE: I always prefer to Edit the queries in the Power Query Editor before loading them into the data model.
• I then added it to my data model
• And uploaded it to the Power BI Service.

Creating the Data Source in the Gateway in the Power BI Service

NOTE: You will have to ensure that you have got the permissions to add data sources to the Gateway in the Power BI Service.

• I went into the Power BI Service and clicked on Manage Gateways.
  • 
• I found my associated Gateway, then clicked on the Ellipses and selected Add Data Source
  • 
• I then configured it with following:
  • Data Source Name
    • 
  • Data Source Type
    • 
  • Connection String (NOTE: You can get this from your PBIX file if you did not copy it from the earlier step
    • 
  • Authentication Method, I chose Windows. Yours possibly will be what you connected to in Power BI Desktop.
    • 
  • I then put in the username and password
    • 
  • Finally, under Advanced settings I selected Privacy Level to None
    • 
  • I then clicked Add
• I then got the confirmation that it was successfully created
  • 
• I then went into my App Workspace where I had uploaded my PBIX, then into the settings and configured it to use the Gateway.
  • 
  • I then clicked Apply.
• I tested the refresh and it successfully refreshed
  • 
• My final test was to sit with the DBA and ensure that when my data was being refreshed that it was reading from the read-only/secondary replica which it was.
  • I always suggest double checking to make sure that it is working as expected, and to not assume that it is working.

Conclusion

In this blog post I have showed how you can now connect and import data from a read-only or secondary SQL Server replica.

This has really been great for some of my customers who want to ensure that the data refresh queries will not impact the data warehouses or production systems. Please feel free to leave any comments or suggestions, I do enjoy getting them.

Additional Note

I needed to ensure that I had the latest versions of the OLE DB Providers installed for this to work as expected. I would suggest making sure if you are going to use the native OLE DB provider that you install the latest version from here: Microsoft OLE DB Driver for SQL Server

[shekarkola_pom]

Hi, 

Great posst, it works with OLE DB connection.

But, it's addressing only "Off loading reporting work load part". doesn't help the requirement of Real-time reporting (where we use Direct query method)

It would be great if there is way to add connection string option for SQL Datasource or at Advance Editor windows from Query editor. 

[GilbertQ]

Hi @shekarkola_pom

Thanks for the message, currently for DirectQuery you can only use the Sql.Database 

Hopefully this will change in the future or you could look at find or create an idea at https://ideas.powerbi.com

[Anonymous]

Hi,

Thanks for the excellent post!

I have used this method for connecting to the Azure SQL Server ReadOnly Replica.  It works well.

In this way, i believe i have to use the On-Premises data gateway.

Is there any other way we can connect to the Azure SQL Server Readonly replica without gateway service?

Regards,

Gnanaprakash

[GilbertQ]

Hi @Anonymous 

Thanks for the kind words

As far as I know currently it has to go via the Gateway.

[Anonymous]

Thanks for the Reply!

Do we have any other way apart from OLE DB connector to connect to the Azure SQL replica - readonly?

[GilbertQ]

As far as I currently know, not at the moment.

I think there might be an idea at https://ideas.powerbi.com that you could vote for.

[Anonymous]

Thanks @GilbertQ 

[Nolock]

Hi @GilbertQ,

it is possible when you click on "Enable SQL Server Failover support" in the SQL Server connector - more about it on https://sqlarcher.com/2019/04/09/read-only-replicas-for-power-bi-and-azure-analysis-services or https://guyinacube.com/2017/09/06/power-bi-sql-server-alwayson-availability-groups/

let
    Source = Sql.Database("mySQLServer", "MyDB", [Query="SELECT @@SERVERNAME", MultiSubnetFailover=true])
in
    Source

[Abevann]

Guys,

I have a connection withan SQL database which works perfectly on Power BI desktop; once I tried to acces it from the power bi on browser, it is no possible to connect to ("There is no gateway to access the origin of the xxxxxx data base" and install the Data ling gateway app from download menu. Seems ok, since the tool said that the gateway was up. Then on data base configuration, the defined gateway was on the list of available ones, but with the label "error on cofiguration"; then add a data origin configuration and seems to be fine but it asks for a user name and a password, which were no asked when configured the data origin on Power BI Desktop. My local database doesn't require user name nor password.

Any idea to solve the issue? Thanks a bunch! Regards,

Abelardo Hernández

[GilbertQ]

Hi there

I would suggest creating a Username and password for the connection to be made.

[Anonymous]

It appears to me that this driver does not currently support Query Folding.  Can anyone confirm this?

I have alot of reports that were under consideration for moving to this driver to support the readonly intent but without Query Folding there is quite a bit of risk of pushing more data and and processing out our gateways.

Thanks in advance!