Search

SQL Collation for Citrix

Stephen Karras
Feb 1, 2016
3 min read

Citrix recommends that the SQL Server 'Read-Committed Snapshot' option is enabled on any XenDesktop database. This is particularly beneficial for sites that will include large numbers of machines or users, or that will experience significant load at certain times.

If Citrix Studio is used to create a new XenDesktop database, or upgrade an existing database, it usually enables 'Read-Committed Snapshot' at that time. However this might not always be the case. If this happens, it will be necessary to manually enable the option.

There are also two cases where Citrix Studio will never automatically enable 'Read-Committed Snapshot'. Those are as follows:

An existing mirrored database
An existing database forming part of an SQL Server 2012 availability group

In XenDesktop 7.x where multiple databases might be in use, 'Read-Committed Snapshot' should be enabled on all of them.

Using SQL Server Management Studio 2008

There is no simple mechanism in SQL Server Management Studio 2008 to either check the current state of, or enable, the 'Read-Committed Snapshot' option.

To check the current state of Read-Committed Snapshot, open a query window and execute the following command:
select is_read_committed_snapshot_on from sys.databases where name= 'CitrixSiteDB';
A value of 1 indicates that 'Read-Committed Snapshot' is already enabled and no change is required.
A value of 0 indicates that 'Read-Committed Snapshot' is disabled.

Use the following command to enable 'Read-Committed Snapshot'
alter database [CitrixSiteDB] set read_committed_snapshot on with rollback immediate;

Using SQL Server Management Studio 2012

SQL Server Management Studio 2012 has the ability to set the 'Read-Committed Snapshot' option directly. On the target database, select:

Properties > Options and scroll down to 'Is Read Committed Snapshot On'. Set the option to True where required.

Cases Not Handled by Citrix Studio

Where the target database is already mirrored, or forms part of an SQL Server 2012 availability group Citrix Studio never automatically enables 'Read-Committed Snapshot'. These cases must be dealt with manually as described in the following section:

Mirrored Databases

'Read-Committed Snapshot' cannot be enabled directly on a database that is already mirrored. The mirror must be broken, Read-Committed Snapshot enabled, and the mirror reinstated.

Using SQL Server Management Studio, on the current Principal database of the mirror, select Tasks > Mirror... Record the current mirroring configuration shown in the displayed dialog.
This is important because SQL Server Management Studio does not remember these settings once the mirror is broken.
Select Remove Mirroring. The Mirror database of the pair changes to state “Restoring…”. Do not change this database in any way.
On what was the Principal server, enable 'Read-Committed Snapshot' for the target database as previously described for the version of SQL Server Management Studio being used.
Select Tasks > Mirror… on the Principal database, then “Configure Security…” and proceed through the dialogs to re-establish the mirror. It is not necessary to back up the database to recreate the mirror, simply reentering the appropriate configuration causes mirroring to resume to the original Mirror database. The Mirror database changes from state “Restoring…” to “Mirror, Synchronized / Restoring…” once the mirror is reinstated.

Databases in SQL Server 2012 Availability Groups

Citrix Studio never automatically enables 'Read-Committed Snapshot' on an existing database that forms part of a SQL Server 2012 availability group. However, to do this manually, you must follow the instructions given earlier for enabling 'Read-Committed Snapshot' using SQL Server Management Studio 2012.