Leah Kaplan on 12 Aug 2015 13:43:56
Can we please get an option to connect to a secondary alwayson replica via the listener? Something equivalent to ApplicationIntent=ReadOnly would be great.
- Comments (18)
RE: connect to alwayson secondary replica
Why is this not supported? The Op's point is totally legit. In which scenario would Power BI have non-readonly activity? Please fix thanks
RE: connect to alwayson secondary replica
It works with OLE DB Connection, but not in "Direct Query" mode.
https://community.powerbi.com/t5/Community-Blog/Refreshing-data-in-Power-BI-from-SQL-Server-Read-only-or/bc-p/606819#M1465
RE: connect to alwayson secondary replica
This needs to be fixed asap. SSRS & SSIS are both able automatically use the listener to pull from the Secondary.
RE: connect to alwayson secondary replica
especially now that SQL Server Availability Groups support round-robin with multiple secondary read-only queries this is extremely important for large datasets!
RE: connect to alwayson secondary replica
Enable SQL Server Failover support is NOT the same as ReadOnly support. ReadOnly support will allow the automatic selection of the readonly server in an always-on Availability Group setting. This feature is huge and I'm shocked that it only has 51 votes thus far. It also adds support to one of SQL Server's highly touted features. If Microsoft won't even support their own features, how do they expect other vendors to support their feature? It sets a very poor example.
RE: connect to alwayson secondary replica
This does work, but the behaviour is strange. Ideally you would want to connect to the AG Listener and allow the Read-Only routing of the cluster to route you to the available readable secondary. This is not the case (at least in my case). In order to connect to my readable secondary, I have to put the name/ip address of my readable secondary in the server name box and then tick the "enable SQL Server support box". It's great because it connects and I can read. It's not so great if my readable secondary becomes the primary, then I have my reporting and my application workloads both running on it together. Anyone else with similar experience or maybe know what is going wrong?
RE: connect to alwayson secondary replica
Checking the box "enable sql server failover support" seemed to do the trick for us.
RE: connect to alwayson secondary replica
Can someone confirm this is indeed now supported in Power BI Desktop app using the setting "enable sql server failover support" in the SQL Server Database connection advanced options?
RE: connect to alwayson secondary replica
'+1 for me, too. This is a frustrating issue
RE: connect to alwayson secondary replica
This is now implemented as "enable sql server failover support". But I found no information about it what so ever