Citrix Database Migration Process

Last Updated on December 27, 2020

I was recently tasked with migrating XenDesktop and PVS databases from Microsoft SQL server 2008 Enterprise edition to MS SQL 2016 standard edition. Since I had to document the entire process for operations team, I thought I should share it with everyone. Citrix Database Migration process starts with doing some preliminary checks, fixing discovered issues upfront and then the remaining part of the exercise goes very smoothly.

Here I am going to demonstrate how to migrate XenDesktop 7.15 LTSR CU6 (7.15.6000) and PVS CU 6 (7.15.27) databases, but practically the Citrix Database Migration process remains same for all 7.x versions and editions. In my case, the Database server is stand alone and relies on hypervisor HA; however, I will include instructions for other High Availability features like Failover Cluster, Mirroring and Availability groups.

Preparation

  1. Run a Site Health check and if there are some issues, address them first. Open Studio, go to <Site Name>/Common Tasks/Site Configuration and click on Test Site.
  1. A successful Site configuration test will show no warnings and failed tests.
Successful Site Test
  1. An unsuccessful Site configuration test will show the warnings or failed tests. Click on Show report and fix the issue(s).
  1. On the Delivery Controller, execute Get-BrokerSite and confirm that LHC is enabled. If not, execute Set-BrokerSite -LocalHostCacheEnabled $true -ConnectionLeasingEnabled $false. Please understand that this command disables the Connection Leasing feature. You should not enable both Local Host Cache and connection leasing.
Citrix Database Migration Process
  1. Plan for a downtime and communicate this to the end-users. Although you have LHC for the rescue but there is no harm in getting an approved downtime. LHC takes over when the Site database is inaccessible for 90 seconds.
    • If you are unsure whether Local Host Cache will work or not, manually force an outage to trigger the LHC feature and validate. Get yourself familiar with How LHC works.
    • To force an outage go to HKLM\Software\Citrix\DesktopServer\LHC and change the value of OutageModeForced to 1. Set the value to 0 to take the controller out of outage mode.
    • See Utilizing Local Host Cache for Non-disruptive Database Upgrades.
  1. On PVS servers, confirm that Offline Database Support is enabled. Offline database support creates a snapshot of the database and initializes it at server startup. After that, Stream process continuously updates the local copy of the database. Therefore, when database goes offline, provisioning Servers and target devices remain operational. Open Provisioning Services Console; right click on Farm and select Properties. Switch to Options tab and ensure “Enable offline database support” is checked.
    • If not checked, do so. You will be prompted to restart Streaming service on all members of the PVS farm.
  1. Ask Database team to assign Sysadmin role a service account on the new Database server. This is not required for Citrix Database migration process but if the service account has same permission in original SQL server then there is no point in loosing this privilege on the new Database Server.
  1. Confirm that the new MS SQL server version is compatible with XenDesktop/Citrix Virtual Apps and Desktop version.
  1. Validate that port 1433 or if you are using a custom port is open from Delivery Controllers and PVS servers towards Database server.

Citrix Database Migration Process

Following steps are to be performed just before the Citrix Database migration procedure begins.

  1. Take the snapshot of Delivery Controllers and PVS servers. This will help you revert to previous state if anything goes wrong while establishing connections with new Databases.
  2. Enable maintenance mode on all VDAs (Citrix Virtual Apps/XenApp Servers, Static and pooled desktops). No worries if that is not feasible. Once primary delivery controller is disconnected from databases, all VDAs will re-register themselves and new connections will be handled with/by newly elected broker. Just ensure that Controllers were initially deployed using LHC design considerations and recommendations.
  3. If there are active user sessions then inform them to log off gracefully. Use Studio to send messages. No need to worry if that is not possible. Existing connections will not have any impact, however, new connections and reconnections might have a very little to no impact. For example, a reconnection request may result into a new connection even though the reconnection to an existing session was possible. These events only occur during the time VDAs are reregistering with newly elected Broker and the Broker is starting to learn about the current sessions.
  1. Back up following databases on the original SQL server.
    • Site Stores the running Site configuration, plus the current session state and connection information.
    • Configuration Logging – Stores information about Site configuration changes and administrative activities.
    • Monitoring – Stores data used by Director, such as session and connection information.
    • PVS – Stores farm data.
  1. Open SSMS and login to the Database server. If you are using server instance then use the name of the SQL server and if you are using named instance, use servername\instancename.
  1. Right click on Database, select Tasks, and click on Backup.
