Databases
A Citrix Virtual Apps or Citrix Virtual Desktops site uses three SQL Server databases:
- Site: (also known as site configuration) stores the running site configuration, plus the current session state and connection 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.
Each Delivery Controller communicates with the site database. Windows authentication is required between the Controller and the databases. A Controller can be unplugged or turned off without affecting other Controllers in the site. This means, however, that the site database forms a single point of failure. If the database server fails, existing connections continue to function until a user either logs off or disconnects. For information about connection behavior when the site database becomes unavailable, see Local Host Cache.
Citrix recommends that you back up the databases regularly so that you can restore from the backup if the database server fails. The backup strategy for each database can differ. For instructions, see CTX135207.
If your site contains more than one zone, ensure that the primary zone always contais the site database. Controllers in every zone communicate with that database.
High availability
There are several high availability solutions to consider for ensuring automatic failover:
- AlwaysOn Availability Groups (including Basic Availability Groups): This enterprise-level high availability and disaster recovery solution introduced in SQL Server 2012 enables you to maximize availability for one or more databases. AlwaysOn Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes. For more information, see Windows Server Failover Clustering with SQL Server.
- SQL Server database mirroring: Mirroring the database ensures that, if you lose the active database server, an automatic failover process happens in a matter of seconds, so that users are generally unaffected. This method is more expensive than other solutions because full SQL Server licenses are required on each database server. You cannot use SQL Server Express edition in a mirrored environment.
- SQL clustering: The Microsoft SQL clustering technology can be used to automatically allow one server to take over the tasks and responsibilities of another server that has failed. However, setting up this solution is more complicated, and the automatic failover process is typically slower than alternatives such as SQL mirroring.
- Using the hypervisor’s high availability features: With this method, you deploy the database as a virtual machine and use your hypervisor’s high availability features. This solution is less expensive than mirroring because it uses your existing hypervisor software and you can also use SQL Server Express edition. However, the automatic failover process is slower, as it can take time for a new machine to start for the database, which might interrupt the service to users.
The Local Host Cache feature supplements the SQL Server high availability best practices. Local Host Cache enables users to connect and reconnect to applications and desktops even when the site database is not available. For more information, see Local Host Cache.
If all Controllers in a site fail, you can configure the VDAs to operate in high availability mode, which allows users to continue to access their desktops and applications. In high availability mode, the VDA accepts direct ICA connections from users, rather than connections brokered by the Controller. Use this feature only on the rare occasion when communication with all Controllers fails. The feature is not an alternative to other high availability solutions. For more information, see CTX 127564.
Installing a Controller on a node in an SQL clustering or SQL mirroring installation is not supported.
Install database software
By default, SQL Server Express edition is installed when you install the first Delivery Controller if another SQL Server instance is not detected on that server. That default action is usually sufficient for proof of concept or pilot deployments. However, SQL Server Express does not support Microsoft high availability features.
The default installation uses the default Windows service accounts and permissions. See the Microsoft documentation for details of these defaults, including the addition of Windows service accounts to the sysadmin role. The Controller uses the Network Service account in this configuration. The Controller does not require any additional SQL Server roles or permissions.
If necessary, you can select Hide instance for the database instance. When configuring the address of the database in Studio, enter the instance’s static port number, rather than its name. See the Microsoft documentation for details about hiding an instance of SQL Server Database Engine.
For most production deployments, and any deployment that uses Microsoft high availability features, we recommend using only supported non-Express editions of SQL Server. Install SQL Server on machines other than the server where the first Controller is installed. System requirements lists the supported SQL Server versions. The databases can reside on one or more machines.
Ensure the SQL Server software is installed before creating a site. You don’t have to create the database, but if you do, it must be empty. Configuring Microsoft high availability technologies is also recommended.
Use Windows Update to keep SQL Server up-to-date.
Set up the databases from the site creation wizard
Specify the database names and addresses (location) on the Databases page in the site creation wizard. (See Database address formats.) To avoid potential errors when Director queries the Monitor Service, do not use whitespace in the name of the monitoring database.
The Databases page offers two options for setting up the databases: automatic and using scripts. Generally, you can use the automatic option if you (the Studio user and Citrix administrator) have the required database privileges. (See Permissions required to set up databases.)
You can change the location of the configuration logging and monitoring database later, after you create the site. See Change database locations.
To configure a site to use a mirror database, complete the following and then proceed with the automatic or scripted setup procedures.
- Install the SQL Server software on two servers, A and B.
- On Server A, create the database intended to be used as the principal. Back up the database on Server A and then copy it to server B.
- On Server B, restore the backup file.
- Start mirroring on server A.
To verify mirroring after creating the site, run the PowerShell cmdlet get-configdbconnection to ensure that the Failover Partner has been set in the connection string to the mirror.
If you later add, move, or remove a Delivery Controller in a mirrored database environment, see Delivery Controllers.
Automatic setup
If you have the required database privileges, 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 exists at an address you specify, it must be empty. If databases don’t exist at a specified address, you are informed that a database cannot be found, and then asked if you want the database to be created for you. When you confirm that action, Studio automatically creates the databases, and then applies the initialization scripts for the principal and replica databases.
Scripted setup
If you do not have the required database privileges, someone with those permissions must help, such as a database administrator. Here’s the sequence:
- On the Databases page in the site creation wizard, select Generate scripts. This action generates six scripts: two for each of the three databases (one for each principal database and another for each replica). You can indicate where to store the scripts.
- Give those scripts to your database administrator. The site creation wizard stops automatically at this point. You are prompted when you return later to continue the site creation.
The database administrator then creates the databases. Each database must have the following characteristics:
- Use a collation that ends with
_CI_AS_KS. Citrix recommends using a collation that ends with_100_CI_AS_KS. - For optimum performance, enable the SQL Server Read-Committed Snapshot. For details, see CTX 137161.
- Configured high availability features, if desired.
- To configure mirroring, first set the database to use the full recovery model (simple model is the default). Back up the principal database to a file and copy it to the mirror server. On the mirror database, restore the backup file to the mirror server. Then, start mirroring on the principal server.
The database administrator uses the SQLCMD command-line utility or SQL Server Management Studio in SQLCMD mode to:
- Run each of the
xxx_Replica.sqlscripts on the high availability SQL Server database instances (if high availability is configured) - Run each of the
xxx\_Principal.sqlscripts on the principal SQL Server database instances.
See the Microsoft documentation for SQLCMD details.
When all the scripts complete successfully, the database administrator gives the Citrix administrator the three principal database addresses.
Studio prompts you to continue the site creation, and are returned to the Databases page. Enter the addresses. If any of the servers hosting a database cannot be contacted, an error message is displayed.
Permissions required to set up databases
You must be a local administrator and a domain user to create and initialize the databases (or change the database location). You must also have certain SQL Server permissions. The following permissions can be explicitly configured or acquired by Active Directory group membership. If your Studio user credentials do not include these permissions, you are prompted for SQL Server user credentials.
| Operation | Purpose | Server role | Database role |
|---|---|---|---|
| Create a database | Create a suitable empty database | dbcreator |
|
| Create a schema | Create all service-specific schemas and add the first Controller to the site |
securityadmin* |
db_owner |
| Add a Controller | Add a Controller (other than the first) to the site |
securityadmin* |
db_owner |
| Add a Controller (mirror server) | Add a Controller login to the database server currently in the mirror role of a mirrored database |
securityadmin* |
|
| Remove Controller | Remove controller from site | ** | db_owner |
| Update a schema | Apply schema updates or hotfixes | db_owner |
* While technically more restrictive, in practice, you can treat the securityadmin server role as equivalent to the sysadmin server role.
** When a Controller is removed from a site, either through Studio, or using scripts generated by Studio or SDK, the Controller logon to the database server is not removed. This is to avoid potentially removing a logon being used by services other than this Citrix product on the same machine. The logon must be removed manually if it is no longer required. This action requires securityadmin server role membership.
When using Studio to perform these operations, the Studio user must either have a database server account that is explicitly a member of the appropriate server roles, or be able to provide credentials of an account that is.
Preferred database rights scripts
In enterprise environments, database setup includes scripts that must be handled by different teams with different roles (rights): securityadmin or db_owner.
Using PowerShell, you can now specify the preferred database rights. (This feature is not available in Studio, which supports only a single script containing all tasks.)
Specifying a nondefault value results in separate scripts being created. One script contains tasks that need the securityadmin role. The other script requires only db_owner rights, and can be run by a Citrix administrator, without having to contact a database administrator.
In the get-*DBSchema cmdlets, the -DatabaseRights option has the following valid values:
-
SA: Generates a script that creates the databases and the Delivery Controller login. These tasks requiresecurityadminrights. -
DBO: Generates a script that creates the user roles in the database, adds the logins, and then creates the database schemas. These tasks requiredb_ownerrights. -
Mixed: (Default) All tasks in one script, regardless of required rights.
For more information, see the cmdlet help.
Database address formats
You can specify a database address in one of the following forms:
ServerNameServerName\InstanceNameServerName,PortNumber
For an AlwaysOn Availability Group, specify the group’s listener in the location field.
Change database locations
After you create a site, you can change the location of the configuration logging and monitoring databases. (You cannot change the location of the site database.) When you change the location of a database:
- The data in the previous database is not imported to the new database.
- Logs cannot be aggregated from both databases when retrieving logs.
- The first log entry in the new database indicates that a database change occurred, but it does not identify the previous database.
You cannot change the location of the configuration logging database when mandatory logging is enabled.
To change the location of a database:
- Ensure a supported version of Microsoft SQL Server is installed on the server where you want the database to reside. Set up high availability features as needed.
- Select Configuration in the Studio navigation pane.
- Select the database for which you want to specify a new location and then select Change Database in the Actions pane.
- Specify the new location and the database name.
- If you want Studio to create the database and you have the appropriate permissions, click OK. When prompted, click OK, and then Studio creates the database automatically. Studio attempts to access the database using your credentials. If that fails, you are prompted for the database user’s credentials. Studio then uploads the database schema to the database. The credentials are retained only for the database creation time frame.
- If you do not want Studio to create the database, or you do not have sufficient permissions, click Generate script. The generated scripts include instructions for manually creating the database and a mirror database, if needed. Before uploading the schema, ensure that the database is empty and that at least one user has permission to access and change the database.
More information
- Database sizing tool.
- Sizing the site database and configuring connection strings when using SQL Server high availability solutions.