Jump to content
Welcome to our new Citrix community!

SQL Server and Citrix Virtual Apps and Desktops databases

Microsoft SQL Server is an important component of any Citrix Virtual Apps and Desktops (CVAD) deployment.
To plan and understand Citrix SQL interactions is greatly beneficial to you and your organization in maintaining a healthy and well performing Citrix environment.
Lacking SQL Server high availability and ample compute resources have a negative effect on the user experience and uptime of the Citrix infrastructure.

Database summary

There are 3 databases that are required / created during the Citrix Virtual Apps and Desktops deployment:

Site: (also known as Site Configuration) Stores the running Site configuration, plus dynamic data related to brokering, such as current session state, connection, load, and Virtually Delivery Agent (VDA) status information.

Configuration Logging: (also known as Logging) Stores information about Site configuration changes and administrative activities. This database is used when the Configuring Logging feature is enabled (default = enabled).

Monitoring: Stores data used by Director, such as session and connection information.

During the initial configuration of Citrix Virtual Apps and Desktops, three separate databases are created - either via Studio or by running scripts on the SQL Server.

For environments with large Monitoring databases, an ideal configuration is to host the Monitoring database on a different server from the Site Configuration and Configuration Logging databases.
It records more data, changes occur more frequently, and the data is not considered to be as critical as the other databases.
For more information, see the article How to Change Database Locations of Monitoring and Configuration Logging Database.

Be sure that appropriate SQL server and database monitoring are in place to catch failure events and issues.

Citrix interaction with SQL

The Delivery Controllers use the database as a message bus for broker communications, storing configuration, monitoring, and auditing data.
The databases are constantly in use and can consume significant compute resources on the SQL server.

Resource enumeration (resources identified and presented to the user), resource launch, and session start up stages require the Citrix Delivery Controller to interact with the SQL server.

Enumeration:
After successful authentication through NetScaler and StoreFront, the Delivery Controller contacts the Citrix Site database to check which applications are available to the user, based on AD credentials. When resources are identified, additional information (such as names of apps, desktops, icons) is pulled from the database.

Launch:
When the user selects the application or desktop to be launched, StoreFront sends a launch request to the Delivery Controller. The Delivery Controller contacts the Site database on the SQL server to select the appropriate VDA to send the user to.

Session initialization:
Following session start-up, the VDA is in contact with the Delivery Controller to write session information into the Site database.

Database recommendations

To be sure that a SQL server outage has a minimal impact to the Citrix Virtual Apps and Desktops infrastructure, customers can choose from the following high availability database options:

  • SQL Server AlwaysOn Failover Cluster Instances
  • SQL Server AlwaysOn Availability Groups (including Basic Availability Groups)
  • SQL Server Database Mirroring

Citrix recommends using the same high availability approach for all three databases.
For example, if you plan to use SQL AlwaysOn Availability Groups as your HA strategy, use it for all three database objects.

We also recommend that you take a full daily backup of the Citrix databases themselves, especially the site database.

Retention periods vary according to organizational requirements, but it is typical to maintain seven days of full backups and at least a month of weekly backups.
Transaction log backup schedules have to be based on a combination of your organization's standards and the transaction log growth rate.
Make sure to monitor available storage on your SQL server.

Align your recovery model for the Citrix databases with the requirements of the high availability approach that you're taking.

As per a Microsoft recommendation, customers set up maintenance plans to run nightly and weekly to maintain the database indexes. The maintenance plans can simply reorganize the indexes over night during the week, and rebuild the indexes at weekends.

With that any performance impact of rebuilding any large indexes during daily operations, especially for a large Monitoring Database is avoided.
Microsoft recommends that indexes are rebuilt if they are greater than 30% fragmented, and reorganized if less than 30%.

Local Host Cache

To account for scenarios in which the database becomes unavailable, Citrix provides the Local Host Cache (LHC) feature with Citrix Virtual Apps and Desktops.

This option allows users of published applications and desktops to connect if communication between Delivery Controllers and the Citrix Site Configuration database is interrupted.
If SQL is configured in a highly available architecture, such as AlwaysOn or Mirroring, this feature affords extra fault tolerance when a full SQL outage occurs or network connectivity is interrupted.

This should not be considered an alternative to SQL high availability, because site management functionality is not available during a SQL outage and the failover process is not instantaneous.

In the event of a SQL outage, brokering functionality is lost until it has been transitioned to the LHC and VDAs have re-registered.
This scenario also happens when transitioning back to the normal mode of operation when SQL connectivity/availability is restored.

Local Host Cache keeps a copy of the static site data in a local SQL Express LocalDB database on every Delivery Controller. It relies on this data during a database outage to continuously support VDA registrations and session brokering requests.