Citrix Database Migration Process
  1. Choose Full in Backup type, select Disk in Back up to: as Destination and Click on Add.
Citrix Database Migration Process
  1. Provide a File name and click on OK twice.
  1. Copy backed up databases over to new Database Server. In my case, it is a standalone server. For you it can be the Primary instance of the Availability group, principal node of mirroring setup or primary node of the failover cluster.
  1. Restore Databases. Open SSMS and login to the Database server. Right click on Databases and select Restore Database.
  1. Select Device and click on three dots (ellipses). Select File in Backup media type. Click on Add, browse the database and click on OK three times.
Citrix Database Migration Process
  1. Create new login for Delivery Controller computer accounts on Primary/Principal node as well as Secondary/Mirror/Replica node(s). Expand Security, right click on Login and select New Login.
  1. In the Login name, type <domain name>\<computer name>$. Repeat it for other Delivery Controllers.
Citrix Database Migration Process
  1. Go to User Mapping, select the database and assign below roles to Delivery Controller computer accounts:
Citrix Site Database – ADIdentitySchema_ROLE
Citrix Site Database – Analytics_ROLE                      # for 7.8 and newer
Citrix Site Database – AppLibrarySchema_ROLE   # for 7.8 and newer
Citrix Site Database – chr_Broker
Citrix Site Database – chr_Controller
Citrix Site Database – ConfigLoggingSiteSchema_ROLE
Citrix Site Database – ConfigurationSchema_ROLE
Citrix Site Database – DAS_ROLE
Citrix Site Database – DesktopUpdateManagerSchema_ROLE
Citrix Site Database – EnvTestServiceSchema_ROLE
Citrix Site Database – HostingUnitServiceSchema_ROLE
Citrix Site Database – Monitor_ROLE
Citrix Site Database – OrchestrationSchema_ROLE  # for 7.11 and newer
Citrix Site Database – public
Citrix Site Database – StorefrontSchema_ROLE        # for 7.8 and newer
Citrix Site Database – TrustSchema_ROLE                 # for 7.11 and newer
Citrix Site Monitoring Database – Monitor_ROLE
Citrix Site Monitoring Database – public
Citrix Site Configuration Logging Database – ConfigLoggingSchema_ROLE
Citrix Site Configuration Logging Database – public
Citrix Database Migration Process
Citrix Database Migration Process
Citrix Database Migration Process
  1. Create new login for PVS server computer accounts on Primary/Principal node as well as Secondary/Mirror/Replica node(s).
  2. Go to User Mapping, select the database and assign below roles to PVS Server computer accounts:
PVS Site Database – Db_datareader
PVS Site Database – DB_datawriter
PVS Site Database – Public
Citrix Database Migration Process
  1. Verify the state of databases.
    • For Mirroring, ensure that the state of the databases shows up as (Principal, Synchronized) and (Mirror, Synchronized/Restoring…).
    • For Availability groups, ensure that synchronization state of Availability replicas under Availability group shows up as Synchronized.

Migrate Citrix Virtual Apps and Desktop/XenDesktop Databases

In this part of Citrix Database Migration process, we will migrate Citrix XenDesktop or Citrix Virtual Apps and Desktops Databases.

  1. Confirm that Citrix Studio is not opened on any Delivery Controller. If Studio is published, ensure that there is no active session of that.
  2. Login to Primary Citrix Delivery Controller, open PowerShell as an Administrator and execute following commands to see the existing database connection strings.
    • In my case the output is Server=SQL2k601V; Initial Catalog=CTX_CA_XD715Site; Integrated Security=True. For you it may be same or Server=PrimarySQLServerName; Initial Catalog=DBName; Integrated Security=True; Failover Partner=SecondSQLServer or Server=ListenerName; Initial Catalog=XDdb; Integrated Security=True; MultiSubnetFailover=True.
