Forum Discussion
SQL Query new advanced setting: "enable sql server failover support"
- 8 years ago
This is a question I've also had for a long time. Guy in a Cube answered it in this video about Always On Availability Groups. When "Enable SQL Server Failover support" is checked, it adds "MultiSubnetFailoverSupport = True; ApplicationIntent = ReadOnly" to the connection string. Some SQL Server documentation describes the MultiSubnetFailoverSupport option to mean when this option is enabled, if the SQL Server Availability Group fails over from one node to the other, the connection will follow the primary node instead of failing. ApplicationIntent = ReadOnly is important. If the Availability Group is configured with it's default settings, it will query the secondary node, leaving the primary node free to process the presumably higher priority load of requests to read and write data that only the primary node can handle. Applications will read and write faster on primary without your report running there, and your report will read faster with no read/writes in your way on the secondary node.
Hi pade
From this blog post at Power BI, it appears that it is for any SQL Server that has got FailOver enabled.
https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-january-feature-summary/#SQLFailover
In terms of what else you are looking for, I would think that there might be someone else on the Forum who has used this, or at the very least I hope tested it?