Re-publishing Report does not update Application Intent

[mcho]

Using PowerBI Server Version 1.8.7485.35104

Using Power BI Desktop Version : 2.51.4885.2501 64-bit (October 2017)

I am not part of my businesses IT Team so I have limited support and access. 

I've recently been given a read only always on SQL server that reduces the impact of my queries on the main node that our application runs off. I've been able to move all SSRS reports to the correct Read Only Node.

However when I republish a PowerBI "Dashboard" with the Read Only node settings. It will republish the dashboard with all the data refreshed but the Data Source will show without the ApplicationIntent=ReadOnly.

There are multiple Dashboards that already exist and I have been unable to alter any of their data sources. Are data sources shared between different dashboards?? Are the other reports preventing me from changing the data sources? 

[SQLShortbeard]

Guy in a Cube from Microsoft published a video on this: https://guyinacube.com/2017/09/06/power-bi-sql-server-alwayson-availability-groups/

[v-deddai1-msft · Accepted solution]

Hi @mcho ,

You can use the following sql query to determine if the multi-subtnet failover works.

SELECT DATABASEPROPERTYEX(DB_NAME(), ‘Updateability’) AS Access;

SELECT

       req.session_id,

       req.status,

       req.command,

       req.cpu_time,

       req.total_elapsed_time,

       s.host_name,

       s.program_name,

       s.login_name

FROM sys.dm_exec_requests req

INNER JOIN sys.dm_exec_sessions s ON  req.session_id = s.session_id

WHERE s.program_name = ‘Mashup Engine’;

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

Best Regards,

Dedmon Dai

[d_gosbell]

So I do not believe that is a reliable way of determining if Application Intent is being applied or not. As you have already noticed for PBIX files the "Connection String" is not stored in a traditional way in Report Server. It's only really the server and database that are reflected there.

My guess would be that the design time settings are possibly still being applied and you should probably check with your DBAs first.  They should be able to run a trace or check a DMV while a refresh is running to determine if this is working or not. 

[d_gosbell]

It looks like this technique should work https://sqlarcher.com/2019/04/09/read-only-replicas-for-power-bi-and-azure-analysis-services/ 

[mcho]

Thanks for reply.

I am pretty much doing roughly the same thing.

I have the multi-subtnet failover set but it doesnt replicate to the server after publishing.

Infact the refreshing from the PowerBI Desktop application works fine and i'm able to submit changes.

Also changing between the active and read only node also works.  The connection string will reflect the server address change but NOT append the Application=ReadOnly setting to the connection string.

[d_gosbell]

---

@mcho wrote:

The connection string will reflect the server address change but NOT append the Application=ReadOnly setting to the connection string.

---

How can you tell? Are you getting told this by your DBA's?

The actual connection string will be built dynamically at run time, I don't believe you will see the "Application Intent" setting anywhere in the client side. Unfortunately I don't have an environment where I can check this for you. But if you are sure this is not working it's probably best to raise an official support ticket with Microsoft since the data providers in Power BI do not provide any way to edit the connection string directly so I don't believe there is any work around if this is not working correctly.

[mcho]

So I have enough access to see the data sources on the PowerBI Report Server side.

Based on my SSRS reports this is what i'm expecting to see.

But no matter what i upload i get the following.

As you noted you cannot directly edit the connection string in PowerBI "reports" hence it's grayed out. I can get the server address to change as already mentioned but it won't reflect the application intent. I do notice that the string is arranged differently. i.e. server;catalogue vs data source = server; initial catalogue = RSSQLDB_NSW; extra settings.

That said i'll defiantely have the call lodged i was just hoping someone may have come across this issue.