asnp citrix*
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
  1. Now run following commands to clear the existing database connections. Ensure that AdminDBConnection is the last command.
Get-LogSite
Set-LogSite -State Disabled
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
Citrix Database Migration Process
  1. If there is an error, restart all Citrix services. If the error persist after restarting services, restart the server.
Get-Service Citrix* | Stop-Service -Force
Get-Service Citrix* | Start-Service
  1. Re-run commands from step 2. The output should be empty this time. You can safely ignore the error messages or do not execute those two commands.
  1. Now run following commands to specify new database connection strings.
$ServerName = “SQL2k602V”
$SiteDBName = “CTX_CA_XD715Site”
$LogDBName = “CTX_CA_XD715Logging”
$MonitorDBName = “CTX_CA_XD715Monitoring”
$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”
  1. For Availability groups, the variables will be as follows:
$ServerName = “ListenerName”
$SiteDBName = “SiteDBName”
$LogDBName = “LoggingDBName”
$MonitorDBName = “MonitoringDBName”
$csSite = “Server=$ServerName; Initial Catalog=$SiteDBName; Integrated Security=True; MultiSubnetFailover=True”
$csLogging = “Server=$ServerName; Initial Catalog=$LogDBName; Integrated Security=True; MultiSubnetFailover=True”
$csMonitoring = “Server=$ServerName; Initial Catalog=$MonitorDBName; Integrated Security=True; MultiSubnetFailover=True”
  1. For Mirroring, the variables will be as follows:
$PServerName = “PrincipalServerName”
$MServerName = “MirrorServerName”
$SiteDBName = “SiteDBName”
$LogDBName = “LoggingDBName”
$MonitorDBName = “MonitoringDBName”
$csSite = “Server=$PServerName; Initial Catalog=$SiteDBName; Integrated Security=True; Failover Partner=$MServerName”
$csLogging = “Server=$PServerName; Initial Catalog=$LogDBName; Integrated Security=True; Failover Partner=$MServerName”
$csMonitoring = “Server=$PServerName; Initial Catalog=$MonitorDBName; Integrated Security=True; Failover Partner=$MServerName”
  1. Execute following commands to set new database connection strings.
Set-AdminDBConnection -DBConnection $csSite
Set-ConfigDBConnection -DBConnection $csSite
Set-AcctDBConnection -DBConnection $csSite
Set-AnalyticsDBConnection -DBConnection $csSite
Set-HypDBConnection -DBConnection $csSite
Set-ProvDBConnection -DBConnection $csSite
Set-AppLibDBConnection –DBConnection $csSite
Set-OrchDBConnection –DBConnection $csSite
Set-TrustDBConnection –DBConnection $csSite
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
Set-LogSite -State Enabled
Citrix Database Migration Process
  1. Run following commands to test the new database connection strings.
Test-AcctDBConnection -DBConnection $csSite
Test-AdminDBConnection -DBConnection $csSite
Test-AnalyticsDBConnection -DBConnection $csSite
Test-AppLibDBConnection -DBConnection $csSite
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
Test-ProvDBConnection -DBConnection $csSite
Test-SfDBConnection -DBConnection $csSite
Test-TrustDBConnection -DBConnection $csSite
Citrix Database Migration Process
  1. Look for following events to verify connection has been successfully established with Database and LHC has been refreshed.
Citrix Database Migration Process
  1. If you execute Get-BrokerController, secondary Delivery Controller’s state will show as Failed.
Citrix Database Migration Process
  1. Login to other Delivery Controllers and repeat all the steps.

Migrate Citrix Provisioning Services (PVS aka Citrix Provisioning) Database

In this part of Citrix Database Migration process, we will migrate Citrix Provisioning Services Database.

  1. Shutdown all Target devices.
  2. Login to first Provisioning services server and open Provisioning Services Configuration Wizard. Click on Next.
  1. Select “The service that runs on anther computer” under “DHCP Services“. Click on Next.
  1. Select “Provisioning services PXE service on this computer” under “PXE Service“. Click on Next.
Citrix Database Migration Process
  1. Select “Join existing farm” under “Farm Configuration“. Click on Next.
