Maintaining and restoring the XenDesktop 7.5 database

Posted on Posted in Citrix XenDesktop

This blog post is inspired by Shaun Wendland’s presentation at Synergy 2014.

Since version 7, XenDesktop uses the FlexCast Management Architecture (FMA). FMA replaces Independent Management Architecture (IMA).

FMA highly depends on a connection to a SQL database.

When the database isn’t available, no new connections can be made. Existing connections will stay active. So the database is the hearth of the infrastructure.

In this blog post I am going to configure, backup and restore the XenDesktop database(s).

 

Configuration of the different databases

XenDesktop has three databases:

  1. Site
  2. Logging
  3. Monitoring

The SQL server where the databases are hosted are shown in the column Server Address.

image

 

Best practice is to change the Logging and the Monitoring database so when problems occur it will not effect all three functionalities. The Site database cannot be changed.

 

Changing the Logging Database

Click on the Logging database and click on Change Database image
Fill in the name of the SQL server and the name of the Logging database.

Click on OK

image
Click on OK image
Wait for the wizard to finish. image
The Logging database is now a separate database. image
View from SQL Management Studio image

Changing the Monitoring Database

Click on the Monitoring database and click on Change Database image
Fill in the name of the SQL server and the name of the Logging database.

Click on OK

image
Click on OK image

image

Wait for the wizard to finish. image
The Logging database is now a separate database. image
View from SQL Management Studio image

 

Ok, so now Site, Logging and Monitoring are configured to use three separate databases.

 

Backup

The next step is to create a backup (plan) of the databases so they can be restored in case of a failure.

I am using a Maintenance Plan to create a backup, this requires that “Agent  XPs”  option is enabled on the SQL server. See this article for more information.

 

image

 

image

 

The following SQL script can be used to enable Agent XP’s:

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Agent XPs’, 1;
GO

PasteBin here

Paste the code and click on Execute image
Successful changed from 0 to 1 (enabled) image
Run a RECONFIGURE image
  image
Do a refresh of the SQL Management Console  
Agent XPs is now enabled. image

 

Creating a Maintenance Plan

Using a Maintenance Plan we can create SQL backups.

Open Microsoft SQL Server Management Studio  
Expand Management and right click on New Maintenance Plan image
Give the Maintenance Plan a name image
In the Toolbar, double click on “Back Up Database Task”
image
Next to Databases, click on the dropdown arrow.

Select the database that need to be back upped

Click ok

image
Select “Create a sub-directory for each database”

Click on OK

image
From the Toolbox, double click Maintenance Cleanup Task image
Open the Maintenance Cleanup Task

Enter the directory where the backups are saved.

Add the file extension BAK

Enable the option to remove backups older the X days/weeks.

I choose 3 days.

It must be long enough for you to detect failures and to enough time to restore to a point in time where everything worked fine.

image
Now that the backup is configured and old backups are removed regularly, it’s time to enable the backup.  
Click on the dots in the Subplan image
Configure how often the backup should be made.

Click on Ok

image
Now that the Maintenance Plan is configured, the plan can be executed.

Make sure the SQL Server Agent is running.

Right click on the plan and select Execute

image
Verify that the backups exist: image
image

 

Restoring the XenDesktop database from backup

Ok, so let’s pretend that our junior system engineer made a massive mistake or XenDesktop does not respond as we would like. Then it’s time to restore the database from backup:

 

Right click on Database and select Restore Files and Filegroups image
Change the database name to a unique name, I have chosen to add Restore behind it.

Select From Device
Click on the dots

image
Click on Add and navigate to the .BAK file from the backup

Click on Ok

image
The filenames will be unique

Click on OK  to start the restore.

image
The database is successfully restored. image

image

Point XenDesktop to the new database

So now that the database is restored to a point in time where everything worked fine, it’s now time to point XenDesktop to the restored database.

Please use PasteBin to copy the PowerShell commands. Scripts can be found here.

 

Start PowerShell on a server where the XenDesktop SDK PowerShell cmdlets are available

Load the XenDesktop snap-ins using Add-PSSnapin Citrix*

image

Run the command:

Get-LogDataStore

Write down the connection string.

image
The connection string is build up of the following components: SNAGHTML131f4df[4]
The next step is to fill a variable with the new connection sting, I modified the database name.

$cs = "Server=MEM01;Initial Catalog=CitrixWhitepaperRestore;Integrated Security=True“

 
Once the variable is filled, we can now reset all the active connections using the command:

Set-LogDBConnection -DataStore Logging -DBConnection $null

Set-MonitorDBConnection -DataStore Monitor -DBConnection $null

Set-MonitorDBConnection -DBConnection $null

Set-AcctDBConnection -DBConnection $null

Set-ProvDBConnection -DBConnection $null

Set-BrokerDBConnection -DBConnection $null

Set-EnvTestDBConnection -DBConnection $null

Set-SfDBConnection -DBConnection $null

Set-HypDBConnection -DBConnection $null

Set-ConfigDBConnection -DBConnection $null -force

Set-LogDBConnection -DBConnection $null -force

Set-AdminDBConnection -DBConnection $null -force

image
Now all the connections are closed, the connection to the restored database can be created:

Set-ConfigDBconnection -DBConnection $cs

Set-AdminDBconnection -DBConnection $cs

Set-LogDBconnection -DBConnection $cs

Set-AcctDBconnection -DBConnection $cs

Set-BrokerDBconnection -DBConnection $cs

Set-EnvTestDBconnection -DBConnection $cs

Set-HypDBconnection -DBConnection $cs

Set-MonitorDBconnection -DBConnection $cs

Set-ProvDBconnection -DBConnection $cs

Set-SfDBconnection -DBConnection $cs

 

 

If you did not change the Logging and the Monitor database, also run these commands:

 

Set-LogDBConnection -DataStore Logging -DBConnection $cs

Set-MonitorDBConnection -DataStore Monitor -DBConnection $cs

 

Because I moved the Logging and Monitor database I get an error. This is fine because I do not want to restore these databases;

image

image

Start the Citrix DesktopStudio.
Click on Configuration

The Site database now points to the restored database:

image

 

That’s it, we first created three separate databases for Site, Logging and Monitoring. Then we created a backup of the databases and simulated a restore. And at last we pointed XenDesktop to the restored database. Hopefully everything works fine now.

Thanks to Shaun Wendland for the inspiration.

3 thoughts on “Maintaining and restoring the XenDesktop 7.5 database

  1. Hi, Ralph. Thanks for the shout-out! Really like the post and the awesome level of detail you’ve provided. I’m also stoked to see that it includes a back-up and maintenance section – points I think are very important(!) to the health and resiliency of any XenDesktop site. Glad to hear you found my presentation helpful. Cheers!

  2. Hello Ralph I have a question, How to recover from a catasthrofic crash (no DDCs, no SQL server nor storefront servers survived) where the only backed up data are the ciitrix dabases in a tape.

    We have only the 3 databases files and new hardware to recover the environment.

Leave a Reply

Your email address will not be published. Required fields are marked *