XenDesktop 7.x DB creation with locked SQL Servers

I have seen many projects in the past were it was not possible to automate the creation of XenDesktop databases during the deployment process because the database servers were locked out of security reasons. In this case the only chance of creating the databases was to deliver SQL scripts to the database team.
When the databases were created and permissions for the controllers were assigned I could go on with automating the environment.

The following lines of code create the SQL scripts for the database creation and the assignment of one controller to the site. Give the scripts to your database admins afterwards.

In any enterprise environment I recommend to separate the Site-, Logging- and Configuration database from each other.

This leads to three pairs of scripts:

Create Site Database script

Get-XDDatabaseSchema -SiteName SITENAME -DataStore Site -DatabaseName DBNAME -DatabaseServer DBSERVERNAME -ScriptType FullDatabase > c:\prep\dev_create_site_script.sql


Add Controller

Get-XDDatabaseSchema -AdminAddress CONTROLLERNAME-SiteName SITENAME -DataStore Site -DatabaseName DBNAME-DatabaseServer DBSERVERNAME -ScriptType AddController > C:\prep\dev_add_controller_site_script.sql


Create Logging Database script

Get-XDDatabaseSchema -SiteName SITENAME -DataStore Logging -DatabaseName DBNAME -DatabaseServer DBSERVERNAME -ScriptType FullDatabase > c:\prep\dev_create_logging_script.sql


Add Controller

Get-XDDatabaseSchema -AdminAddress CONTROLLERNAME-SiteName SITENAME -DataStore Logging -DatabaseName DBNAME-DatabaseServer DBSERVERNAME -ScriptType AddController > C:\prep\dev_add_controller_logging_script.sql


Create Monitoring Database script

Get-XDDatabaseSchema -SiteName SITENAME -DataStore Monitor -DatabaseName DBNAME -DatabaseServer DBSERVERNAME -ScriptType FullDatabase > c:\prep\dev_create_monitor_script.sql


Add Controller

Get-XDDatabaseSchema -AdminAddress CONTROLLERNAME-SiteName SITENAME -DataStore Monitor -DatabaseName DBNAME-DatabaseServer DBSERVERNAME -ScriptType AddController > C:\prep\dev_add_controller_monitor_script.sql

Please be aware of the fact that the following prerequisites must be met:

  • The databases need a special collation: Latin1_General_CI_AS_KS. You can run “ALTER DATABASE
    […] COLLATE Latin1_General_CI_AS_KS” before running any of the scripts.
  • The scripts need to be run in sqlcmd-mode

Have fun with your database admins, they will love you 😉

If there are no further restrictions in security and you are having access to a non-locked SQL server have a look at this Citrix blog post regarding automated site creation: http://blogs.citrix.com/2013/10/02/xendesktop-7-site-creation-via-powershell/

By |2016-10-30T20:12:43+00:00December 1st, 2014|Technical Stuff|2 Comments

About the Author: