This article provides information about database sizing and mirroring, discusses some best practices, and provides links to additional resources.

Database Planning

There are a number of important areas to consider when planning an SQL database for XenDesktop 5.0, including:

• Database Sizing.

• Database Mirroring.

Database Sizing

When sizing an SQL database, two aspects are important:

• The database file.

• The database transaction log file.

Sizing the Database File

The size of the database file depends on the following factors:

• The number of configured and registered workstations.

• The number of connected sessions.

• The connection rate.

• The number of managed desktops.

• The number of provisioned desktops.

Each of the preceding factors impact the sizing requirements.

Each desktop (managed or unmanaged) requires:

• ~2.9 KB for registration and worker state information.

• ~5.1 KB for session state.

• 0.042 KB for each connection log record. By default, these are purged after 48 hours.

Each Machine Creation Service (MCS) provisioned desktop also requires:

• ~1.8 KB for AD computer account information.

• ~1.94 KB for MCS machine information.

For example, with 20,000 unmanaged desktops, in a normal day users will log on once, with some users perhaps roaming and reconnecting multiple times, allowing an average per user of two connections a day. Using these numbers and the data above, you can calculate the database file size as:

• Per Worker: 2.9 KB * 20,000 = 58,000 KB

• Per Session: 5.1 KB * 20,000 = 102,000 KB

• Per Connection: 0.042 KB * 40,000 * 2 days = 3,360 KB

• Total: 163,360 KB or ~160MB

Based on Citrix testing, this database sizing matches the size of a database for 20,000 users.

Using the preceding data, you can calculate the expected database sizes for different user counts. The sizing for a variety of user counts is shown in the following table: 

Note: This sizing information is a guide only. Your exact database usage might be slightly larger because of the SQL server having spare pages in tables. In addition, the number of catalogs and other smaller tables are not included here.

Sizing the Transaction Log

The transaction log is more difficult to size as this depends on a number of factors, including:

• The SQL database recovery model in use.

• Launch rate at peak times.

• The number of desktops.

In testing, with the full recovery model, it was found that a launch rate of 40 users a second to 20,000 desktops consumed ~1.3MB/s of transaction log space. This would consume ~670MB of transaction log.

Idle desktops consume ~62 KB of transaction log every hour.

Note: This depends on the heartbeat setting discussed later; increasing the heartbeat interval will decrease the log usage.

In a 24 hour period, one desktop consumes 1.45 MB and 20,000 desktops will consume ~29 GB.

In a large environment, this level of transaction log usage requires careful management.

By default, Desktop Studio configures any new SQL databases to use the simple recovery model. However, Citrix recommends that if full recovery model is in use, the transaction log be sized, so that it rarely has to grow. Testing showed that when SQL Server auto-grew the transaction log, all transactions stalled causing an increase in controller response times. Instead, Citrix recommends that a fixed-size transaction log be used and that an SQL Alert be setup so that when the transaction log reaches 50% full, the transaction log is backed up, thus freeing it. This maintains the transaction log at a reasonable size, without impacting the SQL Server performance.

Database Mirroring

This section provides information about SQL database mirroring and discusses best practices.

Database Mirroring Options

There are multiple methods of setting up a mirrored SQL database. The models are:

• Asynchronous mirroring.

• Synchronous mirroring, with witness.

• Synchronous mirroring, without witness.

Of these models, only Synchronous mirroring, with witness supports automatic failover. Therefore, Citrix recommends using this model for any production environment; otherwise, if the principal database fails, user intervention is required to fail over the database.

For more information about the different models and their performance characteristics, see - Database Mirroring Best Practices and Performance Considerations.

Database Mirroring Setup

This article does not discuss the initial setup of mirroring; for information about this topic, see - Database Mirroring Deployment How-to Topics.

Note: SQL mirroring can be configured before or after the SQL database is populated. Also, an empty SQL database (mirrored or unmirrored) can be populated using scripts generated by Desktop Studio, PowerShell SDK, or using Desktop Studio to directly populate the database.

Broker SQL Configuration String Setup

If mirroring is configured after the initial deployment of XenDesktop, you must update the connection strings so that the controllers can locate the mirrored SQL server in the event that the principal SQL Server fails. To do this, use the PowerShell SDK.

To retrieve your current connection string, use:
PS C:\> Get-BrokerDbConnection
Server=camr3e1b01.xds2.chsys2.citrix.com;Database=CitrixXenDesktopDB;Trusted_Connection=true

To the preceding string, add the Failover Partner clause, for example:
Server=camr3e1b02.xds2.chsys2.citrix.com;Database=CitrixXenDesktopDB;Trusted_Connection=true; Failover Partner=camr3e2b01.xds2.chsys2.citrix.com

Next, update the connection string for all the XenDesktop services on all the controllers. To do this, use the following script (with the MirroredConnectionString updated appropriately):

$MirroredConnectionString = ‘Server=camr3e1b02.xds2.chsys2.citrix.com;Database=CitrixXenDesktopDB;Trusted_Connection=true; Failover Partner=camr3e2b01.xds2.chsys2.citrix.com’

