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 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.ps1to 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.
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.
- 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.)
- 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
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-->
Verify that all the preceding
Set-<service>DBConnectioncommands 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.
Set-LogDBConnection -DBConnection $nulland
Set-MonitorDBConnection -DBConnection $nullreturn 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.