How to use PowerShell to Change XenDesktop SQL Connection Strings

Background

Delivery Controller is a collection of services, each of which has its own SQL connection string. To change the SQL connection strings, you use PowerShell to change each service. Some points to keep in mind:

  • Most of the Delivery Controller services connect to the same database (Site database), so you can set the same connection string for almost all of them
  • Monitoring has two connection strings: one for the Site database, and another for the separate Monitoring database
  • Logging has two connection strings: one for the Site database, and another for the separate Logging database

Note: Embedded inside the Site database are the SIDs of the Delivery Controller machines. The Delivery Controller SID is added to the Site database whenever you use Citrix Studio to create a site, or join a Delivery Controller to a site. If the Delivery Controller SID is not already in the database, then this article will not help you. In other words, if you build a brand-new Delivery Controller virtual machine and point it to the SQL databases, it will not work.

  • If you delete the Delivery Controller’s Active Directory computer account and re-create it, then the Delivery Controller will have a new SID, and the SQL database connections will not work
  • Adding the new Delivery Controller SID to SQL Login permissions will not fix the missing SID in the Site database

The general process to change the SQL connection strings once a database has been moved is to set the current database connection strings to $null. This must be done in a specific order. In particular, the AdminDBConnection (Delegated Administration service) must be nulled last. Then set the services to the new database connection strings.

This process has been broken down into multiple steps. Click the links below to directly access each step:

  1. Getting Started
  2. Move SQL databases to a different SQL Server
  3. Retrieve the existing Database connections
  4. Remove the existing Database connections
  5. Specify the new Database connection strings
  6. Test the new Database connection strings

Instructions

Getting Started

  • Back up the Citrix Site, Monitoring and Logging databases
  • Take a snapshot of the Delivery Controller virtual machine(s)
  • It is expected that the Citrix databases are unavailable until the procedure is complete. Therefore, connections to the Site may be temporarily unavailable. It is best to complete all the steps in the following procedure within the same change window
  • If you are moving the SQL databases to another SQL server, see the section ‘Move SQL databases to different SQL Server’ for SQL login and Database permission requirements
  • All Management Consoles should be closed. Citrix strongly recommends that no administrative changes be attempted until the Databases are back online and verified

Points to Note

  • Some of the DBConnection cmdlets were only added in later versions of XenApp/XenDesktop and might generate “not recognized” errors on older versions.
  • PvsVmDBConnection is not used in XenDesktop 7.x. It is only applicable to XenDesktop 5.x, and therefore it has been removed from all the PowerShell commands below


Move SQL Databases to a different SQL Server

Here are general instructions for moving the database and assigning the correct permissions:

1. Backup the databases on the original SQL server, and restore them on the new SQL server. See Microsoft’s documentation for details.

2. In SQL Management Studio > Security > Logins, add the Delivery Controller computer accounts (e.g. CORPDDC01$)

3. When adding the SQL Login, on the User Mapping page, select 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 as listed below. The Site database has many more roles than the Logging and Monitoring databases.

Site database – ADIdentitySchema_ROLE

  • Site database – Analytics_ROLE (7.8 and newer)
  • Site database – AppLibrarySchema_ROLE (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 (7.11 and newer)
  • Site database – public
  • Site database – StorefrontSchema_ROLE (7.8 and newer)
  • Site database – TrustSchema_ROLE (7.11 and newer)
  • Monitoring database – Monitor_ROLE
  • Monitoring database – public
  • Logging database – ConfigLoggingSchema_ROLE
  • Logging database – public



Retrieve the existing Database connections

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 # 7.6 and newer

Get-AppLibDBConnection # 7.8 and newer

Get-OrchDBConnection # 7.11 and newer

Get-TrustDBConnection # 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

Remove the existing Database connections

  • At 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 # 7.6 and newer

Set-AppLibDBConnection -DBConnection $null # 7.8 and newer

Set-OrchDBConnection -DBConnection $null # 7.11 and newer

Set-TrustDBConnection -DBConnection $null # 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 the following error, you must restart all the Citrix services.

User-added image
Get-Service Citrix* | Stop-Service -Force

Get-Service Citrix* | Start-Service


User-added image
After restarting all 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.
  • These cmdlets should not return anything:
## Load the Citrix snap-ins

asnp Citrix.*

## ## Get the current Delivery Controller database connections

Get-ConfigDBConnection

Get-AcctDBConnection

Get-AnalyticsDBConnection # 7.6 and newer

Get-AppLibDBConnection # 7.8 and newer

Get-OrchDBConnection # 7.11 and newer

Get-TrustDBConnection # 7.11 and newer

Get-HypDBConnection

Get-ProvDBConnection

Get-BrokerDBConnection

Get-EnvTestDBConnection

Get-SfDBConnection

Get-MonitorDBConnection

Get-LogDBConnection

Get-AdminDBConnection

Specify the new Database connection strings

  • Run the following commands to set the new connection strings.
  • Adjust the variables to match your desired connection string. For example, if you wish to add “;MultiSubnetFailover=True” to the connection strings, then set the $csSite variable to “Server=$ServerName;Initial Catalog=$SiteDBName;Integrated Security=True;MultiSubnetFailover=True”.
  • Repeat this for the $csLogging and $csMonitoring variables
## Replace <dbserver> with the SQL server name, and instance if present, e.g “ServerNameSQLInstanceName”. If no SQL Instance name is mentioned, this commandlet will try to connect to the default SQL instance.

## Replace <dbname> with the name of your restored Database

## Note: AdminDBConnection should be first

$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 # 7.6 and newer

Set-HypDBConnection -DBConnection $csSite

Set-ProvDBConnection -DBConnection $csSite

Set-AppLibDBConnection –DBConnection $csSite # 7.8 and newer

Set-OrchDBConnection –DBConnection $csSite # 7.11 and newer

Set-TrustDBConnection –DBConnection $csSite # 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

Set-LogDBConnection -DBConnection $null

Set-LogDBConnection -DBConnection $csSite

Set-LogDBConnection -DataStore Logging -DBConnection $csLogging

Set-MonitorDBConnection -DBConnection $csSite

Set-MonitorDBConnection -DataStore Monitor -DBConnection $null

Set-MonitorDBConnection -DBConnection $null

Set-MonitorDBConnection -DBConnection $csSite

Set-MonitorDBConnection -DataStore Monitor -DBConnection $csMonitoring

Note: It is important to 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.

Note: The Set-LogDBConnection -DBConnection $null and Set-MonitorDBConnection -DBConnection $null will returnDBUnconfigured instead of OK.

Test the new Database connection strings

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

asnp citrix.*

## Copy these variables from the previous step

## If you haven’t closed your PowerShell window, then the variables might still be defined. In that case, just run the Test commands

$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 # 7.6 and newer

Test-AppLibDBConnection -DBConnection $csSite # 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 # 7.11 and newer

Test-ProvDBConnection -DBConnection $csSite

Test-SfDBConnection -DBConnection $csSite

Test-TrustDBConnection -DBConnection $csSite # 7.11 and newer

User-added image

​2. Restart Citrix Studio

Related:

  • No Related Posts

Leave a Reply