Why can't my PHP 5.4 webserver speak to my multi-subnet failover cluster using SQL Always On?

Question

We've recently implemented a SQL Server 2012 Always On failover cluster. The go-live is in 2 weeks time and some concerning issues have come up. Previously we were dealing with servers in the same subnet, but we've since moved the servers to multiple subnets. Since doing that we encountered the multiple subnet failover issue; http://technet.microsoft.com/en-us/library/ff878716.aspx.

"In a multi-subnet configuration, both the online and offline IP addresses of the network name will be registered at the DNS server. The client application then retrieves all registered IP addresses from the DNS server and attempts to connect to the addresses either in order or in parallel. This means that client recovery time in multi-subnet failovers no longer depend on DNS update latencies.

By default, the client tries the IP addresses in order. When the client uses the new optional MultiSubnetFailover=True parameter in its connection string, it will instead try the IP addresses simultaneously and connects to the first server that responds. This can help minimize the client recovery latency when failovers occur."

The symptoms of the issue are: The PHP 5.4 server will intermittently fail to connect. It may work for 20 minutes, then fail for 25 minutes, then work for 40 minutes.

We've tried introducing the 'MultiSubnetFailover' parameter as so:

$dbhandle = sqlsrv_connect(
    $myServer,
    array("UID"=>$myUser, "PWD"=>$myPass, "Database"=>$myDB, 'ReturnDatesAsStrings'=> true,
    'MultiSubnetFailover'=> true)
   )

And updating the webserver with Microsoft SQL drivers that explicitly support multi-subnet failover; http://blogs.msdn.com/b/sqlphp/archive/2012/03/07/microsoft-drivers-3-0-for-php-for-sql-server-released-to-web.aspx

The subnets are set up correctly and I can connect normally through other services such as SQL Server Management Studio when I supply the 'MultiSubnetFailover=Yes' parameter, in fact the difference is night & day.

Any help appreciated, this one is too close to the release for comfort.

EDIT: There is actually a second connection string I missed, but once configuring this with the multi-subnet failover parameter the error still occurs;

   $pdoHandle = new PDO("sqlsrv:server={$myServer};database={$myDB};multiSubnetFailover=yes", $myUser, $myPass);

Answer 1

This is an awkward problem without much documentation but we did come to a solution. The SQLSRV_Connect parameter should read

'MultiSubnetFailover'=> "Yes"

rather than 'true'. Because true just returns a boolean value, whereas it wants a string. For a connection string as used by the PDO interface the follwoing syntax seems to work for us:

"MultiSubnetFailover=True" 

But even when you use the correct syntax the support isn't great. If this solution doesn't work then you need to increase the timeout on the connection because the SQL Server driver will try each DNS record in turn. We use "LoginTimeout=75" (seconds) for a set-up with 2 subnets and 110 should do for a set-up with 3 subnets.

This solution is, however, still crap. It works acceptably for a front-end application that only needs to connect once and uses the same connection from then on. It doesn't work so well for web servers that tend to create a new connection for each request. It could make loading each web page take as long as 30, 70 or 100 seconds, depending on how the DNS records happen to be configured at that time.