Forum Discussion

pade's avatar
pade
Icon for Advocate III rankAdvocate III
9 years ago
Solved

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

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 e...
Need Help
Tutorial Requests
  • kd7vrc's avatar
    kd7vrc
    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.

pade's avatar
pade
Icon for Advocate III rankAdvocate III
9 years ago

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's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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

    • pade's avatar
      pade
      Icon for Advocate III rankAdvocate III
      9 years ago

      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's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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