Migration of Citrix databases

Table of Contents

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

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

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

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;

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.

CREATE LOGIN [DOMAIN\DDC01$] FROM WINDOWS;
CREATE LOGIN [DOMAIN\DDC02$] FROM WINDOWS;
CREATE LOGIN [DOMAIN\DDC03$] FROM WINDOWS;

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):

USE [CitrixSite];
EXEC sp_addrolemember db_owner', DOMAIN\DDC01$';
EXEC sp_addrolemember db_owner', DOMAIN\DDC02$';
EXEC sp_addrolemember db_owner', DOMAIN\DDC03$';

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.

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

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

RESTORE DATABASE [CitrixSite]
 FROM DISK = N'\BackupShareCitrixSite_Full_YYYYMMDD.bak'
 WITH NORECOVERY;

RESTORE LOG [CitrixSite]
 FROM DISK = N'\BackupShareCitrixSite_Log_YYYYMMDD.trn'
 WITH NORECOVERY;

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:

ALTER DATABASE [CitrixSite]
SET PARTNER = 'TCP://SQLMIRROR.domain.local:5022';

1 2	ALTER DATABASE [CitrixSite] SET PARTNER = 'TCP://SQLMIRROR.domain.local:5022';

And on the mirror:

ALTER DATABASE [CitrixSite]
SET PARTNER = 'TCP://SQLPRINCIPAL.domain.local:5022';

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.

Set-MonitorConfiguration -DataCollectionEnabled $false
Set-LogSite -State Disabled

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:

Asnp Citrix*

1	Asnp Citrix*

Then delete all existing connections:

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

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.

$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"

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

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

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.

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

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:

Get-AcctServiceStatus
Get-AdminServiceStatus
Get-BrokerServiceStatus
Get-ConfigServiceStatus
Get-EnvTestServiceStatus
Get-HypServiceStatus
Get-LogServiceStatus
Get-MonitorServiceStatus
Get-ProvServiceStatus
Get-SfServiceStatus
Get-AppLibServiceStatus

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:

Set-MonitorConfiguration -DataCollectionEnabled $true
Set-LogSite -State Enabled

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.

1. Prerequisites

1.1 Create backups

1.1.1 Step-by-step in SSMS (GUI)

1.1.2 T-SQL Alternative

2. Database recovery

2.1.1 Restore to new principal (GUI)

2.1.2 Restore for T-SQL

2.2 Logins & authorizations

2.3 Optional: Seeding for always on / mirroring

3. Prepare delivery controller

3.1 Deactivate monitoring & logging

3.2 Disconnect old DB connections

4. Configure new connections

4.1 Set Site-DB

4.2 Logging & Monitoring set

4.3 Check connection

4.4 Reactivate logging & monitoring

5. Conclusion

Teilen mit:

Like this:

Related posts: