Update database connection strings when using SQL Server high availability solutions

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 includes 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. If you need to remove the option, use Change_XD_To_ConnectionString.ps1 to run the script again and provide strings without the setting.
  • 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 using Change_XD_To_ConnectionString.ps1.

You can also update database connection strings manually; see Update strings manually. To download the PowerShell scripts, see the Procedure section.

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:

  • For .NET 3.5 SP1, patch with http://support.microsoft.com/kb/2654347.
  • For .NET 4.0, update to minimum 4.0.2. 4.0.3 is recommended: http://support.microsoft.com/kb/2600211.

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.
  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 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

To update the strings manually, run the XenApp and XenDesktop PowerShell cmdlets.

Step 1. Move SQL databases to a different SQL server and assign the correct permissions

  1. Back up the databases on the original SQL server, and restore them on the new SQL server.
  2. In SQL Management Studio > Security > Logins, add the Delivery Controller computer accounts. For example, CORP\DDC01$.
  3. When adding the SQL login, on the User Mapping page, click the three Citrix databases: Site database, Monitoring database, and Logging database.
  4. For each of the three Citrix databases, add the Delivery Controller computer account to the various database roles. The Site database has many more roles than the Logging and Monitoring databases.
Site database - ADIdentitySchema_ROLE

Site database - Analytics_ROLE                # for 7.8 and newer
Site database - AppLibrarySchema_ROLE         # for 7.8 and newer
Site database - chr_Broker
Site database - chr_Controller
Site database - ConfigLoggingSchema_ROLE
Site database - ConfigLoggingSiteSchema_ROLE
Site database - ConfigurationSchema_ROLE
Site database - DAS_ROLE
Site database - DesktopUpdateManagerSchema_ROLE
Site database - EnvTestServiceSchema_ROLE
Site database - HostingUnitServiceSchema_ROLE
Site database - Monitor_ROLE
Site database - MonitorData_ROLE
Site database - OrchestrationSchema_ROLE       # for 7.11 and newer
Site database - public
Site database - StorefrontSchema_ROLE          # for 7.8 and newer
Site database - TrustSchema_ROLE               # for 7.11 and newer
Monitoring database - Monitor_ROLE
Monitoring database - public
Logging database - ConfigLoggingSchema_ROLE
Logging database - public

Step 2. Retrieve the existing database connections (optional)

Run the following commands to see the existing database connection strings:

## Load the Citrix snap-ins
asnp Citrix.*

## Get the current Delivery Controller database connections
Get-ConfigDBConnection
Get-AcctDBConnection
Get-AnalyticsDBConnection              #  for 7.6 and newer
Get-AppLibDBConnection                 #  for 7.8 and newer
Get-OrchDBConnection                   #  for 7.11 and newer
Get-TrustDBConnection                  #  for 7.11 and newer
Get-HypDBConnection
Get-ProvDBConnection
Get-BrokerDBConnection
Get-EnvTestDBConnection
Get-SfDBConnection
Get-MonitorDBConnection
Get-MonitorDBConnection -DataStore Monitor
Get-LogDBConnection
Get-LogDBConnection -DataStore Logging
Get-AdminDBConnection

Step 3. Remove the existing Database connections

On the Delivery Controller, open PowerShell as administrator and run the following commands. This process clears the existing database connections.

## Load the Citrix snap-ins
asnp Citrix.*

## Disable configuration logging for the XD site:
Set-LogSite -State Disabled

## Clear the current Delivery Controller database connections

## Note: AdminDBConnection must be the last command

Set-ConfigDBConnection -DBConnection $null
Set-AcctDBConnection -DBConnection $null
Set-AnalyticsDBConnection -DBConnection $null             #  for 7.6 and newer
Set-AppLibDBConnection -DBConnection $null                #  for 7.8 and newer
Set-OrchDBConnection -DBConnection $null                  #  for 7.11 and newer
Set-TrustDBConnection -DBConnection $null                 #  for 7.11 and newer
Set-HypDBConnection -DBConnection $null
Set-ProvDBConnection -DBConnection $null
Set-BrokerDBConnection -DBConnection $null
Set-EnvTestDBConnection -DBConnection $null
Set-SfDBConnection -DBConnection $null
Set-MonitorDBConnection -DataStore Monitor -DBConnection $null
Set-MonitorDBConnection -DBConnection $null
Set-LogDBConnection -DataStore Logging -DBConnection $null
Set-LogDBConnection -DBConnection $null
Set-AdminDBConnection -DBConnection $null -force

If you see an error message, you must restart all the Citrix services.

Get-Service Citrix* | Stop-Service -Force
Get-Service Citrix* | Start-Service

After restarting the Citrix services, if you still see the errors, you must restart the server. Rerun the original set of commands to confirm that the existing connection is properly removed.

The following cmdlets must return an empty output:

## Load the Citrix snap-ins
asnp Citrix.*

