Citrix Xenapp/Xendesktop 7.9 Database Migration – SQL Express to Mirror

For brand new Xenapp/Xendesktop implementations you are given the option to build out the Site quickly using SQL Express.  While SQL Express may be supported, it’s certainly not recommended.  Any Citrix XA/XD production site/farm should be installed into a full version of SQL.  Many Citrix projects start out as ‘Proof of Concept’ or Trial phase, get vetted out, then move to production.  Since many of these POCs are built with SQL Express (an option when building out a new farm/site), many production environments still utilize this technology.

This blog will focus on migrating an existing Citrix Xendesktop/Xenapp 7.9 Site from a SQL Express 2008R2 database to a SQL 2012 R2 Standard database in a mirrored configuration.  SQL server high availability is a key component in a stable environment.  SQL Mirroring or ‘Always On’ are the two preferred methods.  Mirroring seems to be more prevalent due to the SQL Enterprise license required for Always On.  This changes in SQL Server 2016 Standard. https://msdn.microsoft.com/en-us/library/mt614935.aspx. Although there are some limitations.  Ultimately I chose a SQL Mirror configuration in the end.

Citrix has a great article (http://support.citrix.com/article/CTX140319) detailing this process, so most of the steps and powershell commands I used was from there.  The article also explains some prerequisites that are needed. For instance, the database recovery model must be set to ‘Full’.

I’ll assume you can setup your own 3 SQL Servers (2 SQL Standard, 1 Express Witness).  Confirm the recovery model and other points of interest from Citrix’s article above.  When you have the mirror configuration in place, you can then proceed to run the powershell commands.   I separate this out into 4 batches of code:

First batch – This batch of code disables logging, sets the database server/name variables, and tests out the connections to confirm there are no errors before proceeding

Second batch – This batch of code Sets the Database connection strings to $NULL

Third batch – This batch of code confirms all the Connection Strings are set to $NULL.

Fourth batch – This batch of code sets the currently NULL’ed out Database Connection Strings to the new datbase server location and adds the ‘failoverpartner’ server to complete the Mirrored setup.

Here was a couple gotchas:

1. When testing with one delivery controller
   1. Citrix policy – ‘prohibit’ enable auto update of controllers
   2. Insert GPO to send VDA to one controller
   3. Remember to remove the policy and put back the 2^nd controller when done testing/moved.
2. Once disabling site logging and monitoring
   1. Do this on both controllers
   2. Then backup database
3. Issues with  Set-AppLibDBConnection –DBConnection $null
   1. Restart Delivery Controller server
   2. Had to stop the citrix delegated administration service
   3. Then command worked.
4. Setting Citrix Database Connections to $Null
   1. Make sure all services show up as DBUnconfigured
   2. Do not proceed unless they all are
5. When you get to the enable site logging and monitoring
   1. Do this on both controllers before proceeding
6. If changing the database name, avoid using spaces

Below is the Powershell code I used.  I categorized them in batches from ‘First’ to ‘Fourth’.  However, when I actually ran the code, I EXECUTED THIS LINE BY LINE.  This is so I could granularly confirm each output from the command was processed successfully and with desired results.

FIRST:

## This batch of code disables logging, sets the database server/name variables, and tests out the connections to confirm there are no errors before proceeding

#Disable Configuration Logging

Set-LogSite -State “Disabled”

write-host “Close and Reopen Studio”

Set-MonitorConfiguration -DataCollectionEnabled $False

Write-host “Backup database”

##

## Replace <dbserver> with the New SQL server, and instance if present

## Replace <dbname> with the name of your restored Database

##

##

$ServerName=”sql-servername\sqlexpress”

$DBName =”database-name”

#

$cs=”Server=$ServerName; Initial Catalog=$DBName; Integrated Security=True”

$cs

Test-AdminDBConnection -DBConnection $cs

Test-ConfigDBConnection -DBConnection $cs

Test-AcctDBConnection -DBConnection $cs

Test-AnalyticsDBConnection -DBConnection $cs

Test-HypDBConnection -DBConnection $cs

Test-ProvDBConnection -DBConnection $cs

Test-BrokerDBConnection -DBConnection $cs

Test-EnvTestDBConnection -DBConnection $cs

Test-LogDBConnection -DBConnection $cs

Test-MonitorDBConnection -DBConnection $cs

Test-SfDBConnection -DBConnection $cs

Test-AppLibDBConnection -DBConnection $cs

SECOND:

## This batch of code Sets the Database connection strings to $NULL

## First unregister the Delivery Controllers from the current database:

##

Set-ConfigDBConnection -DBConnection $null

Set-AcctDBConnection -DBConnection $null

Set-AnalyticsDBConnection -DBConnection $null

Set-HypDBConnection -DBConnection $null

Set-ProvDBConnection -DBConnection $null

Set-BrokerDBConnection -DBConnection $null

Set-EnvTestDBConnection -DBConnection $null

Set-SfDBConnection -DBConnection $null

Set-MonitorDBConnection -DataStore Monitor -DBConnection $null

Set-MonitorDBConnection -DBConnection $null

Set-LogDBConnection -DataStore Logging -DBConnection $null

Set-LogDBConnection -DBConnection $null

Set-AdminDBConnection -DBConnection $null

Set-AppLibDBConnection –DBConnection $null

Get-Service Citrix* | Stop-Service -Force

Get-Service Citrix* | Start-Service

Write-host “Restart Controller”

THIRD:

## This batch of code confirms all the Connection Strings are set to $NULL.

## DO NOT PROCEED UNLESS THEY ARE ALL $NULL

Get-AcctServiceStatus

Get-AdminServiceStatus

Get-BrokerServiceStatus

Get-ConfigServiceStatus

Get-EnvTestServiceStatus

Get-HypServiceStatus

Get-LogServiceStatus

Get-MonitorServiceStatus

Get-ProvServiceStatus

Get-SfServiceStatus

Get-AppLibServiceStatus

FOURTH:

## This batch of code sets the currently NULL’ed out Database Connection Strings to the new datbase server location and adds the ‘failoverpartner’ server to complete the Mirrored setup.

asnp Citrix*

##

## Replace <dbserver> with the New SQL server, and instance if present

## Replace <dbname> with the name of your restored Database

##

$ServerName=”PrimarySQLServerDNSName.domain.com”

$DBName =”DatabaseName”

$LogDBName = “DatabaseName”

$MonitorDBName = “DatabaseName”

$FailoverPartner = “SQLFailoverPartnerDNSname.domain.com”

#

$cs=”Server=$ServerName;Initial Catalog=$DBName;Integrated Security=True;Failover Partner=$FailoverPartner”

$csLogging= “Server=$ServerName;Initial Catalog=$LogDBName;Integrated Security=True;Failover Partner=$FailoverPartner”

$csMonitoring = “Server=$ServerName;Initial Catalog=$MonitorDBName;Integrated Security=True;Failover Partner=$FailoverPartner”

Set-AdminDBConnection -DBConnection $cs

Set-ConfigDBConnection -DBConnection $cs

Set-AcctDBConnection -DBConnection $cs

Set-AnalyticsDBConnection -DBConnection $cs

Set-HypDBConnection -DBConnection $cs

Set-ProvDBConnection -DBConnection $cs

#Set-PvsVmDBConnection -DBConnection $cs

Set-BrokerDBConnection -DBConnection $cs

Set-EnvTestDBConnection -DBConnection $cs

Set-LogDBConnection -DBConnection $cs

Set-LogDBConnection -DataStore Logging -DBConnection $null

Set-LogDBConnection -DBConnection $null

Set-LogDBConnection -DBConnection $cs

Set-LogDBConnection -DataStore Logging -DBConnection $csLogging

Set-MonitorDBConnection -DBConnection $cs

Set-MonitorDBConnection -DataStore Monitor -DBConnection $null

Set-MonitorDBConnection -DBConnection $null

Set-MonitorDBConnection -DBConnection $cs

Set-MonitorDBConnection -DataStore Monitor -DBConnection $csMonitoring

Set-AppLibDBConnection –DBConnection $cs    # 7.8 and newer

Set-SfDBConnection -DBConnection $cs

##Enable Monitoring

Set-MonitorConfiguration -DataCollectionEnabled $true

##Enable Configuration Logging

Set-LogSite -State “Enabled”

write-host “Restart Citrix Studio.”

Get-AcctServiceStatus

Get-AdminServiceStatus

Get-BrokerServiceStatus

Get-ConfigServiceStatus

Get-EnvTestServiceStatus

Get-HypServiceStatus

Get-LogServiceStatus

Get-MonitorServiceStatus

Get-ProvServiceStatus

Get-SfServiceStatus

Get-AppLibServiceStatus

Perform this same process on each Delivery Controller in your Site.

I hope this helps everyone.  There really wasn’t anything out there recent as far as blogs on this type of move with XenDesktop/XenApp 7.X.  So there you have it, fully functional database migration with XenApp/XenDesktop 7.9 from SQL Express to Mirrored Configuration.