SQL Server and CVAD 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 will have a negative effect on user experience and uptime of the Citrix infrastructure.
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 Virtualy 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.
When Citrix Virtual Apps and Desktops is installed, admins create the databases during the initial Site configuration (either via Studio or by running scripts on the SQL Server) and they are split into three separate databases.
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.
Make sure that you have a process to regularly upgrade your environment to the latest Cumulative Update (CU) when running Long Term Service Release (LTSR) versions. CUs often contain fixes that are SQL-related. Additionally, ensure appropriate SQL server and database monitoring is in place to catch failure events and issues such as high resource utilization, free disk space, etc.
Citrix interaction with SQL
Citrix Virtual Apps and Desktops Brokers (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.
For example, 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, etc.) is pulled from the database.
Launch: When the user selects the application or desktop to be launched, StoreFront initiates a launch request to the Delivery Controller. Then 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.
To ensure 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 that are supported by Citrix:
- SQL Server AlwaysOn Failover Cluster Instances
- SQL Server AlwaysOn Availability Groups (including Basic Availability Groups)
- SQL Server Database Mirroring
Citrix (and Epic for healthcare environments) recommend using the same high availability approach for all three databases, even though the configuration logging and monitoring database availability isn’t required for the establishment of end user sessions. 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 worth of weekly backups. Transaction log backup schedules should be based on a combination of your organization’s standards and the transaction log growth rate relative to the amount of available storage you have to allocate. 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 you’re taking.
As per Microsoft recommendation, customers should setup maintenance plans to run nightly and weekly to maintain the database indexes. The maintenance plans may simply be to reorganize the indexes over night during the week, and to rebuild the indexes at weekends.
This recommendation avoids any performance impact of rebuilding any large indexes during daily operations, especially for a large Monitoring Database. 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. Enabling 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 additional 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 is also encountered when transitioning back to the normal mode of operation when SQL connectivity/availability is restored.
Local Host Cache retains a copy of the static site data in a local SQL Express LocalDB database on every Delivery Controller, and relies on this data during a database outage to continuously support VDA registrations and session brokering requests.
For CVAD Sites with multiple Zones, a separate LHC will exist for each Zone. Delivery Controllers within the Zone retain dynamic data specific to their zone in the local SQL Express database.
Local Host Cache design considerations
Due to the many variables across enterprise deployments, it is recommended that you work closely with Citrix to determine additional resources required to utilize LHC.
- Scalability considerations
- The documented maximum limits for the LHC as of CVAD 2203 LTSR is 10,000 VDAs in a single-zone and 40,000 VDAs in a multi-zone deployment. In a CVAD environment, LHC and zone scalability is dependent upon logon rate and user count. Therefore, the actual scalability that is observed in your environment may be lower than the published maximums. For this architecture, we recommend considering additional zones if your expected session count exceeds 10,000 and/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 during the outage.
- CPU: Due to the additional CPU load on the elected DC, extra cores should be considered 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, even more so than memory allocation. This 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, for example, having 4 sockets with 1 core each, 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. Storage consumption increases relative to logon rate.
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 impacted, highlighting the importance of designing for and 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 (without LHC).|
|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 CVAD 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 all-encompassing sizing recommendations can be provided. Instead, per-product SQL server sizing recommendations are provided below, and performance should 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 for Citrix Virtual Apps and Desktops deployments, 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. Additionally, newer product versions have more options and data points which consume more space (e.g. LTSR 2203 vs LTSR 1912). For more information on configuring these settings, see Monitoring policy settings and factor this into the database sizing calculations.
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 that have been identified internally by Citrix or our customers (examples might include locking, deadlocks, or stored procedures). We realize that applying comprehensive updates to CVAD environments takes time and proper planning (especially for mission-critical 24x7 situations) but Citrix highly recommends to remain as current as possible on CUs for optimal overall health and performance.
This document calls out the importance of SQL with CVAD environments including HA strategy and various considerations. It is not meant as an all-inclusive literature on Citrix and SQL recommendations. For additional details and up-to-date information, please refer to these documents: