Product Documentation

Databases

Jul 07, 2016

A XenApp or XenDesktop 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 will continue to function until a user either logs off or disconnects. New connections cannot be established if the database server is unavailable, except in certain cases when connection leasing is configured.

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 may differ. For instructions, see CTX135207

If your Site contains more than one zone, the Site database should always be in the primary zone. 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 — 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 http://msdn.microsoft.com/en-us/library/hh510230.
  • SQL Server database mirroring — Mirroring the database ensures that, should 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 may interrupt the service to users.

Note: Installing a Controller on a node in an SQL clustering or SQL mirroring installation is not supported.

The connection leasing feature supplements the SQL Server high availability best practices by enabling users to connect and reconnect to their most recently used applications and desktops, even when the Site database is not available. For more information, see the Connection leasing article.

If all Controllers in a Site fail, you can configure the VDAs to operate in high availability mode so that users can continue to access and use their desktops and applications. In high availability mode, the VDA accepts direct ICA connections from users, rather than connections brokered by the Controller. This feature should be used only on the rare occasion when communication with all Controllers fails; it is not an alternative to other high availability solutions. For more information, see CTX 127564

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 generally sufficient for proof-of-concept or pilot deployments; however, SQL Server Express does not support Microsoft high availability features.

This installation uses the default Windows service accounts and permissions. Refer to 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 required, 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. Refer to Microsoft documentation for details about hiding an instance of SQL Server Database Engine.

Most production deployments, and any deployment that uses Microsoft high availability features, should use supported non-Express editions of SQL Server installed on machines other than the server where the first Controller is installed.  The System requirements article lists the supported SQL Server versions. The databases can reside on one or more machines.

Make sure 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 below.  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 below.

You can change the location of a database later, after you create the Site; see Change database locations below.

To configure a Site to use a mirror database, complete the following and then proceed with the automatic or scripted setup procedures.

  1. Install the SQL Server software on two servers, A and B.
  2. 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.
  3. On Server B, restore the backup file.
  4. Start mirroring on server A.

Tip: 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 the Delivery Controllers article.

Automatic setup

If you have the required database privileges, select the "Create and set up databases from Studio" option on the Databases page of the Site creation wizard, and 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, if needed, 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:

  1. In the Site creation wizard, select the Generate scripts option. This 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.
  2. Give those scripts to your database administrator. The Site creation wizard stops automatically at this point; you'll be prompted when you return later to continue the Site creation.

The database administrator then creates the databases. Each database should 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.
  • High availability features should be configured, 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.sql scripts on the high availability SQL Server database instances (if high availability is configured), and then run each of the xxx_Principal.sql scripts 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.

In Studio, you will be prompted 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, you'll see an error message.

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 *

 

Update a schema

Apply schema updates or hotfixes

 

db_owner

* While technically more restrictive, in practice, the securityadmin server role should be treated as equivalent to the sysadmin server role.

When using Studio to perform these operations, the user account must be a member of the sysadmin server role.

Database address formats

You can specify a database address in one of the following forms:

  • ServerName
  • ServerName\InstanceName
  • ServerName,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 databases. 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:

  1. Make sure 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.
  2. Select Configuration in the Studio navigation pane.
  3. Select the database for which you want to specify a new location and then select Change Database in the Actions pane.
  4. Specify the new location and the database name.
  5. If you want Studio to create the database and you have the appropriate permissions, click OK. When prompted, click OK, and then Studio will create 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.
  6. 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.