To explain how to build a simple script to change the connection strings for FMA services on a site after changing from a single SQL server to SQL mirroring or SQL HA.

---

Instructions

This should be performed only on one controller at the time.

We will start assuming that the database have already been migrated and all the logins and roles are also created on the newly added SQL partner or new SQL server.

Summary

1. Stop configuration logging.
2. Stop monitor data collection.
3. Backup the registry.
4. Confirm the current connection strings.
5. Nullify all the connection strings.
6. Set the new connection strings.
7. Test the services.
8. Confirm services DataStores.
9. Start configuration logging.
10. Start monitor data collection.

Launch a PowerShell prompt and add Citrix modules (asnp Citrix*).

Stop Configuration logging:

Set-LogSite –State Disabled

Stop Monitor data collection:

Set-MonitorConfiguration –DataCollectionEnabled $False

Backup the registry:

Since connection strings are stored on the registry, it’s important to have a backup if something goes wrong so you can go back to the original state.
Make sure you backup “HKLM\Software\Citrix\” and “HKLM\Software\Wow6432Node\Citrix\”
Open regedit, browse to “HKLM\Software\Citrix\” or “HKLM\Software\Wow6432Node\Citrix\”, then right click and export.

Confirm the current connection strings:

Depending on the version of XenApp & XenDesktop 7.x we will have more or less services, then connection strings. That being said, the first thing that we need to know is how many database strings I have to update. In PowerShell run the following command to confirm the current connection strings:Get-Command Get*DBConnection
For 7.16 It returns the following:

Highlight the commands and then hit enter to copy them to clipboard.
Paste on the PowerShell window and you should receive something like:

Tip: If the command fails, check that all services are up and running.
 

           5. Nullify all the connection strings:

In here we are going to start building a little script that will help us nullifying the connection strings and then to set the new ones.
Run the following command to obtain the ‘Set’ commands for the db strings:
 
 Get-Command Set*DBConnection
For 7.16 It returns the following:

Open PowerShell ISE as an admin and show the script pane (Ctrl + R). Write the following:
asnp Citrix*
 
$dbString = $null
               
Set-AnalyticsDBConnection -DBConnection $dbString
Set-AppLibDBConnection -DBConnection $dbString
Set-BrokerDBConnection -DBConnection $dbString
Set-ConfigDBConnection -DBConnection $dbString
Set-EnvTestDBConnection -DBConnection $dbString
Set-HypDBConnection -DBConnection $dbString
Set-LogDBConnection -DBConnection $dbString
Set-MonitorDBConnection -DBConnection $dbString
Set-OrchDBConnection -DBConnection $dbString
Set-ProvDBConnection -DBConnection $dbString
Set-SfDBConnection -DBConnection $dbString
Set-TrustDBConnection -DBConnection $dbString
 
Set-AcctDBConnection -DBConnection $dbString #Important
Set-AdminDBConnection -DBConnection $dbString #Important
 
Before running the script please make sure that Citrix AD Identity Service (Acct) and Citrix Delegated Admin Service (admin) are the final ones, if they are changed before the other ones you can run into issues like “There was an error communicating with the Citrix Delegated Admin Service” which will require to apply the registry backup obtained before and run the script again in the right way.
 
Once you run the script you can confirm with step 4) if all the connections are nulled.
Tip: If you face issues trying to nullify the connection strings, try using –Force on each command.
       
6. Set the new connection strings:

First thing is to build our new connection string using the SQL Connection String Properties.
Here are 3 standard examples:
-Single database server, instance name = XDSite and Windows authentication required:
“Server=FQDN.com\XDSite; Database=XDDatabase; Integrated Security=True”
-DataBase Mirroring configured, Windows authentication required, default instance:
“Server=FQDN.com; Initial Catalog=XDDatabase; Trusted_Connection=True; Failover partner=MirrorFQDN.com”
-SQL HA or SQL Failover Cluster instance, default instance, Windows Integrated authentication:
“Server=FQDN.com; Database=XDDatabase; Integrated Security=Yes; MultiSubnet Failover=True”
 
Once you built your new connection string, go back to the script built to nullify the connections and change $dbString from $Null to your new “SQL connection String”.
 
asnp Citrix*
 
$dbString = “SQL Connection String”
               
Set-AnalyticsDBConnection -DBConnection $dbString
Set-AppLibDBConnection -DBConnection $dbString
Set-BrokerDBConnection -DBConnection $dbString
Set-ConfigDBConnection -DBConnection $dbString
Set-EnvTestDBConnection -DBConnection $dbString
Set-HypDBConnection -DBConnection $dbString
Set-LogDBConnection -DBConnection $dbString
Set-MonitorDBConnection -DBConnection $dbString
Set-OrchDBConnection -DBConnection $dbString
Set-ProvDBConnection -DBConnection $dbString
Set-SfDBConnection -DBConnection $dbString
Set-TrustDBConnection -DBConnection $dbString
 
Set-AcctDBConnection -DBConnection $dbString #Important
Set-AdminDBConnection -DBConnection $dbString #Important
 
Run the script again and now all of your connection strings should be pointing to the right direction.Go ahead and confirm it going again through step 4).
 
      7. Test the services:

If everything went right, now we should be able to test the services by running the following command on PowerShell:
 Get-Command Get*serviceStatus
On 7.16 it returns the following services:

Highlight the commands and then hit enter to copy them to clipboard.
Paste on the PowerShell window and every service status should be OK.

           8.Confirm DataStores:

Run the following commands to check if your DataStores have been automatically configured:
Get-MonitorDataStore
Get-LogDataStore

If they don’t return both connection strings you might set the DataStore manually, but before doing that, try restarting both services or if possible the controller.
If still not there, try with the following commands:
Set-MonitorDBConnection –DataStore Monitor –DBConnection “SQL connection string pointing to monitor db”
Set-LogDBConnection –DataStore Logging –DBConnection “SQL connection string pointing to Logging DB”

       9.Start Configuration Logging:
Once confirmed that you successfully configured the new connection strings, you must enable configuration logging again using the following command:
Set-LogSite –State Enabled

        10.Start Monitor Data Collection:

To enable monitor service to start collecting data again run the following command:
Set-MonitorConfiguration –DataCollectionEnabled $true

To explain how to build a simple script to change the connection strings for FMA services on a site after changing from a single SQL server to SQL mirroring or SQL HA.