Product Documentation

Update database connection strings when using SQL Server high availability solutions

Apr 10, 2017

Citrix offers several PowerShell scripts that update XenApp and XenDesktop database connection strings when you are using SQL Server high availability database solutions such as AlwaysOn and mirroring.

The scripts, which use the XenApp and XenDesktop PowerShell API, are:

  • DBConnectionStringFuncs.ps1: The core script that does the actual work. This script contains common functions that the other scripts use.
  • Change_XD_Failover_Partner_v1.ps1: Updates (adds, changes, or removes) the failover partner. This script prompts for the failover partner location (FQDN) for each database. (Providing a blank failover partner removes the failover partner. You can also use the ClearPartner option to remove a partner.) Do not set the failover partner to the same location as the principal database server.
  • Change_XD_To_ConnectionString.ps1: Uses the provided connection strings to update the connection strings to the databases. This script ensures that certain Citrix services are up and running, and then updates those services in the correct order on all Controllers in the site. Enclose connection string information for each database in quotes.
  • Change_XD_To_MultiSubnetFailover.ps1: Toggles the addition and removal of MultiSubnetFailover=true. If you use AlwaysOn Availability Groups, Microsoft recommends that the connection string include MultiSubnetFailover=true. This option speeds up recovery when a high availability event occurs, and is recommended for both single and multi-subnet environments. Run this script once to add the option. Run the script again to remove it.
  • Change_XD_To_Null.ps1: Resets all the connection strings on the localhost because something has gone wrong. By resetting the connection strings to null, this script places the Controller into an “initial” state. If you run Studio after running this script, you'll be asked if you want to create a site or join an existing site. This is useful if something has gone wrong and a reset is needed. After the reset, you can try again to set the connection strings.

You can also update database connection strings manually; see Update strings manually.

Requirements and considerations

  • You must be a full site administrator to run the scripts.
  • Run the scripts in the PowerShell window of one Controller. PowerShell v3 is required.
  • The XenApp and XenDesktop core components must be installed and the Site up and running.
  • Before running the scripts, disable mandatory configuration logging.
  • The MultiSubnetFailover option is supported with .NET 4.5 and later. However, the MMC that Studio uses on Windows 7 or 2008 R2 machines contains an earlier .NET version, so you might see the error "Keyword not supported: 'multisubnetfailover'" when you select Configuration in the Studio navigation pane. In such cases, patch or update as follows:

Then, use the Change_XD_To_MultiSubnetFailover.ps1 script to update the database connection strings with this option.

Procedure

  1. Download the zip file containing the scripts from Citrix ShareFile: https://citrix.sharefile.com/d/sc237fc7d5ed4f0fb.
  2. Unzip the file.
  3. Ensure that DBConnectionStringFuncs.ps1 is in the same folder as the script you run, because the script you run uses functions in DBConnectionStringFuncs.ps1.
  4. Run the script on a Controller.

If you just want to set your site to multi-subnet failover, you only need to run the Change_XD_To_MultiSubnetFailover.ps1 script. (Remember: ensure that the DBConnectionStringFuncs.ps1 script is in the same folder.)

Tips:

  • When the connection strings are being updated, it is normal to see a message indicating that "Server=SQLxxx\CITRIX\..." is being changed to "Data Source=SQLxxx\CITRIX\...". The terms Server and Data Source are synonymous.
  • If you want to manipulate the connection strings, look at how the Change_XD_ scripts use the functions in DBConnectionStringFuncs.ps1.

Update strings manually

If you prefer to manually run the XenApp and XenDesktop PowerShell cmdlets to update connection strings, rather than use the scripts, follow this guidance.

Step 1. You need three connection strings.

  • Site database, from: $csSite = Get-BrokerDBConnection
  • Monitoring database, from: $csMonitor = Get-MonitorDBConnection –DataStore Monitor
  • Configuration logging database, from: $csLog – Get –LogDBConnection – DtaStore Logging

Step 2. To each connection string, add the failover partner clause.

$csSite = $csSite + ";Failover Partner=FQDN.Of.Site.Mirror"
$csMonitor = $csMonitor + ";Failover Partner=FQDN.Of.Monitor.Mirror"
$csLog = $csLog + ";Failover Partner=FQDN.Of.Logging.Mirror"

Step 3. Disconnect the Controllers from the databases, which is a three-part process.

Part 1: On one Controller, clear the connections to the databases.

Set-MonitorDBConnection -DataStore Monitor -DBConnection $null
-AdminAddress $Controller
Set-LogDBConnection -Datastore Logging -DBConnection $null
-AdminAddress $Controller

Part 2: On all other Controllers in the site, reset their configuration, which reads the cleared setting from the previous action.

Reset-MonitorDataStore –DataStore Monitor –AdminAddress $Controller
Reset-LogDataStore –DataStore Logging –AdminAddress $Controller

Part 3: On every Controller, disconnect the Citrix services from the site database. The following lists show services in XenApp and XenDesktop 7.14; your XenApp and XenDesktop version might have a different set of services. To see the services for your version, run:

get-command set-*DBConnection

Important: Disconnect all of the Citrix services in your XenApp and XenDesktop version. Run the Set-LogDBConnection and Set-AdminDBConnection cmdlets last.

Set-AcctDBConnection -DBConnection $null -AdminAddress $Controller
Set-AnalyticsDBConnection – DBConnection $null –AdminAddress $Controller
Set-AppLibDBConnection –DB Connection $null –AdminAddress $Controller
Set-BrokerDBConnection -DBConnection $null -AdminAddress $Controller
Set-ConfigDBConnection -DBConnection $null -AdminAddress $Controller
Set-EnvTestDBConnection -DBConnection $null -AdminAddress $Controller
Set-HypDBConnection -DBConnection $null -AdminAddress $Controller
Set-MonitorDBConnection -DBConnection $null -AdminAddress $Controller
Set-OrchDBConnection –DBConnection $null –AdminAddress $Controller
Set-ProvDBConnection -DBConnection $null -AdminAddress $Controller
Set-SfDBConnection -DBConnection $null -AdminAddress $Controller
Set-TrustDBConnection –DBConnection $null –AdminAddress $Controller
Set-LogDBConnection -DBConnection $null -AdminAddress $Controller
Set-AdminDBConnection –Force -DBConnection $null -AdminAddress $Controller

At this point, the site has no database connectivity.

Step 4. Reconnect the Controllers and the databases, which is a three-part process.

Part 1: On every Controller, reconnect all of the Citrix services to the site database.

Important: Reconnect all of the Citrix services you disconnected in the previous step. Run the Set-LogDBConnection and Set-AdminDBConnection cmdlets first.

Part 2: On one Controller, set the database connection.

Set-MonitorDBConnection -DataStore Monitor -DBConnection $csMonitor
-AdminAddress $Controller
Set-LogDBConnection -DataStore Logging -DBConnection $csLog
-AdminAddress $Controller

Part 3: On all other Controllers in the site, reset their configuration, which reads the new connection string from the previous action.

Reset-MonitorDataStore –DataStore Monitor –AdminAddress $Controller
Reset-LogDataStore –DataStore Logging –AdminAddress $Controller

This article has been modified from blog posts written by Chris Gilbert.