$brokers = Get-BrokerController

foreach ($broker in $brokers)
{
$brokerDNSName = $broker.DNSName
Set-ConfigDBConnection –DBConnection $null –adminaddress $brokerDNSName
Set-ConfigDBConnection –DBConnection $MirroredConnectionString –adminaddress $brokerDNSName

Set-AcctDBConnection –DBConnection $null –adminaddress $brokerDNSName
Set-AcctDBConnection –DBConnection $MirroredConnectionString –adminaddress $brokerDNSName

Set-BrokerDBConnection –DBConnection $null –adminaddress $brokerDNSName
Set-BrokerDBConnection –DBConnection $MirroredConnectionString –adminaddress $brokerDNSName

Set-HypDBConnection –DBConnection $null –adminaddress $brokerDNSName
Set-HypDBConnection –DBConnection $MirroredConnectionString –adminaddress $brokerDNSName

Set-PvsVmDBConnection –DBConnection $null –adminaddress $brokerDNSName
Set-PvsVmDBConnection –DBConnection $MirroredConnectionString –adminaddress $brokerDNSName

Set-ProvDBConnection –DBConnection $null –adminaddress $brokerDNSName
Set-ProvDBConnection –DBConnection $MirroredConnectionString –adminaddress $brokerDNSName
}

Database Mirroring Best Practices

Controller Configuration Changes

Citrix recommends that you change the default heartbeat timeout when using database mirroring. You can do this by changing the registry settings.

Caution! Refer to the Disclaimer at the end of this article before using Registry Editor. 

The two settings you must change are stored in HKEY_LOCAL_MACHINE\SOFTWARE\Citrix\DesktopServer

These settings are:

• HeartbeatPeriodMs – controls the interval that the workstations' heartbeats with the controller.
  Note: The workstations actually heartbeat at half the specified interval. The Controller terminates a workstation if it does not communicate at the specified interval. The default is 60,000 ms.

• MaxHeartbeatIntervalMs – specifies how long before a controller terminates a workstation; you can use this to override the preceding setting. By default, this is not configured.

In testing, it was found that if network connectivity on the principal SQL Server fails, it can take upto one minute for the XenDesktop services to failover to the mirrored SQL Server. This occurs because of TCP and network timeouts.

When workstations' heartbeats every 30 seconds, which requires an SQL operation, this results in workstations de-registering and then attempting to re-register. In addition, the controllers attempt to unregister any workers that do not heartbeat for over one minute, which can lead to workstations being marked as unregistered, although these are alive and capable of accepting a session.

Based on these findings, Citrix recommends you to increase the default HeartbeatPeriodMS to 10 minutes (600,000 ms). Although workstations might still fail to heartbeat during the SQL failover period, this prevents the controllers from de-registering them, so workstations are still available for users.

This works well with managed workstations as XenDesktop 5 removes any session records and automatically de-registers any Virtual Machines that are powered off, but do not unregister cleanly for any reason.

With unmanaged workstations, this increases the risk that a user might be directed to a workstation that is not available; however, the use case for unmanaged workstations is for workstations to be assigned, so it is expected that the user is more aware of the state of their workstation.

Note: When this occurs, the user receives a launch failure error in Web Interface.

Database Mirroring Maintenance

As previously mentioned in Database Planning, when an SQL database uses the full recovery model the transaction log must be backed up. Full recovery model is a requirement of database mirroring.

During testing, we found it best to avoid SQL transaction log growth as this impacts performance. Therefore, a system was used in which the transaction log is configured to a fixed size (2 GB) and an SQL alert is set so that, if the transaction log reaches 50% full, a job is run to back up the log.

Note: For alerts and jobs to be executed, the SQL Server Agent must be set up and configured to auto-start.

The job is set up to run an SQL command similar to the following:

BACKUP LOG [CitrixXenDesktopDB] TO DISK = N'D:\Rhone_Log_Backup\CitrixXenDesktopDB.bak' WITH NOFORMAT, NOINIT, COMPRESSION ,  NAME = N'Rhone-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The alert is configured to monitor the SQL performance counter:

SQLServer:Databases - Percent Log Used - CitrixXenDesktopDB

For more details on configuring jobs, see - Implement Jobs.

For more details on configuring alerts see - Monitor and Respond to Events.

Deleting a Mirrored Database

To delete a mirrored database, you must first remove the mirroring configuration. This is done through the properties of the database on the current principal database.

Once the database is no longer mirrored from the former principal database, you can delete the database.

On the former mirror, it might not be possible to delete the database while it is in a restoring state. If this is the case, take the database out of the restoring state before you delete it. You can do this by rolling pending transactions forward using the SQL statement:

RESTORE DATABASE CitrixXenDesktopDB
WITH RECOVERY

The database is then treated as a normal database and can be deleted.

Additional Resources

• Microsoft Whitepaper on Database Mirroring Best Practices; while written for SQL 2005, it still applies to SQL 2008 and SQL 2008 R2 - Database Mirroring Best Practices and Performance Considerations.

• Microsoft TechNet - Database Mirroring Deployment How-to Topics.