2

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);
Jansky
  • 1,455
  • 1
  • 17
  • 33
  • Is there anything in your logs for this? Not familiar with MS environment, unfortunately - what support options do you have with them? – halfer Dec 04 '13 at 11:23
  • In terms of logging we have the connection error which seems to be a time-out, "Unable to complete login process due to delay in opening server connection". We also set up a script to send HTTP requests and record when the server fails. From this I can see it succeeded this morning from 9:52 to 10:09, then failed until 10:28, succeeded until 10:37, failed until 10:46.. I doubt there is a useful pattern, though. We don't have any support contracts with Microsoft. – Jansky Dec 04 '13 at 11:50
  • Alright. Is it worth trying a different version of 5.4? You don't say which you are using, so I assume latest - maybe bump down a couple of minor versions? Also, if you get stuck, see if the same behaviours are exhibited on latest 5.5? – halfer Dec 04 '13 at 12:06
  • It's not some networking config that's been missed such as a firewall rule or a missing route or something like that is it? – steoleary Dec 04 '13 at 14:27
  • It's definitely an issue with the server because when I point it at a different database server (in the same network) it works. I'll experiment with different versions of PHP but I think the drivers are the suspect – Jansky Dec 04 '13 at 14:47
  • By way of an update I've tried PHP 5.5 and found no difference. There are no officially supported sqlsrv drivers for PHP 5.5 and the unofficial compilations failed in the same way 5.4 did. – Jansky Dec 05 '13 at 11:05
  • Did you find a solution? If so can you post your solution to assist others? – James Jenkins Apr 18 '14 at 12:09

1 Answers1

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.

Jansky
  • 1,455
  • 1
  • 17
  • 33