I’ve noticed that our support lists have had a number of requests recently about how to configure SQL mirroring for a XenDesktop site.  Generally because mirroring was added after initial site setup, and so needs to be configured afterwards.

They’ve done the “easy” part of actually mirroring their SQL database, now they’re trying to work out the commands to update the services.

Searching around you can find a XenDesktop 5 guide: CTX127538

Which kind of works on XenDesktop 7, but misses a number of the new services, and the secondary datastores for monitoring and config logging (as Citrix does recommend splitting them out)  In some cases though it’s made a mess of the site, and failover doesn’t work.

So I wanted to share a correct sequence of events for reconfiguring for SQL mirroring.

Assumptions:

  • You’ve updated your SQL DBs to be mirrored, and that’s working ok.
  • You’ve sorted out permissions on the secondary/mirror server.  This can be done either by using the scripts from XenDesktop, or if you’re using SQL 2012 with a partially contained database.
  • Your site is up and working and currently functional
  • You’re running XD 7.1 Controllers.  The -force option on the Set-AdminDBConnection was added in 7.1.
  • You’re not using Mandatory Configuration Logging, if you are you’ll need to turn it off.

To update the site, you’ll need to:

  • Create new connection strings which include the failover partner for the 3 databases.
  • Disconnect all the controllers from the databases
  • Reconnect all the controllers back to the databases

There are a couple of ways to achieve this.

The Scripted Way
I’ve created a powershell script that scripts the manual process, the powershell script can be found on sharefile:

https://citrix.sharefile.com/d/s5818bf6ccf14616b

The script should be run on a controller in the site.  It should handle updating of the services in the right order, and on all the controllers, along with some basic checking that all the services are up and running before it attempts to update them.
It’s been tested on Server 2012 R2. I expect it to work on Server 2012 and 2008 R2 as well, if it doesn’t please leave a comment and I’ll look into it.

The Manual Way

You’ll need to have 3 connection strings, one for each of:

  • Site Database, from
    $csSite = Get-BrokerDBConnection
  • Monitoring Datastore, from:
    $csMonitor = Get-MonitorDBConnection -DataStore Monitor
  • Configuration Logging DataStore, from:
    $csLog = Get-LogDBConnection - DataStore Logging
To each of these connection strings you’ll need to add the failover partner clause.

$csSite = $csSite + ";Failover Partner=FQDN.Of.Site.Mirror"
$csMonitor = $csMonitor + ";Failover Partner=FQDN.Of.Monitor.Mirror"
$csLog = $csLog + ";Failover Partner=FQDN.Of.Logging.Mirror"

The next step is to disconnect the controllers from the databases, there are 3 parts to this:

On one controller only, clear the datastore connections:

Set-MonitorDBConnection -DataStore Monitor -DBConnection $null -AdminAddress $Controller
Set-LogDBConnection -Datastore Logging -DBConnection $null -AdminAddress $Controller

On all the other controllers, reset their datastore configuration (which reads the cleared setting above)
Reset-MonitorDataStore –DataStore Monitor –AdminAddress $Controller
Reset-LogDataStore –DataStore Logging –AdminAddress $Controller

On every controller you need to disconnect all the services from the database, note that the order here is important, the last two cmdlets must be at the end:
Set-SfDBConnection -DBConnection $null -AdminAddress $Controller
Set-EnvTestDBConnection -DBConnection $null -AdminAddress $Controller
Set-MonitorDBConnection -DBConnection $null -AdminAddress $Controller
Set-BrokerDBConnection -DBConnection $null -AdminAddress $Controller
Set-ProvDBConnection -DBConnection $null -AdminAddress $Controller
Set-HypDBConnection -DBConnection $null -AdminAddress $Controller
Set-AcctDBConnection -DBConnection $null -AdminAddress $Controller
Set-ConfigDBConnection -DBConnection $null -AdminAddress $Controller
Set-LogDBConnection -DBConnection $null -AdminAddress $Controller
Set-AdminDBConnection –Force -DBConnection $null -AdminAddress $Controller

At this stage your site will have no db connectivity.

The next step is to hook all the services back up to the site database:

Set-AdminDBConnection -DBConnection $csSite -AdminAddress $Controller
Set-LogDBConnection -DBConnection $csSite -AdminAddress $Controller
Set-ConfigDBConnection -DBConnection $csSite -AdminAddress $Controller
Set-AcctDBConnection -DBConnection $csSite -AdminAddress $Controller
Set-HypDBConnection -DBConnection $csSite -AdminAddress $Controller
Set-ProvDBConnection -DBConnection $csSite -AdminAddress $Controller
Set-BrokerDBConnection -DBConnection $csSite -AdminAddress $Controller
Set-MonitorDBConnection -DBConnection $csSite -AdminAddress $Controller
Set-EnvTestDBConnection -DBConnection $csSite -AdminAddress $Controller
Set-SfDBConnection -DBConnection $csSite -AdminAddress $Controller

Then on one controller only, set the datastore connections:
Set-MonitorDBConnection -DataStore Monitor -DBConnection $csMonitor -AdminAddress $Controller
Set-LogDBConnection -Datastore Logging -DBConnection $csLog -AdminAddress $Controller

And on all the other controllers, reset their datastore configuration (which reads the new connection string from above):
Reset-MonitorDataStore –DataStore Monitor –AdminAddress $Controller
Reset-LogDataStore –DataStore Logging –AdminAddress $Controller

At this stage the site should be back up and running, and capable of starting up when the SQL server is failed over.
I would recommend testing failover of SQL. To fully check this all works, the SQL Server should be failed over, this checks that the services can talk to the mirror. Also the services (or server) should be restarted to check that the Failover Partner can be used to locate the mirror server.