Citrix Server Databases: Ensuring High Availability and Performance

Citrix Virtual Apps and Desktops rely on robust database infrastructure to manage site configurations, logging, and monitoring. Understanding and properly managing these databases is crucial for maintaining a highly available and performant Citrix environment. This article provides a comprehensive overview of Citrix Server databases, focusing on their importance, high availability options, and setup procedures.

Understanding Citrix Server Databases

A Citrix Virtual Apps or Citrix Virtual Desktops site utilizes three SQL Server databases, each serving a distinct purpose:

• Site Database (Site Configuration): This core database stores the real-time configuration of your Citrix site, including session states and connection details. It’s the central repository for your Citrix environment’s operational data.
• Logging Database (Configuration Logging): As the name suggests, this database records site configuration changes and administrative activities when the configuration logging feature is enabled, which is the default setting. It provides an audit trail of modifications within your Citrix environment.
• Monitoring Database: This database is essential for Citrix Director, storing session and connection information. It powers the monitoring and historical reporting capabilities within Citrix Director.

Each Delivery Controller in your Citrix site communicates directly with the site database. For security, Windows authentication is mandated for communication between Controllers and databases. While Controllers operate independently, the site database represents a single point of failure. If the database server experiences downtime, existing user sessions remain active until logout or disconnection. For handling database unavailability, Citrix Local Host Cache (Local Host Cache) ensures continued user connectivity.

Citrix strongly recommends housing the site database within the primary zone if your deployment spans multiple zones. Controllers across all zones communicate with this central site database.

Web Studio and Citrix Studio Management Consoles: Note that this article focuses on Web Studio for managing Citrix Virtual Apps and Desktops deployments.

High Availability Options for Citrix Server Databases

To ensure continuous operation and minimize downtime, implementing high availability for Citrix server databases is paramount. Several solutions offer automatic failover capabilities:

• AlwaysOn Availability Groups (including Basic Availability Groups): Introduced in SQL Server 2012, AlwaysOn Availability Groups deliver enterprise-grade high availability and disaster recovery. This solution maximizes database availability by leveraging Windows Server Failover Clustering (WSFC). For in-depth information, refer to Windows Server Failover Clustering with SQL Server.
• SQL Server Database Mirroring: Database mirroring provides rapid automatic failover, typically within seconds, minimizing user disruption if the primary database server fails. However, this method carries a higher cost due to the requirement for full SQL Server licenses on both database servers. SQL Server Express edition is not compatible with mirrored environments.
• SQL Clustering: Microsoft SQL clustering offers automated failover by enabling a standby server to assume the roles of a failed server. While effective, setup complexity is higher, and automatic failover is generally slower compared to SQL mirroring.
• Hypervisor High Availability Features: Deploying the database as a virtual machine and utilizing your hypervisor’s high availability features presents a cost-effective solution. It leverages existing hypervisor infrastructure and supports SQL Server Express edition. However, automatic failover is slower as it involves starting a new VM for the database, potentially causing service interruptions.

Local Host Cache enhances these SQL Server high availability practices. It allows users to connect and reconnect to applications and desktops even when the site database is temporarily unavailable. Detailed information is available in Local Host Cache.

In the rare event of all Delivery Controllers failing, VDAs can operate in high availability mode, enabling users to directly access their applications and desktops via ICA connections, bypassing Controller brokering. This mode (CTX 127564) is a contingency measure, not a substitute for comprehensive high availability solutions.

Important Note: Installing a Delivery Controller on a node within an SQL clustering or SQL mirroring setup is not a supported configuration.

Installing SQL Server for Citrix Databases

Note:

As of version 2411, Azure SQL Managed Instance is supported for Citrix databases, providing cloud-based database options.

By default, installing the first Delivery Controller automatically installs SQL Server Express edition if no existing SQL Server instance is detected. This default setup is suitable for proof-of-concept or pilot deployments. However, SQL Server Express lacks support for Microsoft high availability features, making it unsuitable for production environments requiring robust resilience.

The default installation employs default Windows service accounts and permissions. Consult Microsoft documentation for specifics on these defaults, including the addition of Windows service accounts to the sysadmin role. In this configuration, the Controller utilizes the Network Service account and does not necessitate additional SQL Server roles or permissions.

