SQL Query new advanced setting: "enable sql server failover support"

[pade]

In the January Power BI Blog, the advance SQL query stiing "enable sql server failover support" was announced.

But I can't find any more information from Microsoft about this capability. I know it enables using the failover support and/or Always On in SQL server.

Maybe it's obvious for those that have better knowledge in SQL, but I'm looking for information about SQL requirements like version requirements, configuration requirements etc. I'm also looking for information if this feature is also enabled in the Power BI Service, and if works with the Power BI Gateway or if it's limitied to Azure SQL.

Probably I'm looking for more information as well, currently not obvious to me 🙂

[kd7vrc · Accepted solution]

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.

[pade]

I tested with the Power BI Service, an OnPrem SQL and the Enterprise Gateway, but that didn't work.

If this is denpending an the GW configuration, or if this is an comming feature, I don't know. It could also be due to that my DB was not configured correctly, and that PBI Service is more verbose than my Power BI Desktop. So still some questions and verry little information from Microsoft in this

[v-huizhn-msft]

Hi @pade,

For your requirement, you can review and vote the feature here. Your feedback is valuable for us to improve our products and increase the level of service provided.

Thanks,
Angelia

[mohassan99]

Is this only relevant to DirectQuery. I have no idea what failover support even is.

[pade]

Thanks for the information @v-huizhn-msft

I understod your answer as: failover support is currently not supported in Power BI GateWay.

If I would go for an Azure Analysis Service that requires the Analysis Service Gateway, do we have the same limitations?

That idea you refered to might possible solve this, but it's actually not the same thing. It will probably take more effort to implment suppport for Power BI Gateway automatically failover between two gateways, than it would take to implement support for connecting to a SQL server failover cluster using only one PBI gateway. I have now loged a new idea about only the failover support via the PBI Gateway

Any more information about OnPrem SQL service requirements and configuration settings needed to be done on local SQL host and/or DB?

[v-huizhn-msft]

Hi @pade,

Thanks for your understanding, while I personally post the configuration settings of SQL database for failover support to SQL server forum, which will post specific solution.

Thanks,
Angelia

[GilbertQ]

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?