SQL Server and Citrix databases 编辑

Microsoft SQL Server is an important component of any Citrix Virtual Apps and Desktops 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 has a negative effect on 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 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.

In previous versions of Citrix Virtual Apps and Desktops, such as XenApp and XenDesktop 7.6, the database required for Citrix Virtual Apps and Desktops has been created as one database during the initial Site configuration (via Studio or by running scripts on the SQL Server). After the installation, the admin could split it into different databases to enhance performance or comply with backup/security guidelines.

With newer releases of Citrix Virtual Apps and Desktops, you can create the databases during the initial Site configuration, as well as via Studio, or by running scripts on the SQL Server. Your database is automatically split into three separate databases.

For environments with large Monitoring databases, an ideal configuration would be 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 Database Sizing Guidance or page 97 in the VDI handbook.

Each cumulative update (CU) for the long-term service release (LTSR) contains fixes to the SQL database schema. For example, see CTX230536. To best protect your environment against unexpected issues, make sure that you have a process to regularly upgrade your environment to the latest CU. Also, make sure that appropriate SQL server and database monitoring are in place to catch failure events and issues with high resource utilization and free space.

Citrix interaction with SQL

Citrix Virtual Apps and Desktops Brokers 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 Citrix ADC 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 initiates a launch request to the Delivery Controller. Then the Delivery Controller contacts the Site database on the SQL server to select appropriate VDA to send the user to.

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

Database recommendations

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 options that are supported by Citrix:

• AlwaysOn Availability Groups
• AlwaysOn Failover Clustering
• Basic Availability Groups
• Hypervisor HA*

Note:

While Citrix supports Hypervisor HA, it is not recommended to use it in environments hosting EHR apps, where uptime is of utmost importance.

Citrix and Epic 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 Always-On Availability Group 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 day-today 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%. See the Database maintenance section.

Local Host Cache

To account for scenarios in which the database becomes unavailable, Citrix added the Local Host Cache (LHC) feature into the Citrix Virtual Apps and Desktops 7.x platform (7.12 and later, including XenApp and XenDesktop 7.15 LTSR). Enabling this option allows users of Published Applications 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 Always On, Mirroring, or Clustering, 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.

Local Host Cache design considerations

Due to the variation in size of Epic Community Member 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 in XenApp and Xendesktop 7.15 is 10,000 VDAs in a single-zone and 40,000 VDAs in a multi-zone deployment. In a Citrix Virtual Apps environment, LHC and zone scalability is dependent 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 local host cache services can consume 2 plus GB 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 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 local host cache mode, storage usage increases approximately 1 MB 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:

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 vCPU and 8 GB of RAM for up to 10,000 users. For larger deployments or deployments with high logon rates, we recommend a minimum of 8 vCPU and 16 GB RAM. For more information about SQL database sizing concepts for Citrix Virtual Apps and Desktops 7.x 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. XenApp and XenDesktop 7.15 LTSR has more options than 7.6 LTSR, after the ability to capture granular VDA performance data has been added to the product. 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 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, such as locking, deadlocks, store procedures, that have been identified by Citrix or our customers. For example, there is number of SQL related fixes in the XenApp 7.6 CUs all the way up to CU5. The recommendation would be to review the Fixed issues section for each CU and search for SQL within the page.

Note:

LC8477 was released in 7.6 CU5 and 7.17

Additional references

• XenApp and XenDesktop 7.15 LTSR
• VDI Handbook
• Citrix Database Sizing
• Citrix Local Host Cache

Contributed by Henry Vernov, Principal System Engineer.