For enhanced security, you can choose to Hide instance for the database instance. When configuring the database address in Web Studio, specify the instance’s static port number instead of its name. Refer to Microsoft documentation for details on hiding SQL Server Database Engine instances.

For most production deployments, and any deployment leveraging Microsoft high availability features, Citrix recommends utilizing supported non-Express editions of SQL Server. Install SQL Server on dedicated machines separate from the first Delivery Controller. Refer to System requirements for a list of supported SQL Server versions. Databases can reside on single or multiple machines as per your scalability and availability needs.

Ensure SQL Server software is installed prior to creating a Citrix site. While database pre-creation is not mandatory, if you choose to create them, they must be empty. Implementing Microsoft high availability technologies is also strongly advised at this stage.

Maintain SQL Server up-to-date using Windows Update to ensure security and performance.

Setting Up Citrix Databases via the Site Creation Wizard

During Citrix site creation, the Databases page in the site creation wizard is where you specify database names and addresses (locations). (See Database address formats.) To prevent potential errors when Director queries the Monitor Service, avoid using whitespace in the monitoring database name.

The Databases page provides two methods for database setup: automatic and scripted. Automatic setup is generally suitable if you (the Web Studio user and Citrix administrator) possess the necessary database privileges. (See Permissions required to set up databases.)

Post-site creation, you can modify the locations of the configuration logging and monitoring databases. See Change database locations.

To configure a mirrored database setup for your Citrix site, follow these preliminary steps before proceeding with automatic or scripted setup:

1. Install SQL Server software on two servers, designated as Server A and Server B.
2. On Server A, create the database intended as the principal database. Back up this database on Server A and transfer the backup to Server B.
3. On Server B, restore the backup file.
4. Initiate mirroring on Server A.

To verify mirroring post-site creation, execute the PowerShell cmdlet get-configdbconnection to confirm that the Failover Partner is correctly configured in the connection string to the mirror server.

If you subsequently add, move, or remove a Delivery Controller in a mirrored database environment, consult Delivery Controllers for specific considerations.

Automatic Database Setup

If you have the requisite database permissions, select Create and set up databases from Studio on the Databases page of the site creation wizard. Then, provide the names and addresses of the principal databases.

If a database already exists at a specified address, it must be empty. If databases are not found at the specified addresses, the wizard will inform you and prompt if you wish to create them. Upon confirmation, Web Studio automatically creates the databases and applies the initialization scripts for both principal and replica databases.

Scripted Database Setup

If you lack the necessary database rights, collaboration with a database administrator (DBA) is required. The process unfolds as follows:

1. On the Databases page in the site creation wizard, select Generate scripts to manually set up. This action generates three script types for each principal and replica database (site, monitoring, and logging):

   • “SysAdmin” Script: Creates databases and Delivery Controller login. Requires securityadmin rights.
   • “DbOwner” Script: Creates user roles, adds logins, and creates database schemas. Requires db_owner rights.
   • “Mixed” Script: Combines all tasks into a single script, irrespective of required rights.

   You can specify the script storage location.

   Note:

   In enterprise environments, database setup often involves separate teams with differing roles and permissions (securityadmin or db_owner). In such cases, “SysAdmin” scripts are executed by administrators with securityadmin rights, followed by “DbOwner” scripts run by administrators with db_owner rights. PowerShell can also be used to generate these scripts. See Preferred database rights scripts for details.

2. Provide these scripts to your DBA. The site creation wizard pauses at this point and prompts you to continue upon your return.

The DBA then proceeds to create the databases, ensuring each database adheres to these requirements:

• Utilize a collation ending in _CI_AS_KS. _100_CI_AS_KS collation is recommended.
• Enable SQL Server Read-Committed Snapshot for optimal performance. Refer to CTX 137161 for details.
• Implement high availability features, if applicable.
• For mirroring, set the database recovery model to full (simple is default). Back up the principal database and restore it on the mirror server. Finally, initiate mirroring on the principal server.

The DBA uses the SQLCMD command-line utility or SQL Server Management Studio in SQLCMD mode to execute the scripts:

• Run xxx_Replica.sql scripts on high availability SQL Server database instances (if configured).
• Run xxx_Principal.sql scripts on the principal SQL Server database instances.