Citrix Database Migration Process
  1. Enter the name of the new Database server. If you are using names instance, mention it in Instance name. If you are using a custom port then mention it in Optional TCP port. Leave both fields blank otherwise. Click on Next.
    • For Always-on Availability group, check Enable MultiSubnetFailover for SQL Server Always On.
    • For Mirroring, check Specify database mirror failover, and provide the mirror server instance, named instance and custom port.
Citrix Database Migration Process
  1. Select the Farm in Farm name under Existing Farm. Click on Next.
  1. Existing site should be already checked. Select Site in Site name under Site. Click on Next.
  1. Existing Store should be already checked. Select a Store in Store name under Store. Click on Next.
  1. Here you can change the License Server if required. Otherwise, click on Next under License Server.
  1. Leave “Network service account” checked under “User Account” and click on Next.
    • It is advised for PVS Stream and Soap services to run under a service account. That account needs to be local Administrator on all PVS servers. Since I am using Active Directory based Activation instead of KMS, I have chosen Network Service account.
  1. Leave Automatic computer password update checked with Days set to 7 under Active Directory Computer Account Password. Click on Next.
  1. Leave Streaming and Management network cards set to current NIC, first communication port set to 6890 and console port set to 54321. Click on Next.
  1. Leave everything as it is under TFTP Option and Bootstrap Location. Click on Next.
  1. Leave everything as it is under “Stream servers Boot List“. Click on Next.
  1. Click on Next on Soap SSL Configuration.
  1. Click on Next on Problem Report Configuration. If you plan to submit problem reports to Citrix, provide Citrix username and password.
  1. Make sure Automatically Start Services is checked. Click on Finish.
Citrix Database Migration Process
  1. At Configuring Service phase, PVS will establish connection with new Database server. Click on Done.
Citrix Database Migration Process
  1. Login to other PVS servers and repeat steps 2-19.

Finalizing the Citrix Database Migration Process

  1. Change the Compatibility level of all Databases to 130 (SQL Server 2016). This step is only required if you are migrating databases from an older version of SQL server to the newer one. Choose the compatibility level appropriately.
Citrix Database Migration Process
  1. Change the Recovery Model of all Databases from Simple to Full. I have written a detailed article on How to choose Citrix Database recovery model. See above image.
  2. Ensure that each Database is using a collation that ends with “_CI_AS_KS”. Citrix recommends using a collation that ends with “_100_CI_AS_KS”. See above image.
  3. Ensure that Read committed Snapshot option is ON for all databases. For standalone Database Server and Availability group, select the Database, Right click and select Properties. Go to Options, scroll down to Is Read Committed Snapshot On. The value should be set to True, if not, do so.
    • For Mirrored Databases, if Read-Committed Snapshot is not enabled then mirroring has to be broken and reinstated. See CTX137161 for more detail.
Read-Committed Snapshot
  1. Go to Primary PVS server and boot Target devices from the Device Collections.
  2. Ensure that all target devices have booted successfully.
  3. Disable maintenance mode on all VDAs and try launching random apps and desktops.
  4. Delete snapshots and make Databases offline on original SQL server after 7 days.
  5. This concludes the Citrix Database Migration process.

Recommended Readings for Citrix Database Migration Process

  1. Provisioning Services: Service Account Permissions and Configuration for Accessing PVS SQL Database.
  2. How to Enable Database Offline in Provisioning Services Farm.
  3. Update XD database connection strings to include multisubnetdfailover=True for Always on availability groups.
  4. Update database connection strings when using SQL Server high availability solutions.
  5. SQL Server AlwaysOn Configuration for Provisioning Services 7.6.

3 thoughts on “Citrix Database Migration Process

  1. Excuse me, if I make sql an independent machine, ddc and storefront are one machine, is this the same?

  2. This really is a great blog. I did this procedure one time and it worked great, Will use this a second time for another farm in a few weeks. Thanks for the effort to set this up.

    For anybody who has the same troubles as me: SQL Express 2019 does set different dynamic ports after install. This happened 2 times to me yet. I had to set the dynamic port to 1433 for the controllers to be able to connect to the new database server.

Leave a Reply