This article describes how to convert an existing operational XenDesktop 7 site to use a mirrored SQL Server database.

Use of a mirrored database is the option of choice where high-availability of a site is important.

The required re-configuration of the site and suggested subsequent verification of the database failover behavior are covered. However, it is assumed that the reader is familiar with the steps required to re-configure the underlying database as a mirrored database. This information is covered in various Microsoft articles, for example http://technet.microsoft.com/en-us/library/bb510479.aspx.

Instructions

SQL Server Prerequisites

XenDesktop 7 supports use of SQL Server 2012 Service Pack 1, and SQL Server 2008 R2 Service Pack 2.

Use of SQL Server mirroring requires two licensed installations of SQL Server (the principal and mirror). One of these servers might be the one already in use by the XenDesktop site, although it does not need to be.

Note: SQL Server Express Edition cannot be used in the role of either principal or mirror.

For automatic failover support, a further SQL Server installation is required (the witness). This server might be an SQL Server Express Edition installation.

The recommended mode of mirroring operation is high-safety with automatic failover (synchronous). If automatic failover is not required, then high-safety without automatic failover (synchronous) is also acceptable.

XenDesktop Prerequisites

Because all XenDesktop services in the site must be reconfigured, it is recommended that all site controllers are available and operational before attempting any configuration changes.

The site will be unavailable during reconfiguration of the database, although existing user sessions will be unaffected. Appropriate downtime for the site should be scheduled to allow for the reconfiguration. 

Use of all the XenDesktop service SDKs is required. A machine, which can be one of the site controllers, must be available that has all the SDKs installed and registered in a PowerShell window. To make use of the PowerShell scripts present in this article also requires the machine to have the standard SQL Server SQLCMD utility installed locally.

XenDesktop Site Preparation

To start with, the DB connectivity will be tested. Also prior to reconfiguring the database, all XenDesktop services in the site must be disconnected from the current database. This is done by clearing the database connection strings, which places all services into a quiescent state. This can be achieved using the following PowerShell script:

Note: It is assumed that the Broker PowerShell SDK Snap-in has already been used to contact the site being reconfigured so its initial default admin address is set to one of the controllers in that site. If this has not been done, the XenDesktop PowerShell SDK Snap-in should be loaded by running Add-PSSnapin Citrix* from within PowerShell.

If the database used prior to the configuration of mirroring forms the principal of the new-mirrored configuration then only the controller logons are required to be added to the new mirror server. Where both database servers in the mirrored pair are new, logons must be created on both.

Suitable logons can be created using the Broker PowerShell SDK and SQLCMD as shown. Substitute the name of the database server on which the logons are to be created and the name of the target database as required:

$PrimaryDB= 'FQDN name of the primary DB within quotes'
$mirrorDB = 'FQDN name of the mirror DB within quotes'
$dbName = 'Name of the XD database within quotes'
$controllers = Get-BrokerController | %{$_.DNSName}
foreach ($controller in $controllers) {
    Write-Host "Make login for $controller on $mirrorServer ..."
    Get-BrokerDBSchema -DatabaseName $dbName -ScriptType Login -AdminAddress $controller > add-login.sql
    sqlcmd -S $mirrorDB -Q ':r add-login.sql'
    }

# In Maintenance Window , Failover to the Mirror and run the following to test connectivity

$cs = "Data Source=$PrimaryDB; Failover Partner=$MirrorDB; Initial Catalog=$dbName; Integrated Security=True; Network=dbmssocn"

$controllers = Get-BrokerController | %{$_.DNSName}
foreach ($controller in $controllers) {
    Write-Host "Testing controller $controller ..."

Test-ConfigDBConnection -DBConnection $cs -AdminAddress $Controller
Test-AcctDBConnection -DBConnection $cs -AdminAddress $Controller
Test-HypDBConnection -DBConnection $cs -AdminAddress $Controller
Test-ProvDBConnection -DBConnection $cs -AdminAddress $Controller
Test-BrokerDBConnection -DBConnection $cs -AdminAddress $Controller
Test-EnvTestDBConnection -DBConnection $cs -AdminAddress $Controller
Test-SfDBConnection -DBConnection $cs -AdminAddress $Controller
Test-MonitorDBConnection -DBConnection $cs -AdminAddress $Controller
Test-MonitorDBConnection -DataStore Monitor -DBConnection $cs -AdminAddress $Controller
Test-AdminDBConnection -DBConnection $cs -AdminAddress $Controller
Test-LogDBConnection -DBConnection $cs -AdminAddress $Controller
Test-LogDBConnection -Datastore Logging -DBConnection $cs -AdminAddress $Controller
}

​For version 7.6, also execute "Test-AnalyticsDBConnection -DBConnection $cs -AdminAddress $controller".

# Now set the DB connections to null.