Consult Microsoft documentation for SQLCMD specifics.

Once all scripts execute successfully, the DBA provides the Citrix administrator with the three principal database addresses.

Web Studio will then prompt you to resume site creation, returning you to the Databases page. Enter the database addresses. Error messages will appear if any database servers are unreachable.

Database Permissions for Citrix Server Setup

To create and initialize Citrix server databases (or modify database locations), you must be a local administrator, a domain user, and possess specific SQL Server permissions. These permissions can be explicitly granted or inherited via Active Directory group membership. If your Web Studio user credentials lack these permissions, you will be prompted to provide SQL Server user credentials.

Operation	Purpose	Server Role	Database Role
Create a database	Create a suitable empty database	dbcreator
Create a schema	Create service schemas and add the first Controller	securityadmin*	db_owner
Add a Controller	Add Controllers (beyond the first) to the site	securityadmin*	db_owner
Add Controller (mirror)	Add Controller login to the mirror database server	securityadmin*
Remove Controller	Remove Controller from site		db_owner
Update a schema	Apply schema updates or hotfixes		db_owner

* In practice, securityadmin server role can be considered equivalent to sysadmin for these operations, although technically more restrictive.

** Controller logons are not automatically removed upon Controller removal from a site to prevent unintended removal of logons used by other services. Manual removal, if desired, requires securityadmin server role membership.

When using Web Studio for these tasks, the Web Studio user must have a database server account with explicit membership in the necessary server roles or be able to provide credentials for such an account.

Preferred Database Rights Scripts for Enhanced Security

In enterprise environments, separating database setup scripts based on required roles (securityadmin or db_owner) enhances security and aligns with typical administrative responsibilities.

PowerShell enables you to specify preferred database rights, generating separate scripts based on role requirements. One script contains tasks needing securityadmin rights, while the other requires only db_owner rights, allowing Citrix administrators to execute it without DBA intervention.

The get-*DBSchema cmdlets offer the -DatabaseRights option with these valid values:

• SA: Generates a script for database creation and Delivery Controller login, requiring securityadmin rights.
• DBO: Generates a script for user roles, logins, and database schemas, requiring db_owner rights.
• Mixed: (Default) Creates a single script containing all tasks, regardless of required rights.

Refer to cmdlet help for comprehensive information.

Database Address Formats

Database addresses can be specified in these formats:

• ServerName
• ServerNameInstanceName
• ServerName,PortNumber

For AlwaysOn Availability Groups, use the group’s listener in the location field.

Changing Citrix Database Locations Post-Setup

After Citrix site creation, you can relocate the configuration logging and monitoring databases. However, the site database location is immutable post-creation. When changing database locations, consider these points:

• Data from the old database is not migrated to the new database.
• Logs cannot be aggregated across old and new databases when retrieving logs.
• The initial log entry in the new database indicates a database change but does not reference the previous database.
• Relocation of the configuration logging database is prohibited when mandatory logging is enabled.
• Changing database locations between SQL Server and Azure SQL is not supported.

To change a database location:

1. If using Microsoft SQL Server, ensure a supported version is installed on the target server and configure high availability features as needed.
2. Log in to Web Studio and navigate to Settings in the left pane.
3. Locate the Database tile and select Edit.
4. On the Manage Database page, select the database to relocate and choose Change Database in the action bar.
5. Specify the new location and database name. For Azure SQL hosting, provide credentials for a service principal with database management permissions.
6. To allow Web Studio to create the database (if you have permissions), click Done. Confirm when prompted. Web Studio will attempt database access using your credentials. If unsuccessful, you will be prompted for database user credentials. Web Studio then uploads the database schema. Credentials are used only for database creation.
7. To manually create the database or if you lack permissions, click Generate database script. The generated scripts contain instructions for manual database creation and mirror database setup, if needed. Before schema upload, ensure the database is empty and at least one user has database access and modification permissions.

Further Information on Citrix Server Databases

For more detailed information and best practices regarding Citrix server databases, consult the official Citrix documentation and the Citrix Tech Zone.

This revised article aims to provide a more SEO-friendly and comprehensive guide to Citrix server databases, targeting an English-speaking audience seeking information on this critical aspect of Citrix Virtual Apps and Desktops.