Test-AdminDBConnection fails with MultiSubnetFailover=True option enabled for SQL Database move to HA/Availability Group

[Jamie Rojas]

Hey guys, we are looking at trying to migrate our XenApp 7.15 LTSR SQL database from the current 2013 Mirrored setup to a new 2016 HA/Database Availability Group.

Our DBA setup a new Availablity group with a valid cluster name that's setup between 2 SQL servers across our 2 Datacenters.

 

However when I am trying to test the connection from one of our DDCs I am getting an error with the String, and am worried the move won't work again (we attempted once using the PowerShell scripts available from CTX140319, but the string it created looked very different to everything else out there/manual steps, and didnt work.. So we are going to try again the manual way based upon CTX216504)

 

What we have/I am trying;

$ServerName="AUSQAGP02.FQDN.com"
$SiteDBName = "CitrixAPAC"
$LogDBName = "CitrixAPAC_Logging"
$MonitorDBName = "CitrixAPAC_Monitoring"
$cs="Server=$ServerName;Initial Catalog=$SiteDBName;Integrated Security=True;MultiSubnetFailover=True"
$csLogging= "Server=$ServerName;Initial Catalog=$LogDBName;Integrated Security=True;MultiSubnetFailover=True"
$csMonitoring = "Server=$ServerName;Initial Catalog=$MonitorDBName;Integrated Security=True;MultiSubnetFailover=True"

 

PS H:\> Test-AdminDBConnection -DBConnection "$cs" | fl

ServiceStatus : Unknown
ExtraInfo     : {[Database.Status, Normal], [Database.RecoveryModel, Full], [Database.LastBackupDate, 06/27/2019
                00:46:42], [Database.IsReadCommittedSnapshotOn, True]...}

 

Whereas if i change the string and remove the MultiSubnetFailover=True option it works?

PS H:\> $cs="Server=$ServerName;Initial Catalog=$SiteDBName;Integrated Security=True"
PS H:\> Test-AdminDBConnection -DBConnection "$cs" | fl
ServiceStatus : OK
ExtraInfo     : {[Database.Status, Normal], [Database.RecoveryModel, Full], [Database.LastBackupDate, 06/27/2019
                00:46:42], [Database.IsReadCommittedSnapshotOn, True]...}

Is there something wrong with the Test-AdminDBConnection string when using MultiSubnetFailover option? Or do we have something wrong in the SQL config? As the last attempt to move the DB it looked like it never even connected to the new SQL Server... Any help would be much appreciated! Thanks!

 

[Jamie Rojas]

So after finally getting SQL access to have a look at our current setup, and following the setup guides Carl points to for AlwaysOn Database Availability Group setup I can confirm it was a SQL configuration error on our side 

There was no specific Availability Group Listener setup for the new DB setup. As a result, the Test-AdminDBConnection was able to only query the active/Primary DB server, but adding the MultiSubnetFailover=True option made it an invalid SQL connection string :/ Setting up the availability listener properly and running all the connectivity tests now works! Thanks guys, trying to explain the issue I think helped me think about it more as well :)