## Get the current Delivery Controller database connections
Get-ConfigDBConnection
Get-AcctDBConnection
Get-AnalyticsDBConnection              #  for 7.6 and newer
Get-AppLibDBConnection                 #  for 7.8 and newer
Get-OrchDBConnection                   #  for 7.11 and newer
Get-TrustDBConnection                  #  for 7.11 and newer
Get-HypDBConnection
Get-ProvDBConnection
Get-BrokerDBConnection
Get-EnvTestDBConnection
Get-SfDBConnection
Get-MonitorDBConnection
Get-LogDBConnection
Get-AdminDBConnection

Step 4. Specify the new database connection strings

Adjust the variables to match your desired connection string.

  • For the Standalone SQL Server Connection String: Server=SQLServerName; Initial Catalog=DBName; Integrated Security=True
  • For the Database Mirroring Connection String: Server=PrimarySQLServerName; Initial Catalog=DBName; Integrated Security=True; Failover Partner=SecondSQLServer
  • For Always on High Availability: Server=ListenerName; Initial Catalog=XDdb; Integrated Security=True; MultiSubnetFailover=True

Run the following commands to set the new connection strings.

$ServerName = "<dbserver>"
$SiteDBName = "<SiteDbName>"
$LogDBName = "<LoggingDbName>"
$MonitorDBName = "<MonitorDbName>"
$csSite = "Server=$ServerName;Initial Catalog=$SiteDBName;Integrated Security=True"
$csLogging = "Server=$ServerName;Initial Catalog=$LogDBName;Integrated Security=True"
$csMonitoring = "Server=$ServerName;Initial Catalog=$MonitorDBName;Integrated Security=True"


Set-AdminDBConnection -DBConnection $csSite
Set-ConfigDBConnection -DBConnection $csSite
Set-AcctDBConnection -DBConnection $csSite
Set-AnalyticsDBConnection -DBConnection $csSite              # for 7.6 and newer
Set-HypDBConnection -DBConnection $csSite
Set-ProvDBConnection -DBConnection $csSite
Set-AppLibDBConnection –DBConnection $csSite                 # for 7.8 and newer
Set-OrchDBConnection –DBConnection $csSite                   # for 7.11 and newer
Set-TrustDBConnection –DBConnection $csSite                  # for 7.11 and newer
Set-BrokerDBConnection -DBConnection $csSite
Set-EnvTestDBConnection -DBConnection $csSite
Set-SfDBConnection -DBConnection $csSite
Set-LogDBConnection -DBConnection $csSite
Set-LogDBConnection -DataStore Logging -DBConnection $csLogging
Set-MonitorDBConnection -DBConnection $csSite
Set-MonitorDBConnection -DataStore Monitor -DBConnection $csMonitoring

Note:

Verify that all the preceding Set-<service>DBConnection commands have returned a result of OK. If the result is other than OK for any of these commands, it might be necessary to enable logging or tracing to determine the cause of the connection failure.

The Set-LogDBConnection -DBConnection $null and Set-MonitorDBConnection -DBConnection $null return DBUnconfigured instead of OK.

Step 5. Test the new database connection strings

  1. Run the following commands to verify connectivity to the database.

    ## Load the Citrix snap-ins
    asnp citrix.*
    
    $ServerName = "<dbserver>"
    $SiteDBName = "<SiteDbName>"
    $LogDBName = "<LoggingDbName>"
    $MonitorDBName = "<MonitorDbName>"
    $csSite = "Server=$ServerName;Initial Catalog=$SiteDBName;Integrated Security=True"
    $csLogging = "Server=$ServerName;Initial Catalog=$LogDBName;Integrated Security=True"
    $csMonitoring = "Server=$ServerName;Initial Catalog=$MonitorDBName;Integrated Security=True"
    
    Test-AcctDBConnection -DBConnection $csSite
    Test-AdminDBConnection -DBConnection $csSite
    Test-AnalyticsDBConnection -DBConnection $csSite   # for 7.6 and newer
    Test-AppLibDBConnection -DBConnection $csSite      # for 7.8 and newer
    Test-BrokerDBConnection -DBConnection $csSite
    Test-ConfigDBConnection -DBConnection $csSite
    Test-EnvTestDBConnection -DBConnection $csSite
    Test-HypDBConnection -DBConnection $csSite
    Test-LogDBConnection -DBConnection $csSite
    Test-LogDBConnection -DataStore Logging -DBConnection $csLogging
    Test-MonitorDBConnection -DBConnection $csSite
    Test-MonitorDBConnection -Datastore Monitor -DBConnection $csMonitoring
    Test-OrchDBConnection -DBConnection $csSite       # for 7.11 and newer
    Test-ProvDBConnection -DBConnection $csSite
    Test-SfDBConnection -DBConnection $csSite
    Test-TrustDBConnection -DBConnection $csSite      # for 7.11 and newer
    
  2. Restart Citrix Studio.

More information

Update database connection strings when using SQL Server high availability solutions