$controllers = Get-BrokerController | %{$_.DNSName}
foreach ($controller in $controllers) {
    Write-Host "Disconnect controller $controller ..."

Set-ConfigDBConnection -DBConnection $null -AdminAddress $Controller
Set-AcctDBConnection -DBConnection $null -AdminAddress $Controller
Set-HypDBConnection -DBConnection $null -AdminAddress $Controller
Set-ProvDBConnection -DBConnection $null -AdminAddress $Controller
Set-BrokerDBConnection -DBConnection $null -AdminAddress $Controller
Set-EnvTestDBConnection -DBConnection $null -AdminAddress $Controller
Set-SfDBConnection -DBConnection $null -AdminAddress $Controller
Set-MonitorDBConnection -Datastore Monitor -DBConnection $null -AdminAddress $Controller
reset-MonitorDataStore -DataStore Monitor
Set-MonitorDBConnection -DBConnection $null -AdminAddress $Controller
Set-LogDBConnection -DataStore Logging -DBConnection $null -AdminAddress $Controller
reset-LogDataStore -DataStore Logging
Set-LogDBConnection -DBConnection $null -AdminAddress $Controller
Set-AdminDBConnection -DBConnection $null -AdminAddress $Controller
}

For version 7.6, also execute “Set-AnalyticsDBConnection -DBConnection $null -AdminAddress $controller”.

Note: The variable $controllers obtained in the preceding script is required as input to the PowerShell scripts shown later in this article. Ideally, the scripts should be run in the same PowerShell window. However, if this is not possible, the variable is simply an array containing the DNS names of all the controllers in the site.

The following commands needs to be executed only on one DDC. Do not run them when you are configuring DB mirroring on other DDCs within the same site.

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

Mirroring the Database

After the services have been disconnected from the database, the underlying database can be reconfigured as a mirrored database. As far as the XenDesktop site is concerned, it is not important whether the existing database becomes the initial principal database of the new configuration, or whether a completely new-mirrored configuration is created – potentially using entirely different database servers. The only critical point is that the original content of the XenDesktop site database is present in the newly mirrored database.

Note the following points when the existing database is intended to be reused directly as the initial principal in the mirrored configuration:

• If the database was originally created using Desktop Studio, its recovery model is set to Simple. This must be changed to Full before it can be mirrored.

• If the database was created using Quick Deploy, it is using a local installation of SQL Server Express Edition.

  This edition of the server does not support mirroring. In this case, the database must be backed-up and restored on a different SQL Server installation, which is running a suitable non-Express Edition.

• Mirroring a database that uses a fixed, non-default port is problematic. Citrix recommends that mirrored databases be hosted by database engines using the default port 1433.

After setting up the mirrored database, and before continuing, verify the basic failover operation of the database. This can be done through SQL Server Management Studio (SSMS) using the Failover option available from the database properties Mirroring dialog.

In standard operation, the two databases should be shown in states (Principal, Synchronized) and (Mirror, Synchronized/Restoring…). Selecting the Failover option in the database mirroring properties dialog should cause the two databases to switch roles.

Enabling Mirrored Database Use by the XenDesktop Site

After the mirrored database is configured and its basic failover behavior verified, the XenDesktop services must be configured to use the new database. This entails:

• Ensuring that correct logons for all controllers in the site exist on both database servers
• Defining a new connection string including details of both database servers in the mirrored pair

Note the following points:

• Because all XenDesktop services run as NetworkService, a single logon suffices for all services on a single controller. In the preceding script, although only the broker logon script is used, this is sufficient to enable access for all the services on that controller.
• It is assumed that neither database server in a mirrored configuration is itself a XenDesktop controller. This is not a recommended configuration and the script shown does not create appropriate database logons in this case.
• It is assumed that the Broker PowerShell SDK snap-in has already been used to contact the site being reconfigured and so its initial default admin address is set to one of the controllers in the site.
• The preceding PowerShell commands shown assume that the standard SQL Server utility SQLCMD is available locally.
• This script must be run as the user who has sysadmin rights on the database servers to be modified.
• If appropriate logons already exist on the database server, they are not modified.

The final step of the reconfiguration is to re-establish the connections from the XenDesktop services to the mirrored database. This requires a revised connection string to be distributed to all the service instances. An example connection string suitable for a mirrored database is:

"Data Source=$PrimaryDB; Failover Partner=$MirrorDB; Initial Catalog=$dbName; Integrated Security=True; Network=dbmssocn"

Citrix recommends the use of the Network=dbmssocn option with mirrored databases. This option enforces use of the TCP provider for communication with both database servers by the XenDesktop services; this provider is optimized for use with mirroring. Also, note that:

• If you previously used TCP: as a prefix on the database server name in the connection string, you should switch to using the Network=dbmssocn option.
• Specifying the TCP: prefix on both server names in a mirroring connection string does not enforce use of the TCP provider for both database servers.
• The Network=dbmssocn option is incompatible with the use of the TCP: prefix; the database connection fails if both are present.

The new connection string can be applied to all services in the site using the XenDesktop PowerShell SDK as shown in the following section. Substitute the new connection string value as required. Applying the connection string implicitly re-enables all the XenDesktop services; the site resumes operation from its state at the time when the services were originally disconnected:

$cs = '<new connection string>'

