Reading from SQL Managed Instance Read Only Replica via 'On Premise' SQL Data Gateway

[BrianDrought]

Hi Folks,

Trying to set my Data Flows to pull data from the Read Only replica of my Azure SQL MI cluster (Business Critical SKU). For regular SQL I simply throw 'ApplicationIntent=ReadOnly' on the connection string and it goes to the ReadOnly node. 

In the DataFlow SQL connection there's the option to “Enable SQL Server Failover support” as noted here:

https://www.altisconsulting.com/uk/insights/how-to-connect-power-bi-to-read-only-replica-of-azure-sq...

I've done that, used the SQL queries to check the processes on the RO and RW and annoyingly it's hitting the RW node. I think it's because I'm using an 'On-premises data gateway' to link to the Managed Instance (it's actually an Azure VM). Someone in the comments mentions the same.

So, tried using an ODBC driver with the connection string:

Driver={SQL Server Native Client};Server=mysqlmi.6xxxxxxx34.database.windows.net;Database=MyData;ApplicationIntent=ReadOnly;

Works fine, but again, it hits the RW node. Again, my gut feeling is the On Premise SQL Gateway is stripping the ReadOnly part out.

Anyone managed to get Fabric/PBI to work with the RO node of an MI cluster ? 

Screenshot of the verification that it's hitting the RW node:

[v-yueyunzh-msft]

Hi ,  @BrianDrought 

Accoridng to your description and the shared link is trying to connect to the Azure Sql server Read-Only Replica.

You have selected the “Enable SQL Server Failover support”  options in the dataflow sql server connector and add the connection string "ApplicationIntent=ReadOnly". And you are using the on-premise data gateway to connect your Azure sql server then you found that it hit the Read-Write node instead of the Read-Only Node .

As searched , there is the document about this configure the  connect to the Azure Sql server Read-Only Replica, you can refer to :
Refreshing data in Power BI from SQL Server Read-o... - Microsoft Fabric Community

And as searched in the internal PG reply , the PG teams said that :
Dataflows always write to a blob store during refresh so at that point there is no design today to maintain the ApplicationIntent = ReadOnly. So we also connect to Sql without application intent and thus returning READ_WRITE in the Dataflow as also seen in the fiddler.

 So, you can try to check whether we hit the read-only node when we view the data in Dataflow. And when using the gateway to refresh, hit the read-write node?

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[DennesTorres]

Hi,

This link contains details about the support of this connectionstring property. This would be the first thing I would check: https://techcommunity.microsoft.com/t5/sql-server-support-blog/connect-to-sql-server-using-applicati...

"SQL Native Access Client Supports Application Intent property

Support for read-only routing of SQL connections is via the SQL Native Access Client provider (SNAC) version 11. You can specify the Application Intent connection property when connecting to SQL Server via SQLClient, SNAC ODBC or SNAC OLEDB.

The SNAC provider must be installed where your client application runs."

This other link: https://dba.stackexchange.com/questions/162347/alwayson-secondary-readable-cant-connect-with-applica...

Reminds how you need to change the AG configuration to allow the read only connections, otherwise the connectionstring setting would not work.

Kind Regards,

Dennes