High Availability Multi Subnet Failover issues

[support]

Issue:

If running Passwordstate in High Availability mode, where you have two webservers both communicating to 2 x SQL servers replicating data in real time, some customers have had issues testing an automatic failover in SQL to the second database.  This only happens when the second database server is located on a different subnet to where the Primary Passwordstate website is hosted.

 

Potential Fixes:

Three are a few things you can try to fix this issue:

 

Fix 1:

In the connection string sections of your web.config files, add in the following code:

 

MultiSubnetFailover=True

This tells the Passwordstate website that it can communicate to a database on a different subnet

 

Connect Timeout=30

This setting can be expanded to 60, or 90 and this is how long the website stays alive for whilst it’s trying to establish a connection to the new database.

 

Persist Security Info=True

This means the SQL password is stored in cache when failing over - Use this at own risk and please see Microsoft Explanation for this below.  Perhaps only do this for testing purposes:

 

Microsoft Explanation of Persist Security Info:

The default value for Persist Security Info is false; we recommend using this default in all connection strings. Setting Persist Security Info to true or yes allows security-sensitive information, including the user ID and password, to be obtained from a connection after it has been opened. When Persist Security Info is set to false or no, security information is discarded after it is used to open the connection, ensuring that an untrusted source does not have access to security-sensitive information.

 

Example of web.config file:

 

<connectionStrings>

<add name="PasswordstateConnectionString" connectionString="Data Source=xxxxxxxxx; Initial Catalog=passwordstate; User ID=passwordstate_user; Password=xxxxxxxxx; Persist Security Info=True; MultiSubnetFailover=True; Connect Timeout=30" providerName="System.Data.SqlClient” />

</connectionStrings>

 

 

Fix 2:

Instead of MultiSubnetFailover=True set this to Yes like this example: MultiSubnetFailover=Yes

 

We're not 100% sure why, but possibly different versions of SQL accept the different values and we've had some customers report that one works, but the other doesn't.

 

Fix 3:

You'll be using an Active Directory Listener as part of your SQL fail over.  A Listener is just a virtual computer object in AD with functioning DNS like any physical server. Typically you would set the Netbios name of Listener object in your connection string as the Data Source.  Instead of setting the Netbios name for this Listener, set the IP Address of the Listener in the Connection String.

 

 

The only other thing we can recommend is logging a support call with Microsoft if none of the above suggestions help.  There's no code inside Passwordstate that can be changed to get this to work, and the issue lies between IIS and the SQL Technology.  Microsoft should be able to assist with this.

 

Regards,

Click Studios Support

[support]

Some additional information from one of our customers about this, may help some users (Thanks Patrick R)

 

Hey,

 

I would like to update you on this, I still had issues using multi subnet AG listener.

 

I found the following so the cluster only publishes the active (single) IP for the Listener (see below), downside is that there is some downtime when the db switches because of the DNS TTL (Default 20 minutes), but that can be lowered as well.

 

 

#Fill the variables below:

$ClusterName =                   "ClusterName"             # Get-Cluster

$ClusterResourceName =    "ClusterResourceName"            # Get-clusterResource | ? { $_.resourcetype -eq 'SQL Server Availability Group' }

$AGListener =                    "AGListener"              # Get-clusterResource | ? { $_.resourcetype -eq 'Network Name' }

 

 

Get-ClusterResource $ClusterResourceName -Cluster $ClusterName | set-clusterparameter RegisterAllProvidersIP 0 -Cluster $ClusterName

Get-ClusterResource $ClusterResourceName -Cluster $ClusterName | set-clusterparameter HostRecordTTL 10 -Cluster $ClusterName

 

# You'll get a warning, all changes will take effect until ClusterResourceName is taken offline and then online again.

 

Stop-clusterresource $ClusterResourceName -Cluster $ClusterName        # Take Offline

Start-clusterresource $ClusterResourceName -Cluster $ClusterName       # Right Back Online

 

Start-clusterresource $AGListener -Cluster $ClusterName                 # This step is important. The Listener is offline, must bring the Listener Back online

 

 

This sets the HostRecordTTL to 10 seconds, so downtime is max 10 seconds, this might put extra strain on the DNS so depending on the environment this setting could be set higher.

 

 

 

Hope this helps if anyone else attempts this with multisubnet availability group listeners