Preparing the SQL Server 2012 database

The evolution of the XenDesktop platform is not only in terms of Citrix core components, but also for collateral technologies used to implement its architecture. For this reason, we decided to implement all the latest releases of the software required by XenDesktop 7.6. This is also the case for the database component, which will be installed and configured in this recipe on Microsoft SQL Server 2012 edition.

Note

Even if the latest release of the SQL Server product is 2014, we preferred to work on the more supported and documented 2012 version.

Getting ready

XenDesktop 7.6 supports the following versions of Microsoft SQL Server:

• SQL Server 2008 R2 SP2 (Express, Standard, Enterprise, and Datacenter editions)
• SQL Server 2012 SP1 (Express, Standard, and Enterprise editions)
• SQL Server 2014 (Express, Standard, and Enterprise editions)

Note

SQL Server high availability supported features are: clustered instances, mirroring, and AlwaysOn Availability groups.

How can we choose the right database version? It depends on the required level of performance and availability. For standalone installations (integrated with the XenDesktop Controller server) within a test or POC environment, the Express Edition should be the right choice. In the presence of a huge number of clients and users, with a great number of processed data, if you want to create a clustered database instance, you should implement the non-Express version of SQL Server.

For a separate database installation, we need to perform the common installation operations, as explained in the following section.

How to do it...

Perform the following steps to generate SQL Server Database, which will be used by XenDesktop:

1. From the SQL Server installation media, launch the executable setup file. If you want, you can launch System Configuration Checker from the Planning section, to perform a preinstallation test and verify that all the requirements are met:
   
2. Click on the Installation tab, which you can see in the left-hand side menu and select New SQL Server stand-alone installation or add features to an existing installation option. For the purpose of this book, we won't execute all the steps required to complete the database installation:
   
3. If you have available resources, you can select to create a new named instance, not using the default SQL Server instance (MSSQLSERVER).
4. On the database server, create a database on the desired instance (preferably having a dedicated instance for Citrix, as previously seen) with the following parameters:
   • Create a new database instance on the database server, setting the Collation sequence parameter to Latin1_General_CI_AS_KS
   • Configure the authentication method only as Windows authentication
   • Configure the Permissions settings, as shown in the following table:

     Activity	Server role	Database role
     Database creation	dbcreator
     Schema creation	securityadmin	db_owner
     Controller addition	securityadmin	db_owner
     Controller removal		db_owner
     Schema update		db_owner

5. This permission will be granted to the operating system user, who will perform configuration activities through XenDesktop.

   Tip

   Using a separate instance is not mandatory, but it is better (more isolation, more security).

How it works...

We configured the most common format for the collation sequences (the same used by Citrix), and also restricted the way to log on to the database at Windows authentication, because XenDesktop does not support SQL or Mixed mode. For the collation, you are free to use not only the indicated version, but also the most important thing is that you will choose a version one that is member of the *_CI_AS_KS category (collation family is case and accent insensitive, but kanatype sensitive).

You must be careful when increasing the size of database logging; despite the normal data component (you should expect to have a database size of 250 MB with some thousands of clients), logs can unexpectedly increase in 24 hours in the presence of thousands of desktops. Based on the following table for MCS architectures, we will be able to calculate the database log and data files occupation:

Component	Data/log	Occupation
Registration information	Data	2.9 kB per desktop
Session state	Data	5.1 kB per desktop
Active Directory computer account info	Data	1.8 kB per desktop
MCS machine info	Data	1.94 kB per desktop
Transaction log for idle desktop	Log	62 kB per hour

Note

For a more detailed SQL Server installation, please refer to official Microsoft online documentation at http://msdn.microsoft.com/en-us/library/ms143219.aspx.

There's more...

In case of necessity to redeploy one or more Desktop Delivery Controller servers configured in your VDI infrastructure, the first action to perform is cleaning the XenDesktop configured database. To perform this task, you have to set all the Citrix components' database connection to null, using the custom PowerShell running the following commands:

Set-ConfigDBConnection -DBConnection $null
Set-AcctDBConnection -DBConnection $null
Set-HypDBConnection -DBConnection $null
Set-BrokerDBConnection -DBConnection $null

Once you finished these operations, you can proceed with the manual deletion and the recreation of the SQL Server database.

Note

Later in this book, we will explain how to use the PowerShell cmdlets available with XenDesktop 7.

See also

• The Retrieving system information – Configuration Service cmdlets recipe in the Chapter 9, Working with Powershell.