With the latest Citrix Virtual Apps & Desktops (CVAD) LTSR version, older SQL Server versions have been discontinued. If you want to keep your environment stable and supportable, there is no way around migrating the Citrix databases (site, logging, monitoring) to modern SQL servers (2019/2022). Whether cluster, always on or mirroring – the procedure remains essentially the same. In this article, I will show you step by step how to migrate securely.
1. Prerequisites
- Complete backups of all Citrix databases
- Backups/VM snapshots of the delivery controllers (DDCs)
- New SQL Server (Cluster, Always On or Mirror)
- Same SQL version on Principal and Mirror
1.1 Create backups
Before you start the migration, the most important thing comes first: clean, verified backups.
Sounds trivial, but anyone who has ever had to restore a Citrix site without a working backup knows why this step is crucial.
Create complete database backups of all Citrix databases:
- Monitoring database (e.g. CitrixMonitoring)
- Site database (e.g. CitrixSite)
- Logging database (e.g. CitrixLogging)
1.1.1 Step-by-step in SSMS (GUI)
- Start SQL Server Management Studio (SSMS) and connect to the source SQL (Windows/SQL-Auth)
- Select the first DB in the Object Explorer (e.g. CitrixLogging)
- Right-click on the DB → Tasks → Backup…
- Set backup type to Full
- Under Destination, remove entries if necessary, then select Add… → File, e.g. D:BackupsCitrixLogging_Full_YYYYYMMDDHHmm.bak
- Media Options tab
- Verify backup when finished
- Perform checksum before writing to media
- Backup Options tab
- Compress backup (smaller, faster backups)
- Click OK → Wait for success message
- Repeat the whole process for Site-DB and Monitoring-DB (CitrixSite, CitrixMonitoring or similar)
- Create transaction log backups (Recovery Model Full!):
- Tasks again → Backup…, Backup type = Transaction Log, Target: *.trn
- Optional: save several log backups up to the time of migration
- Copy backup files to a share that the new SQL server can read
1.1.2 T-SQL Alternative
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Full Backups (COPY_ONLY, CHECKSUM, COMPRESSION empfohlen) BACKUP DATABASE [CitrixSite] TO DISK = N'D:BackupsCitrixSite_Full_YYYYMMDD.bak' WITH COPY_ONLY, CHECKSUM, COMPRESSION, STATS = 10; BACKUP DATABASE [CitrixLogging] TO DISK = N'D:BackupsCitrixLogging_Full_YYYYMMDD.bak' WITH COPY_ONLY, CHECKSUM, COMPRESSION, STATS = 10; BACKUP DATABASE [CitrixMonitoring] TO DISK = N'D:BackupsCitrixMonitoring_Full_YYYYMMDD.bak' WITH COPY_ONLY, CHECKSUM, COMPRESSION, STATS = 10; -- Transaction log (only with recovery model = FULL) BACKUP LOG [CitrixSite] TO DISK = N'D:BackupsCitrixSite_Log_YYYYMMDD_HHMM.trn' WITH CHECKSUM, COMPRESSION, STATS = 10; |
2. Database recovery
After the backup, the actual migration step follows: restoring the Citrix databases to the new SQL server.
It doesn’t matter whether you use a standalone instance, a cluster or Always On – the procedure is essentially the same.
2.1.1 Restore to new principal (GUI)
- Connect to the new SQL server in SSMS
- Object Explorer: Right-click Databases → Restore Database…
- Source = Device → Add… → Select Full-Backup *.bak
- Check destination (leave DB name unchanged)
- Files tab: Adapt paths to new drives if necessary
- Options tab:
- RESTORE WITH RECOVERY (Default)
- „Overwrite the existing database“ only if really necessary
- OK → Success message. Then import the last log backup if necessary (only if you want to go to T time)
2.1.2 Restore for T-SQL
|
1 2 3 4 5 6 7 8 |
RESTORE DATABASE [CitrixSite] FROM DISK = N'\BackupShareCitrixSite_Full_YYYYMMDD.bak' WITH MOVE N'CitrixSite' TO N'D:SQLDataCitrixSite.mdf', MOVE N'CitrixSite_log' TO N'D:SQLLogsCitrixSite.ldf', CHECKSUM, STATS = 10, RECOVERY; -- Optional up to a point in time: -- RESTORE LOG [CitrixSite] FROM DISK = N'...CitrixSite_Log_YYYYMMDD_HHMM.trn' WITH RECOVERY; |
2.2 Logins & authorizations
After the restore, the Delivery Controller (DDC) machine accounts must be given access to the new databases again.
As SQL server logins are server-wide, they are not copied during the backup/restore – so you have to create them manually.
|
1 2 3 |
CREATE LOGIN [DOMAIN\DDC01$] FROM WINDOWS; CREATE LOGIN [DOMAIN\DDC02$] FROM WINDOWS; CREATE LOGIN [DOMAIN\DDC03$] FROM WINDOWS; |
Then assign the appropriate roles in each Citrix database (site, logging, monitoring):
|
1 2 3 4 |
USE [CitrixSite]; EXEC sp_addrolemember db_owner', DOMAIN\DDC01$'; EXEC sp_addrolemember db_owner', DOMAIN\DDC02$'; EXEC sp_addrolemember db_owner', DOMAIN\DDC03$'; |
2.3 Optional: Seeding for always on / mirroring
If you want to operate your Citrix databases with high availability, you can integrate them into an Always On Availability Group or classic database mirroring.
At Always On, the following applies:
All replicas involved must have identical databases that were restored with NORECOVERY.
This is called seeding – i.e. the initial filling of the secondary replicas.
|
1 2 3 4 5 6 7 8 9 10 |
-- On the secondary node: RESTORE DATABASE [CitrixSite] FROM DISK = N'\BackupShareCitrixSite_Full_YYYYMMDD.bak' WITH MOVE N'CitrixSite' TO N'D:SQLDataCitrixSite.mdf', MOVE N'CitrixSite_log' TO N'D:SQLLogsCitrixSite.ldf', NORECOVERY, CHECKSUM, STATS = 10; RESTORE LOG [CitrixSite] FROM DISK = N'\BackupShareCitrixSite_Log_YYYYMMDD.trn' WITH NORECOVERY, CHECKSUM, STATS = 10; |
Then perform the same steps for logging and monitoring database.
As soon as all DBs are in the RESTORING state, you can add them to the availability group.
If you are still working with classic SQL mirroring, the procedure is similar:
Here too, the secondary database must be restored with NORECOVERY.
|
1 2 3 4 5 6 7 |
RESTORE DATABASE [CitrixSite] FROM DISK = N'\BackupShareCitrixSite_Full_YYYYMMDD.bak' WITH NORECOVERY; RESTORE LOG [CitrixSite] FROM DISK = N'\BackupShareCitrixSite_Log_YYYYMMDD.trn' WITH NORECOVERY; |
Then on the Principal:
|
1 2 |
ALTER DATABASE [CitrixSite] SET PARTNER = 'TCP://SQLMIRROR.domain.local:5022'; |
And on the mirror:
|
1 2 |
ALTER DATABASE [CitrixSite] SET PARTNER = 'TCP://SQLPRINCIPAL.domain.local:5022'; |
As soon as the “Synchronized” status is reached, you can add the other Citrix databases identically.
3. Prepare delivery controller
Before you can set the new database connections, the delivery controllers (DDCs) must be prepared.
The aim is to cleanly disconnect all existing connections to the old SQL environment, temporarily deactivate logging and monitoring and thus prevent Citrix services from writing incorrect data during the migration phase.
3.1 Deactivate monitoring & logging
First of all, all telemetry and logging services should be paused.
This prevents unnecessary write operations during the change of database connections.
|
1 2 |
Set-MonitorConfiguration -DataCollectionEnabled $false Set-LogSite -State Disabled |
This will temporarily deactivate both Citrix Monitoring and Configuration Logging.
As soon as the migration is complete, reactivate both functions.
3.2 Disconnect old DB connections
Now the old DB connections of the controllers must be resolved.
The Citrix services (Broker, Config, Logging, Monitoring, etc.) each have their own SQL connection strings, which you must set to zero individually.
To do this, first load the Citrix module:
|
1 |
Asnp Citrix* |
Then delete all existing connections:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Set-ConfigDBConnection -DBConnection $null Set-AcctDBConnection -DBConnection $null Set-AnalyticsDBConnection -DBConnection $null Set-AppLibDBConnection -DBConnection $null Set-BrokerDBConnection -DBConnection $null Set-EnvTestDBConnection -DBConnection $null Set-HypDBConnection -DBConnection $null Set-OrchDBConnection -DBConnection $null Set-ProvDBConnection -DBConnection $null Set-SfDBConnection -DBConnection $null Set-TrustDBConnection -DBConnection $null 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 |
After these commands, there are no longer any active SQL connections between the Citrix services and the old database environment.
- Carry out these steps on all Delivery Controllers (DDC01, DDC02, DDC03 etc.).
- In a running cluster, the order does not matter – you can switch controllers individually.
- If you want to be sure, run Get-BrokerServiceStatus beforehand to check whether the services are running properly.
All controllers are now disconnected from the old database, logging & monitoring is paused.
The environment is now ready to set the new connections to the new SQL server in the next step.
4. Configure new connections
After the old database connections have been disconnected, the new connection strings can now be set on the delivery controllers.
This step reconnects the Citrix services to their respective databases – but now on the new SQL server.
First define the target SQL server and the new database names.
Make sure that the SQL server name points either directly to the new server or – in the case of Always On – to the listener.
|
1 2 3 4 5 6 7 8 |
$ServerName = "SQL2022.domain.local" $DBName = "CitrixSite" $LogDBName = "CitrixLogging" $MonitorDBName = "CitrixMonitoring" $cs = "Server=$ServerName;Initial Catalog=$DBName;Integrated Security=True" $csLogging = "Server=$ServerName;Initial Catalog=$LogDBName;Integrated Security=True" $csMonitoring = "Server=$ServerName;Initial Catalog=$MonitorDBName;Integrated Security=True" |
If you use Always On, be sure to use the listener name (e.g. SQL-LISTENER.DOMAIN.LOCAL) so that your controllers remain connected even in the event of a failover without you having to adjust connection strings later.
4.1 Set Site-DB
Now establish the connection to the new site database.
This is the core of your Citrix environment and must be set first.
|
1 2 3 4 5 6 7 8 9 10 11 |
Set-AdminDBConnection -DBConnection $cs Set-ConfigDBConnection -DBConnection $cs Set-AcctDBConnection -DBConnection $cs Set-AnalyticsDBConnection -DBConnection $cs Set-HypDBConnection -DBConnection $cs Set-ProvDBConnection -DBConnection $cs Set-BrokerDBConnection -DBConnection $cs Set-EnvTestDBConnection -DBConnection $cs Set-AppLibDBConnection -DBConnection $cs Set-SfDBConnection -DBConnection $cs Set-TrustDBConnection -DBConnection $cs |
All central Citrix services (Broker, Config, Prov, Hyp, etc.) are thus reconnected to the Site DB.
4.2 Logging & Monitoring set
Next come the two secondary databases:
Configuration, Logging and Monitoring.
|
1 2 3 4 5 6 7 8 9 |
Set-LogDBConnection -DBConnection $cs Set-LogDBConnection -DataStore Logging -DBConnection $csLogging Set-LogDBConnection -DataStore Logging -DBConnection $null -Force Set-LogDBConnection -DataStore Logging -DBConnection $csLogging Set-MonitorDBConnection -DBConnection $cs Set-MonitorDBConnection -DataStore Monitor -DBConnection $csMonitoring Set-MonitorDBConnection -DataStore Monitor -DBConnection $null -Force Set-MonitorDBConnection -DataStore Monitor -DBConnection $csMonitoring |
This sequence ensures that both services point cleanly to the new databases and that no old connection strings are active.
4.3 Check connection
After setting the new connections, a short function test is mandatory.
Check the status of all Citrix services:
|
1 2 3 4 5 6 7 8 9 10 11 |
Get-AcctServiceStatus Get-AdminServiceStatus Get-BrokerServiceStatus Get-ConfigServiceStatus Get-EnvTestServiceStatus Get-HypServiceStatus Get-LogServiceStatus Get-MonitorServiceStatus Get-ProvServiceStatus Get-SfServiceStatus Get-AppLibServiceStatus |
All services should return the status OK and point to the new SQL server.
If you have several delivery controllers, repeat these steps on all nodes. Then use Get-LogDBConnection and Get-MonitorDBConnection to check whether all controllers really use the same connection strings. Different connection strings often lead to synchronization problems between the services later on.
4.4 Reactivate logging & monitoring
Once all connections have been successfully set, you can reactivate the previously deactivated services:
|
1 2 |
Set-MonitorConfiguration -DataCollectionEnabled $true Set-LogSite -State Enabled |
The Citrix site is now fully functional and ready for production again.
5. Conclusion
Migrating Citrix databases is not rocket science – but it does require discipline, a clean approach and an understanding of the internal dependencies of Citrix services.
If you “simply restore” without a plan, you risk faulty connections, defective logging databases or interrupted monitoring sessions.
With the structured process described above, however, the migration remains transparent, secure and reproducible.
The most important thing is:
- Check backups before you start.
- Restore databases in the correct order.
- Disconnect and reset controller connections in a controlled manner.
- Finally, test all services – it is better to test too much than too little.
Especially in productive enterprise environments, it is advisable to simulate the steps beforehand in a test farm and document the connection strings.
This prevents errors in the live system and you can roll back at any time if problems occur.
Those who also use Always On or SQL mirroring benefit from genuine high availability and ensure that their Citrix site continues to work even in the event of SQL failures without users noticing.
In short:
A clean Citrix database migration is less of a technical challenge than a question of preparation and care.
If you have mastered the steps, you not only have a stable system, but also the perfect template for future updates or SQL changes.
