Citrix - Configure XenApp SQL DB Mirroring

To configure XenApp to use a Mirrored database rather than an standalone SQL DB follow the steps below. (Detailed instructions can be found at - https://support.citrix.com/article/CTX127538)

On the Primary SQL Server ensure that the existing Database is configured with the correct collation settings (Latin1_General_CI_AS_KS)

On the Delivery Controller open Power Shell ISE and run:

Add-PSSnapin Citrix* 

Download and install the "Microsoft® ODBC Driver 11 for SQL Server® - Windows"

Download and install the "Microsoft® Command Line Utilities 11 for SQL Server®"


Run the following command to generate the required script, which must be used on the secondary SQL server to create the required logins for the Delivery Controller computer account.

$controllers = Get-BrokerController | %{$_.DNSName}
$mirrorServer = 'NameOfYourMirrorSQLServer'
$dbName = 'CITRIXDBNAME'

foreach ($controller in $controllers) {
    Write-Host "Make login for $controller on $mirrorServer ..."
    Get-BrokerDBSchema –DatabaseName $dbName –ScriptType Login –AdminAddress $controller >> D:\add-login.sql
    sqlcmd -S $mirrorServer -Q ':r add-login.sql'

} 

This will output the following query which you must execute as a query on the secondary SQL server.

-- ============================================================================
--
-- XenDesktop database maintenance script for Citrix Broker Service
-- Action performed: MirrorController
--
-- $Change: 362342 $
-- $DateTime: 2016/02/11 21:59:38 $
-- Copyright (c) Citrix Systems, Inc. All rights reserved.
-- ============================================================================

-- Substitution Parameters:
--  Service Name           : Citrix Broker Service
--    Service Group Name   : <n/a>
--  Database Action        : MirrorController
--    Database Name        : [CITRIXDBNAME]
--    Controller Name      : DOMAIN\DELIVERYCONTROLLER1$
--    Controller SID       : S-5-5-21-204343783-3719578482-54981
--    Database Account     : DOMAIN\DELIVERYCONTROLLER1$
--    Database Account SID : 0x01050000E79EF3BB6DDF24DC5D60000

-- Abort script on error (requires SQLCMD mode).
:on error exit

-- Standard preamble.
set ansi_nulls on;
set quoted_identifier on;
go

-------------------------------------------------------------------------------
-- Create SQL Server login for specified controller (DDC) account.
-------------------------------------------------------------------------------

-- Check is suitable login already exists.
declare @LoginName sysname;
set @LoginName = (select name from sys.server_principals
                   where sid = 0x01050000E79EF3BB6DDF24DC5D60000);

-- Create login if it doesn't already exist.
if @LoginName is null begin
    create login [DOMAIN\DELIVERYCONTROLLER1$] from windows;
    if @@error = 0
        print N'Login [DOMAIN\DELIVERYCONTROLLER1$] for controller [DOMAIN\DELIVERYCONTROLLER1$] created';
    else
        raiserror(N'Failed to create login [DOMAIN\DELIVERYCONTROLLER1$] for controller [DOMAIN\DELIVERYCONTROLLER1$] (login may already exist but with the wrong SID)', 18, 1);
end else
    print N'Login [' + @LoginName + N'] for controller [DOMAIN\DELIVERYCONTROLLER1$] already exists';
go

-- ===============================================
If you have multiple controllers the script output will be duplicated and you'll need to execute for both controllers.
===============================================

Then you need to disconnect the controllers from the standalone DB:

$controllers = Get-BrokerController | %{$_.DNSName}
foreach ($controller in $controllers) {
    Write-Host "Disconnect controller $controller ..."
    Set-ConfigDBConnection –DBConnection $null –AdminAddress $controller
    Set-HypDBConnection –DBConnection $null –AdminAddress $controller
    Set-AcctDBConnection –DBConnection $null –AdminAddress $controller
    Set-ProvDBConnection –DBConnection $null –AdminAddress $controller
    Set-PvsVmDBConnection –DBConnection $null –AdminAddress $controller
    Set-BrokerDBConnection –DBConnection $null –AdminAddress $controller
} 



You must then recreate the connection strings and reconnect the controllers:


$cs = ‘Data Source=PrimarySQLServerName; Failover Partner=SecondarySQLServerName; Initial Catalog=CITRIXDBNAME; Integrated Security=True; Network=dbmssocn’
foreach ($controller in $controllers) {
    Write-Host "Reconnect controller $controller ..."
    Set-ConfigDBConnection –DBConnection $cs –AdminAddress $controller
    Set-HypDBConnection –DBConnection $cs –AdminAddress $controller
    Set-AcctDBConnection –DBConnection $cs –AdminAddress $controller
    Set-ProvDBConnection –DBConnection $cs –AdminAddress $controller
    Set-PvsVmDBConnection –DBConnection $cs –AdminAddress $controller
    Set-BrokerDBConnection –DBConnection $cs –AdminAddress $controller
}