Share via

Multi-subnet SQL Server Clusters + SharePoint 2013 + SPDatabase.MultiSubnetFailover

  • Article

Small but significant update; earlier I described a problem where SharePoint can get its’ knickers in a twist when it comes to consuming a SQL Server endpoint that exists in multiple clusters.

Well the problem is real enough; anyone with this configuration that has a failover will probably find SharePoint mysteriously unable to connect to SQL Server with the default configuration set. In my previous post I showed a way of handling this from the SQL end; in short, to use just one A-record for the clustered-name object SharePoint uses.

Well it turns out that there’s a more elegant solution that had escaped my attention – a new property that was added to SPDatabase just before SharePoint went to General Availability called “MultiSubnetFailover”, which is set to “false” by default, meaning sequential IP-address connections if there is more than one to connect to.

Anyway, in short setting this property to “true” across all clustered databases means SharePoint can now connect to multiple IP addresses at the same time for when there’s multiple DNS A-records for a single name; the SQL client will connect to every endpoint at once & just use the first one to respond, thus avoiding connection timeouts. Thank you blog commenters for pointing this out, amongst other people.

 

Setting MultiSubnetFailover=true for SharePoint Databases

Pretty simple really; you’ll want to do it for every database that’s on the multi-subnet cluster. In my case it’s every database, so:

Get-SPDatabase | foreach { $_.MultiSubnetFailover = $true; $_.Update(); }

clip_image002

If you take a look at the connection-string now used by the configuration DB for example (in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\15.0\Secure\ConfigDB\dsn), we see “MultiSubnetFailover=True” added to the config DB connection-string.

Now we can set/have RegisterAllProvidersIP as “1” against the cluster and SharePoint will work just fine.

image

It’s worth pointing out too that because we don’t wait for a DNS update, the failover from SharePoint’s point of view is much quicker – the DNS doesn't change, just the other endpoint responds first instead of the previous (now inactive) endpoint.

 

Caveats

There was a bug in the .Net framework 4 that misread the timeout value when “MultiSubnetFailover=True” setting was used in the connection-string, meaning the client (SharePoint in this example) would timeout anyway. This has been fixed but it might be easier to disable multiple A-records instead if patching the runtime isn’t an option – in production environments etc.

Cheers,

Sam Betts

Comments

  • Anonymous
    May 05, 2016
    Hello Sam - great post. And of course a follow up question. I used the powershell as outlined above and when I checked the registery I saw the change on one front end server but not the second. Also not on APP server (I am not running sites on App servers). Any insights?Cheers,Mo