foreach ($controller in $controllers) {
    Write-Host "Reconnect controller $controller ..."

Set-AdminDBConnection -DBConnection $cs -AdminAddress $Controller
Set-LogDBConnection -DBConnection $cs -AdminAddress $Controller
Set-ConfigDBConnection -DBConnection $cs -AdminAddress $Controller
Set-AcctDBConnection -DBConnection $cs -AdminAddress $Controller
Set-HypDBConnection -DBConnection $cs -AdminAddress $Controller
Set-ProvDBConnection -DBConnection $cs -AdminAddress $Controller
Set-BrokerDBConnection -DBConnection $cs -AdminAddress $Controller
Set-EnvTestDBConnection -DBConnection $cs -AdminAddress $Controller
Set-SfDBConnection -DBConnection $cs -AdminAddress $Controller
Set-MonitorDBConnection -DBConnection $cs -AdminAddress $Controller
Set-MonitorDBConnection -DataStore Monitor -DBConnection $cs -AdminAddress $Controller
Set-LogDBConnection -Datastore Logging -DBConnection $cs -AdminAddress $Controller
}

For version 7.6, also execute Set-AnalyticsDBConnection -DBConnection $cs -AdminAddress $controller

Just in case, if an error pops-up as specified in the following section while executing the preceding command, the appropriate connection string value has to be emptied.

Set-AdminDBConnection : The operation could not be performed because of a configuration logging error.

At line:16 char:22

+ Set-AdminDBConnection <<<<  -DBConnection $null -AdminAddress $Controller

    + CategoryInfo          : InvalidOperation: (:) [Set-AdminDBConnection], InvalidOperationException

    + FullyQualifiedErrorId : Citrix.XDPowerShell.Status.ConfigurationLoggingError,Citrix.DelegatedAdmin.Sdk.Cmdlets.DataStore.Commands.SetAdminDBConnectionCommand

The connection string value has to be manually emptied in the registry as specified in the following section:

Caution! Refer to the Disclaimer at the end of this article before using Registry Editor. 

Browse to HKLM\Software\Citrix\XDservices. Based on the specific service connection that failed during execution, the appropriate connection string value has to be emptied.

For example, if the AdminDBConnection failed during execution as specified in the preceding section, empty the ConnectionString value under HKLM\Software\Citrix\XDservices\DAS\Datastore\Connections.

If Virtual Delivery Agents were registered and user sessions active at the time when the services were disconnected, there is a period of several minutes following the resumption of operation where the site data recorded in the database is resynchronized with the current state of the active Virtual Delivery Agents and any user sessions. This is an expected behavior; no further corrective action is required.

Verification of Database High Availability

Citrix strongly recommends that you verify the correct failover behavior of the XenDesktop site with respect to the mirrored database. It is important that all the following test steps are successful:

• Successfully starts-up and its services connect to whichever database server is the current principal at the time.
• Fails-over in both directions between database servers if a failure occurs while the services are active.

Because the connection strings used are the same for all services, it should be sufficient to select a single service instance for test purposes. In the following example test sequence, an arbitrary broker service instance within the site is used:

$cs = Get-BrokerDBConnection
Set-BrokerDBConnection –DBConnection $null
Set-BrokerDBConnection –DBConnection $cs
Get-BrokerServiceStatus

With a correctly functioning database, the output from the preceding script is:

DBUnconfigured
OK
OK

To verify the correct interaction between the XenDesktop site and the mirrored database, use the following sequences of tests.

Database Connectivity on Service Start-up

First, verify that the service can successfully connect to both database servers using the information present in the connection string as the service starts up:

1. Run the preceding PowerShell script and check for the expected output.
2. Use SSMS to manually force a failover to the other database server.
3. Run the preceding script again and check for the expected output.
4. Use SSMS to manually force a failover back to the original database server.
5. Run the preceding script again and check for the expected output

Failures in the results from the preceding test tend to indicate errors in the configuration of the XenDesktop site or its connectivity to the database servers. Possible errors might include:

Database Connectivity following Database Failover

If the preceding tests show the expected results, verify that the services automatically follow the active database between servers when a database failover is triggered:

1. Run just the Get-BrokerServiceStatus PowerShell commandlet and verify that the output is OK.
2. Use SSMS to manually force a failover to the other database server.
3. Run just the Get-BrokerServiceStatus PowerShell commandlet and verify that the output is OK. It is possible that following a failover triggered in this way that the output might briefly show PendingFailure, and if so, repeat the command. If seen, the PendingFailover state should only persist for a few seconds before OK is reported.
4. Use SSMS to manually force a failover back to the original database server.
5. Repeat the previous check using Get-BrokerServiceStatus.

Assuming the first tests showed that connectivity to both database servers is OK following a service restart, errors in these tests tend to indicate problems in the configuration of the database mirroring itself. For example, attempting to use non-default fixed ports for the database instances can cause the services to successfully failover from the server specified in the connection string as the failover partner to the to one specified as the data source, but not in the other direction.

• The XenDesktop database has not been correctly restored into the mirrored configuration.
• One or both server names are incorrectly specified in the connection string.
• Connectivity problems exist between the controller and one or both database servers.
• The controller logons on one or both database servers are missing or incorrect.

Citrix Documentation - Update database connection strings when using SQL Server high availability solutions