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.

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

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

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

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

8. Confirm that the new MS SQL server version is compatible with XenDesktop/Citrix Virtual Apps and Desktop version.
   • Supported Microsoft SQL server version for XenDesktop 7.15 LTSR
   • Supported Microsoft SQL server version for Citrix Virtual Apps and Desktops 1912 LTSR

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

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

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

6. Right click on Database, select Tasks, and click on Backup.

7. Choose Full in Backup type, select Disk in Back up to: as Destination and Click on Add.

8. Provide a File name and click on OK twice.

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

10. Restore Databases. Open SSMS and login to the Database server. Right click on Databases and select Restore Database.

11. 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.
    • For Availability groups, add restored databases to Availability Databases and connect to all secondary replicas. See How to configure Citrix XenApp/XenDesktop to use Microsoft SQL multi-subnet (Basic) Availability Groups.
    • For Mirroring, create a backup of restored database and restore it on the mirror server with Recovery State set to RESTORE WITH NORECOVERY. See How to create a Database Mirroring.

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

13. In the Login name, type <domain name>\<computer name>$. Repeat it for other Delivery Controllers.

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

15. Create new login for PVS server computer accounts on Primary/Principal node as well as Secondary/Mirror/Replica node(s).
16. 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

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

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

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

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

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

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

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

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

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

11. Look for following events to verify connection has been successfully established with Database and LHC has been refreshed.

12. If you execute Get-BrokerController, secondary Delivery Controller’s state will show as Failed.

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

3. Select “The service that runs on anther computer” under “DHCP Services“. Click on Next.

4. Select “Provisioning services PXE service on this computer” under “PXE Service“. Click on Next.

5. Select “Join existing farm” under “Farm Configuration“. Click on Next.

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

7. Select the Farm in Farm name under Existing Farm. Click on Next.

8. Existing site should be already checked. Select Site in Site name under Site. Click on Next.

9. Existing Store should be already checked. Select a Store in Store name under Store. Click on Next.

10. Here you can change the License Server if required. Otherwise, click on Next under License Server.

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

12. Leave Automatic computer password update checked with Days set to 7 under Active Directory Computer Account Password. Click on Next.

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

14. Leave everything as it is under TFTP Option and Bootstrap Location. Click on Next.

15. Leave everything as it is under “Stream servers Boot List“. Click on Next.

16. Click on Next on Soap SSL Configuration.

17. Click on Next on Problem Report Configuration. If you plan to submit problem reports to Citrix, provide Citrix username and password.

18. Make sure Automatically Start Services is checked. Click on Finish.

19. At Configuring Service phase, PVS will establish connection with new Database server. Click on Done.

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

2. 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.
3. 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.
4. 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.

5. Go to Primary PVS server and boot Target devices from the Device Collections.
6. Ensure that all target devices have booted successfully.
7. Disable maintenance mode on all VDAs and try launching random apps and desktops.
8. Delete snapshots and make Databases offline on original SQL server after 7 days.
9. 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.