Advanced Concepts

Update database connection strings when using SQL Server high availability solutions

October 28, 2020

Contributed by:

B C

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 Always On 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 Always On 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 must 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.

Important:

This article was written and the scripts were tested on XenDesktop 7.6. This documentation is no longer maintained or tested with later versions of Citrix Virtual Apps and Desktops.

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

Download the zip file containing the scripts from Citrix ShareFile.
Unzip the file.
Ensure that DBConnectionStringFuncs.ps1 is in the same folder as the script you run, because the script you run uses functions in DBConnectionStringFuncs.ps1.
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

Back up the databases on the original SQL server, and restore them on the new SQL server.
In SQL Management Studio > Security > Logins, add the Delivery Controller computer accounts. For example, CORP\DDC01$.
When adding the SQL login, on the User Mapping page, click the three Citrix databases: Site database, Monitoring database, and Logging database.
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 - 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 - 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
<!--NeedCopy-->

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
<!--NeedCopy-->

Step 3. Remove the existing Database connections

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

## Note the state of the log site
Get-LogSite

## 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 -Force
Set-AcctDBConnection -DBConnection $null -Force
Set-AnalyticsDBConnection -DBConnection $null -Force             #  for 7.6 and newer
Set-AppLibDBConnection -DBConnection $null -Force                #  for 7.8 and newer
Set-OrchDBConnection -DBConnection $null -Force                  #  for 7.11 and newer
Set-TrustDBConnection -DBConnection $null -Force                 #  for 7.11 and newer
Set-HypDBConnection -DBConnection $null -Force
Set-ProvDBConnection -DBConnection $null -Force
Set-BrokerDBConnection -DBConnection $null
Set-EnvTestDBConnection -DBConnection $null -Force
Set-SfDBConnection -DBConnection $null -Force
Set-MonitorDBConnection -DataStore Monitor -DBConnection $null -Force
Set-MonitorDBConnection -DBConnection $null -Force
Set-LogDBConnection -DataStore Logging -DBConnection $null -Force
Set-LogDBConnection -DBConnection $null -Force
Set-AdminDBConnection -DBConnection $null -Force
<!--NeedCopy-->

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

Get-Service Citrix* | Stop-Service -Force
Get-Service Citrix* | Start-Service
<!--NeedCopy-->

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
<!--NeedCopy-->

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 $null -force
Set-LogDBConnection -DataStore Logging -DBConnection $csLogging
Set-MonitorDBConnection -DBConnection $csSite
Set-MonitorDBConnection -DataStore Monitor -DBConnection $null -force
Set-MonitorDBConnection -DataStore Monitor -DBConnection $csMonitoring

## If necessary, enable configuration logging for the XD site:
Set-LogSite -State Enabled
<!--NeedCopy-->

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

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
<!--NeedCopy-->

Restart Citrix Studio.

More information

How To Configure Standalone SQL Server, Database Mirroring, and Always on High Availability

The official version of this content is in English. Some of the Cloud Software Group documentation content is machine translated for your convenience only. Cloud Software Group has no control over machine-translated content, which may contain errors, inaccuracies or unsuitable language. No warranty of any kind, either expressed or implied, is made as to the accuracy, reliability, suitability, or correctness of any translations made from the English original into any other language, or that your Cloud Software Group product or service conforms to any machine translated content, and any warranty provided under the applicable end user license agreement or terms of service, or any other agreement with Cloud Software Group, that the product or service conforms with any documentation shall not apply to the extent that such documentation has been machine translated. Cloud Software Group will not be held responsible for any damage or issues that may arise from using machine-translated content.

Was this helpful

Update database connection strings when using SQL Server high availability solutions

October 28, 2020

Contributed by:

B C

October 28, 2020

Contributed by:

B C

Update database connection strings when using SQL Server high availability solutions

Requirements and considerations

Procedure

Update strings manually

More information

In this article