Forum Discussion
Advocate IIISQL Query new advanced setting: "enable sql server failover support"
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.
Advocate IThis 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.