For Citrix Virtual Apps and Desktops Sites with multiple Zones, a separate LHC exists for each Zone.
Delivery Controllers within the Zone keep dynamic data specific to their zone in the local SQL Express database.

Local Host Cache design considerations

Due to many variables across enterprise deployments, it is recommended that you work closely with Citrix to determine the extra resources required to use LHC.
In a Citrix Virtual Apps and Desktops environment, LHC and zone scalability are dependent upon logon rate and user count.

  • Scalability considerations

    • The maximum limits for the LHC of Citrix Virtual Apps and Desktops 2203 LTSR is 10,000 VDAs in a single-zone and 40,000 VDAs in a multi-zone deployment.
    • The actual scalability can be lower than the published maximums. We recommend considering extra zones if your expected session count exceeds 10,000 or your logon rate is greater than 10 users per second.
  • Delivery Controller sizing

    • When LHC is active, the elected primary Delivery Controller (DC) per zone handles all VDA registrations, enumerations, launches, and updates.
    • RAM: The LHC services can consume 2GB+ of RAM depending on the duration of the outage and the number of user launches.
    • CPU: Due to the additional CPU load on the elected DC, consider extra cores to compensate.

    A Controller’s CPU configuration, particularly the number of cores available to the SQL Server Express LocalDB, directly affects Local Host Cache performance.
    CPU overhead is observed only during the outage period when the database is unreachable and the High Availability service is active.

    While the LocalDB can use multiple cores (up to 4), it’s limited to only a single socket.
    Adding more sockets doesn't improve the performance. Instead, Citrix recommends using multiple sockets with multiple cores.
    In Citrix testing, a 2x3 (2 sockets, 3 cores) configuration provided better performance than 4x1 and 6x1 configurations.

  • Storage: During LHC mode, storage usage increases approximately 1MB every 2–3 minutes, assuming an average of 10 logons per second.

For more information, see the Local Host Cache article.

Effects of a database outage

In the event of a total database outage, nearly all critical Delivery Controller functions are affected, highlighting the need of implementing one of the recommended SQL HA strategies.
The following table calls out these effects:

Component Impact of Database Outage
Site configuration database Users cannot connect or reconnect to a virtual app or desktop.
Monitoring database Director doesn't display any historical data and Studio cannot be started. Brokering of incoming user requests and existing user sessions is not affected.
Configuration logging database If Allow changes when the database is disconnected has been enabled within Citrix Virtual Apps and Desktops logging preferences, an outage of the configuration logging database has no impact (other than configuration changes not being logged). Otherwise, administrators are unable to make any changes to the Citrix Virtual Apps and Desktops environment.

Note: Refer to What is unavailable during an outage, and other differences for more details on this topic.

SQL sizing recommendation

The SQL server must be sized correctly to ensure the performance and stability of an environment. Since every Citrix product uses SQL server in a different way and each customer has different usage patterns, no generic sizing recommendations can be provided.
Instead, per-product SQL server sizing recommendations are provided later, and performance must be carefully monitored during deployment to validate sizing assumptions.

For a SQL environment hosting only Citrix-related databases, the SQL servers should be provisioned with a minimum of 4 vCPUs and 8GB of RAM for up to 10,000 users.
For larger deployments or deployments with high logon rates, we recommend a minimum of 8 vCPUs and 16GB of RAM.

For more information about SQL database sizing concepts see the Citrix XenDesktop 7.x Database Sizing. This article also includes information on workload characteristics, such as estimated transaction log growth rate.

Keep in mind that the Monitoring Database varies in size depending on data retention settings.
Also, newer product versions have more options and data points which consume more space (for example LTSR 2203 vs LTSR 1912).
For more information on configuring these settings, see Monitoring policy settings.

CU updates and fixes

Several times a year, Citrix releases Cumulative Updates (CUs) for Citrix Virtual Apps and Desktops LTSRs. These CUs only contain security updates and bug fixes, with no new features introduced.

Citrix recommends running the latest CUs, because they fix issues that have been identified in the product. Some of these fixes are SQL related. They address the issues identified by Citrix or our customers (examples might include locking, deadlocks, or stored procedures).

We realize that applying comprehensive updates to Citrix Virtual Apps and Desktops environments takes time and proper planning (especially for mission-critical 24x7 situations) but Citrix highly recommends remaining as current as possible on CUs for optimal overall health and performance.

Latest documentation

This document calls out the importance of SQL with Citrix Virtual Apps and Desktops environments including HA strategy and various considerations. It is not meant as an all-inclusive literature on Citrix and SQL recommendations. For extra details and up-to-date information, refer to these documents:

Citrix Virtual Apps and Desktops Databases

Citrix Local Host Cache

Citrix XenDesktop 7.x Database Sizing


User Feedback


There are no comments to display.



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...