• Changing a Production XenDesktop 7.1 Site to use SQL Mirroring

    February 11, 2014

    PowerShell, XenDesktop

    In a previous article, I changed my lab’s PVS Farm to use SQL database mirroring.  Since that has been done it is time to change my XenDesktop 7.1 Site to also use SQL database mirroring.  I will document the process I found that worked.  It is not all like what is described in CTX127538 How to Reconfigure a XenDesktop Site to Use a Mirrored Database but that article does have some valid information in it. I will also use information gleaned from two Citrix blogs: XD Tipster – SQL Mirroring: Just the Skinny Please… and XenDesktop 7.x Database Migration.  They are helpful but they don’t have 75 screen shots!


    Updated 11-Feb-2014.  A reader emailed me saying they had worked with Citrix support on this very process and gave me some information they felt should be added to this article.  Look for the Updated stuff in-line in the article.

    Updated 13-Feb-2014 to answer questions asked.  Look for the Updated stuff in-line in the article.

    My lab is running two XenDesktop 7.1 servers on Microsoft Windows Server 2012 R2 and three Microsoft SQL Server 2012 SP1 servers also running on Server 2012 R2.

    Update 30-Mar-2016: When I wrote this article, I knew it worked with version 7.1 but never tested it with later versions. Since Citrix has added additional features and services in 7.5, 7.6, 7.7 and 7.8, do not attempt to use this article for anything other than version 7.1. Citrix really should have a thoroughly documented process for this procedure for each version of XenApp/XenDesktop 7.x. Most important, you should never attempt anything in a production environment until you have tested it in a lab or test environment.  I have changed the title of this article from 7.x to 7.1.

    Note: The Witness SQL server can run SQL Server Express but I already had full SQL Server installed on three servers so I decided to use full SQL Server and not SQL Server Express for the Witness server.

    When I created my Site, I allowed Studio to create and configure the Site database.  The Site configuration, Configuration Logging and Monitoring are all using the same database.  eDocs say if database mirroring is configured first, when the secondary databases are moved, Studio can automatically create the new databases AND configure mirroring.  We will see if that is true.

    I will be using my Controller XD71Studio1 (Studio1) to run all the PowerShell scripts.

    The first thing to do is to install SQLCMD or the SQL Command Line Utilities.  This is needed to give the Controllers permissions to the databases

    Because I am using SQL Server 2012 SP1, I had to download the SQL Server 2012 SP1 Feature Pack.  From the download page I selected ENU\x64\SqlCmdLnUtils.msi which required that ENU\x64\sqlncli.msi (SQL Server Native Client) be installed first.  Installing the SQL Server Native Client may require a server restart.

    I created a security group named SQLServers that has all three SQL Servers as members.  This security group will be used when mirroring is configured later using a wizard in SQL Server.

    SQLServer security group
    SQLServer security group

    The members of the SQLServers security group.

    SQLServers members
    SQLServers members

    The Site configuration as shown in Studio show I am not configured for database mirroring.

    Site database configuration
    Site database configuration

    The first thing that must be done is to change the Recovery model of the database that Studio created automatically.  Studio created the database using the Simple Recovery model but mirroring requires the use of the Full Recovery model.  Please see Prerequisites, Restrictions, and Recommendations for Database Mirroring.

    “The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database.”

    On the SQL Server that has the Site database, open the SQL Server Management Studio, expand Databases, right-click the Site database, click Properties and click Options in the left pane.

    Simple Recovery Model
    Simple Recovery Model

    Change the Recovery model to Full and click OK.

    Change Recovery Model to Full
    Change Recovery Model to Full

    Eight XenDesktop services in the site must be disconnected from the current database by running the following PowerShell script.

    Add-PSSnapin Citrix*
    
    $controllers = Get-BrokerController | %{$_.DNSName}
    
    foreach ($controller in $controllers)
    {
        Write-Host "Disconnect controller $controller ..."
    
        Set-SfDBConnection $null –AdminAddress $controller
        Set-AcctDBConnection $null –AdminAddress $controller
        Set-BrokerDBConnection $null –AdminAddress $controller
        Set-ConfigDBConnection $null –AdminAddress $controller
        Set-AdminDBConnection $null –AdminAddress $controller
        Set-EnvtestDBConnection $null –AdminAddress $controller
        Set-HypDBConnection $null –AdminAddress $controller
        Set-ProvDBConnection $null –AdminAddress $controller
    }
    

    Updated: The reader said Citrix support had them run these commands to disconnect services.

    $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
    }
    

    Leave that PowerShell session running.

    Now to start configuring the database to be mirrored.

    Next step is to do two backups of the Site database.  One for the database and one for the transaction logs.

    Still in the Databases node of the SQL Server Management Studio, right-click the Site database, click Tasks and click Back Up.

    Database Backup Menu
    Database Backup Menu

    Leave the Backup type as Full and click OK.

    Backup Type Full
    Backup Type Full

    Click OK.

    Full database backup completed
    Full database backup completed

    Repeat the process for the second backup.  Right-click the Site database, click Tasks and click Back Up.

    Database Backup Menu
    Database Backup Menu

    Change the Backup type to Transaction Log and click OK.

    Transaction Log Backup
    Transaction Log Backup

    Click OK.

    Transaction log backup completed
    Transaction log backup completed

    The backup on the primary SQL server needs to be copied to the same location on what will be the mirror SQL server.  For my lab, XD71SQL1 is the primary and XD71SQL2 is the mirror.

    What I did is on SQL1 open up Windows explorer to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup and also open up an Explorer window to \\xd71sql2\c$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup.  I then copied and pasted the backup file from SQL1 to SQL2.

    Backup file copied from SQL1 to SQL2
    Backup file copied from SQL1 to SQL2

    Exit both explorer windows.

    Either go to SQL2 or add SQL2 to the SQL Server Management Studio on SQL1 (which is what I did).

    Note: Please make sure your SQL Servers are running the same version or you will run into issues.  Don’t ask me how I found that out. 🙂

    SQL2 Server added to Management Studio
    SQL2 Server added to Management Studio

    For the mirror server connection (SQL2 for me), right-click Databases and click Restore Database.

    Restore Database on mirror server
    Restore Database on mirror server

    Change Source to Device and click the browse button.

    Restore step 1
    Restore step 1

    Browse to the backup file copied from the primary SQL server, click on the backup file and click OK.

    Restore step 2
    Restore step 2

    Click OK.

    Restore step 3
    Restore step 3

    Make sure the Database is your Site database and both Backup sets to restore are selected.  DO NOT click OK at this time.

    Restore step 4
    Restore step 4

    Click Options in the left pane, change the Recovery state to RESTORE WITH NORECOVERY and click OK.

    Restore step 5
    Restore step 5

    Click OK.

    Restore step 6
    Restore step 6

    Expand Databases on the mirror server.  The Site database should say “(Restoring…)“.

    Mirror database "Restoring"
    Mirror database “Restoring”

    On the principal server, expand Databases, right-click the Site database, click Tasks and click Mirror.

    Mirror step 1
    Mirror step 1

    Click Configure Security.

    Mirror step 2
    Mirror step 2

    Click Next.

    Mirror step 3

    Verify Yes is selected and click Next.

    Mirror step 4

    Verify Witness server instance is selected and click Next.

    Mirror step 5

    Click Next

    Mirror step 6

    Select the mirror server from the Mirror server instance dropdown and click Connect.

    Mirror step 7

    Click Connect to authenticate and connect to the mirror server.

    Mirror step 8

    Click Next.

    Mirror step 9

    Select the witness server from the Witness server instance dropdown and click Connect.

    Mirror step 10

    Click Connect to authenticate and connect to the witness server.

    Mirror step 11

    Click Next.

    Mirror step 12

    Enter the SQLServers security account for all three instances and click Next.

    Mirror step 13

    Click Finish.

    Mirror step 14

    If all Action columns have a status of success, click Close.  Otherwise, view the report generated, resolve the issues reported and rerun the Configure Security wizard.

    Mirror step 15

    Click Start Mirroring (and pray that everything works).

    Mirror step 16

    WHEW, it worked.  Click OK.

    Mirror step 17
    Mirror step 17

    In the SQL Server Management Studio, refresh the primary and mirror servers.  Expand Databases on both servers.  The principal server should show the Site database as (Principal, Synchronized) and the mirror server should show the Site database as (Mirror, Synchronized / Restoring…).

    Site database status after starting mirroring
    Site database status after starting mirroring

    Now do a basic test of the failover before continuing.

    Go back to the SQL Server Management Studio.  On the principal server, expand Databases, right-click the Site database, click Tasks and click Mirror.

    Test mirror 1
    Test mirror 1

    Click the Failover button.

    Test mirror 2
    Test mirror 2

    Click Yes.

    Test mirror 3

    Refresh both the principal and mirror servers, expand Databases and look at the database status.

    The principal server now shows (Mirror, Synchronized / Restoring…) and the mirror server now shows (Principal, Synchronized).

    Test mirror 4
    Test mirror 4

    Updated 13-Feb-2014:  I have added some explanations and more PowerShell to this section on adding logon.

    Click the database on the mirror server and failover back to the principal server.  Don’t failover back to the principal just yet.  Let me show you something.

    Why are logons needed?  When Studio created the Site database it added the Controllers computer account as a login account on the SQL server.  When others Controllers are added to the Site, Studio asks if you want the Site database updated.  If the answer is Yes then Studio adds the new Controller as a login account on the SQL server.

    Here is my principal SQL server’s login accounts after both Controllers are in the Site.

    Principal SQL server login accounts
    Principal SQL server login accounts

    Here is my mirror SQL server’s login accounts after the database is restored, mirroring configured and failed over.

    Mirror SQL server's initial login accounts
    Mirror SQL server’s initial login accounts

    What does this mean?  While the database is failed over to the mirror server and the Site database is accessed, neither Controller has permissions to access the database on the mirror server so nothing will work.  We can test this by running Get-BrokerController from the PowerShell session.

    Get-BrokerController from mirror server before login accounts created
    Get-BrokerController from mirror server before login accounts created

    Fail back to the principal server, wait about 1 minute and run Get-BrokerController again.

    Principal server works
    Principal server works

    The command works now because the principal SQL server has login accounts for both controllers.

    Do not fail back over to the mirror server yet.

    Next logons need to be created on the new database server.

    Back on the Controller, run the following script (substituting your information).

    $mirrorServer = 'XD71SQL2'
    $dbName = 'XD71Site'
    $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 $mirrorServer -Q ':r add-login.sql'
    }
    
    Login accounts created on the mirror server
    Login accounts created on the mirror server

    The new logins now exist on the mirror server.

    New login accounts on the mirror server
    New login accounts on the mirror server

    Now failover to the mirror server, wait 1 minute and run Get-BrokerController.

    Get-BrokerController now works when failed over to mirror
    Get-BrokerController now works when failed over to mirror

    A new connection string is needed to configure XenDesktop for mirroring.

    ‘Server=<dbserver1>; Failover Partner=<dbserver2>; Initial Catalog=<dbname>; Integrated Security=True; Network=dbmssocn’

    For my lab, that becomes ‘Server=XD71SQL1; Failover Partner=XD71SQL2; Initial Catalog=XD71Site; Integrated Security=True; Network=dbmssocn’

    To test the new connection string and verify login permissions, run the following script.

    $cs = "Data Source=XD71SQL1; Failover Partner=XD71SQL2; Initial Catalog=XD71Site; 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
    }
    
    Testing new connection string
    Testing new connection string

    Then this script is run.

    $cs = ‘Server=XD71SQL1; Failover Partner=XD71SQL2; Initial Catalog=XD71Site; Integrated Security=True; Network=dbmssocn’
    
    foreach ($controller in $controllers)
    {
    Write-Host "Reconnect controller $controller ..."
    
        Set-SfDBConnection –DBConnection $cs –AdminAddress $controller
        Set-AcctDBConnection –DBConnection $cs –AdminAddress $controller
        Set-BrokerDBConnection –DBConnection $cs –AdminAddress $controller
        Set-ConfigDBConnection –DBConnection $cs –AdminAddress $controller
        Set-AdminDBConnection –DBConnection $cs –AdminAddress $controller
        Set-EnvtestDBConnection –DBConnection $cs –AdminAddress $controller
        Set-HypDBConnection –DBConnection $cs –AdminAddress $controller
        Set-ProvDBConnection –DBConnection $cs –AdminAddress $controller
    }
    

    Now for the final services.

    foreach ($controller in $controllers)
    {
        Write-Host "Processing controller $controller ..."
    
        Set-LogDBConnection $null –AdminAddress $controller
        Set-MonitorDBConnection -DBConnection $null –AdminAddress $controller
    
        Set-LogDBConnection –DBConnection $cs –AdminAddress $controller
        Set-MonitorDBConnection –DBConnection $cs –AdminAddress $controller
    
        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
    }
    

    Update: The reader says Citrix Support did not do this in two separate steps but in one. This is what they said Citrix Support had them run.

    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
    }
    

    They also said that if there are any errors, go to HKLM\Software\Citrix\XDservices\\Datastore\Connections and manually clear the connection string that is in the registry (insert the usual editing the registry may hose your server disclaimer).

    Note from Webster: That did not work for me. I had to use the two step process. Your milage may vary but I wanted to let you know what the reader said Citrix Support had them do and it worked for them.

    Is everything still working?  Rerun the testing script.

    $cs = "Data Source=XD71SQL1; Failover Partner=XD71SQL2; Initial Catalog=XD71Site; 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
    }
    
    Everything still looks good
    Everything still looks good

    Yep, everything tests as OK.

    A refresh of Studio shows the database now configured for mirroring.

    Studio showing mirroring
    Studio showing mirroring

    Now for the next part, splitting the databases and letting Studio create the Logging and Monitoring databases.

    According to eDocs:

    “If you previously installed and configured a mirror database using Microsoft SQL Server tools, both the principle and mirror databases are set up.”

    We shall see if that is true.

    In Studio. click the Logging database and in the right pane click Change Database.

    Change Logging database
    Change Logging database

    Enter a name for the Logging database and click OK.

    Name for Logging database
    Name for Logging database

    Click OK for Studio to create the database.

    Click OK
    Click OK

    After the Logging database is created, Studio refreshes and we see that the mirror configuration does not appear to have been created.

    Where's the mirror?
    Where’s the mirror?

    Go to SQL Server Management Studio, Refresh  and expand Databases for both the primary and mirror servers.

    Looking at the new Logging database
    Looking at the new Logging database

    Do you see a Logging database on the mirror server?  I don’t.   It appears eDocs is incorrect and “both the principle and mirror databases are [NOT] set up”.

    Let’s go ahead and create the Monitoring database using the same process.

    And no surprise now, no Monitoring database mirror configuration…

    Where's the mirror, Part 2
    Where’s the mirror, Part 2

    and no Monitoring database created on the mirror server.

    Looking at the Monitoring database
    Looking at the Monitoring database

    Using the process shown earlier, change the Recovery Model to Full, backup the Logging and Monitoring databases, copy the files and restore them on the mirror server.

    Logging and Monitoring databases restored on mirror server
    Logging and Monitoring databases restored on mirror server

    Using the process shown earlier, configure both the Logging and Monitoring databases for Mirroring.

    Logging and Monitoring databases configured for Mirroring
    Logging and Monitoring databases configured for Mirroring

    Update:  Oops, copy and paste error in the original PowerShell code here.  Two new connection strings are needed.  One for the Logging database and one for the Monitoring database.

    Now run this script.

    $csL =  "Data Source=XD71SQL1; Failover Partner=XD71SQL2; Initial Catalog=XD71_Logging; Integrated Security=True; Network=dbmssocn"
    $csM =  "Data Source=XD71SQL1; Failover Partner=XD71SQL2; Initial Catalog=XD71_Monitoring; Integrated Security=True; Network=dbmssocn"
    foreach ($controller in $controllers)
    {
        Write-Host "Processing controller $controller ..."
    
        Set-LogDBConnection $null –AdminAddress $controller
        Set-MonitorDBConnection -DBConnection $null –AdminAddress $controller
    
        Set-LogDBConnection –DBConnection $csL –AdminAddress $controller
        Set-MonitorDBConnection –DBConnection $csM –AdminAddress $controller
    
        Set-MonitorDBConnection -Datastore Monitor -DBConnection $null –AdminAddress $controller
        Set-LogDBConnection -DataStore Logging -DBConnection $null –AdminAddress $controller
    
        Set-MonitorDBConnection -Datastore Monitor –DBConnection $csM –AdminAddress $controller
        Set-LogDBConnection -DataStore Logging –DBConnection $csL –AdminAddress $controller
    }
    

    Refresh Studio and all three databases show as configured for mirroring.

    All three databases configured for mirroring
    All three databases configured for mirroring

    CTX127538 shows how to test if the mirroring configuration is successful.

    Run the following script:

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

    And verify the output matches the following:

    DBUnconfigured
    OK
    OK

    First verification
    First verification

    Using the SQL Server Management Studio, force a mirror failover of all three databases using the process shown earlier.

    First failover
    First failover

    Rerun the preceding script and verify the output is correct.

    Second verification
    Second verification

    Failover all three databases again.

    Second failover
    Second failover

    Rerun the preceding script again and verify the expected output.

    Third verification
    Third verification

    Whew!,  and that is all there is to changing a production XenDesktop Site to use SQL Mirroring in less than 75 screen shots.

    Thanks

    Webster

    ,

    About Carl Webster

    Webster is a Sr. Solutions Architect for Choice Solutions, LLC and specializes in Citrix, Active Directory and Technical Documentation. Webster has been working with Citrix products for many years starting with Multi-User OS/2 in 1990.

    View all posts by Carl Webster

    49 Responses to “Changing a Production XenDesktop 7.1 Site to use SQL Mirroring”

    1. Steven Cochrane Says:

      I know this thread is from 2015, etc Similar to the post from Goran Jozic

      I was planning to do cutover from a standalone SQL server to new SQL servers that would represent the three site DB’s.

      The only difference is that your post was including the standalone that that ALSO became the Primary in the Mirror.

      What I don’t have any record of is copying the backup files to the two new sql servers that were not part of the site previously. If I had to take a guess this process could also still be completed? That wouldn’t require a new site to be created would it? For just copying the DB to new Primary server name?

      BTW – Your post is fantastic. I saved it for reference.

      Reply

      • Carl Webster Says:

        Just use File Explorer (or similar) to just copy the backup files to the other server(s). It is as easy as that.

        Webster

        Reply

    2. François Gagné Says:

      Hi Carl,
      terrific post !

      I’have maybe found two errors In the last part of your post when you configure the logging Db and monitoring Db,
      line7 in the script “–DBConnection” is missing.
      and
      line10 and 11
      in my opinion you should specify the Site Db instead the logging and monitoring db because without Datastore parameter the default datastore is Site.

      for example “Set-LogDBConnection –DBConnection $xxx” is the same as “Set-LogDBConnection -datastore Site –DBConnection $xxx”

      So I think this is the best usage:
      Set-LogDBConnection –DBConnection $cs –AdminAddress $controller
      Set-MonitorDBConnection –DBConnection $cs –AdminAddress $controller

      I also add Set-LogSite commandlet to disable and enable logging before and after performing Set-LogDBConnection.

      Set-LogSite -State Disabled –AdminAddress $controller
      Set-LogDBConnection blabla blabla
      Set-LogSite -State Enabled –AdminAddress $controller

      Anyway your post is amazing, and it helped me to understand a lot of things.

      keep going

      François

      Reply

    3. Datta Says:

      Your in depth technical information with a simple way to make any citrix admin understand the functionality, hatsoff to you carl. By referring the articles published, i have setup the lab environment and also have made our production environment more robust and highly available.

      Keep continuing the good work, You are making many more Citrix admins through out the world. The best Mentor and teacher

      Reply

    4. Knight Says:

      In my case I am migrating from a standalone SQL Server to Always On Availability Group

      What steps and additional considerations are required for ensuring a smooth migration?
      So far I have:
      1. DDC Accounts on SQL (Need to Match)
      2. When re-conecting use the Listener´s name.

      Thanks

      Reply

      • Carl Webster Says:

        The AlwaysOn Availability Group Listener is the magic key. That is what you enter for what my article calls the mirror server. Are you really still on 7.1? This article is not applicable to any version after 7.1.

        Webster

        Reply

    5. Fred Says:

      This page has been very useful in helping me create the mirroring. My one problem that I am still encountering though is when failing over to the 2nd SQL Server, I still am not able to run the command “Get-BrokerController” which gives the PermissionDenied error…even though both logins have been added to the mirrored server and 5 minutes passed before I even attempt to run the command.

      Reply

      • Carl Webster Says:

        I am not a SQL guy so I have no help for you. In all the mirror setups I have done, they all failed over and were ready for use in less than one minute.

        Webster

        Reply

    6. Kyle Woodbury Says:

      We used this last night to attempt a 7.6 DB mirror, and it corrupted our DDCs due to the following from CTX escalation:
      If the db strings on a 7.x broker are nulled out in the wrong order, there is a high probability, and looks like was the case here based on the DAS errors you experienced, you can lock yourself out of the farm and fall into a outage situation. http://support.citrix.com/article/CTX140319 outlines the appropriate process correctly for 7.6, there are several other blog posts that discuss a similar process but instructions are incomplete, this is because of the versions of XD they were written for, even through the title says 7.x, if the body of the document was written for versions earlier than 7.5/7.6 then they did not account for the analytics/Delegated admin services and these are the services that get our users into trouble.

      Reply

    7. Luciano Says:

      Great post, do you have a similar procedure but for XenApp 6.5 and 7.5?
      I’m looking for a procedure to migrate the datastores of 6.5 and 7.5 (currently on a single sql 2008 server) to a SQL 2014 cluster.
      Thanks.

      Reply

    8. Michael Dyer Says:

      Hi Carl

      I got a warning message during the mirroring process for the security group used for the Service account portion of the mirror configuration. “SQL Server cannot create domain\sqlserver”. (Microsoft. SqlServer.Smo)

      The mirror worked just want to ensure its been configured correctly.

      All three servers are members of the SQL Server security group. Do this group need rights on the database server? Did I miss a step

      Thanks again for your time.

      Reply

    9. Salim Says:

      Hi Carl,

      Great Article…

      All above steps i have completed SQL Mirroring

      When I failback to principal server and when I go back to DDC1 in opened Powershell Get-BrokerController command fails and gives me below error

      PS C:\Users\administrator.SKLAB> Get-BrokerController
      Get-BrokerController : Insufficient administrative privilege
      At line:1 char:1
      + Get-BrokerController
      + ~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : PermissionDenied: (:) [Get-BrokerController], SdkOperationException
      + FullyQualifiedErrorId : Citrix.XDPowerShell.Broker.AccessDenied,Citrix.Broker.Admin.SDK.GetBrokerControllerComma
      nd

      PLease help me in abve ….

      Reply

      • Carl Webster Says:

        That is explained in the article. Do you have the computer accounts added to both SQL servers? Did you wait long enough for the failover to settle down?

        Thanks

        Webster

        Reply

    10. Goran Jozic Says:

      Hello Carl,
      Wonderful post.
      We have XD 7.6 farm and rather than creating new farm from scratch I just used your steps and moved POC to production. (with few other additional steps 🙂 )
      I just wanted to share one thing that worked for me.

      When it came to splitting off Monitoring and Logging DG from Site DB I did things little bit differently.
      I first created LOGDB and MONITORDB manually (with proper collation and recovery model) and configured mirroring. Then went to Studio and pointed Monitoring and Logging DB to those two new databases I had just created. After few seconds Studio recognized both: new DBs and mirrored instances.

      Again, thank you very much for all your contributions to the community.

      Reply

    11. Magnus Says:

      Hey Carl,
      As usual, great post. I am trying to write a health check script that would run either before the business day start or late Sunday night for a “Start of Business Week” check using some of the “test-xx” commands that you have above. Most of them seem to not exist with XD7.6 SDK that I have installed on my workstation (or scheduling server). Am I missing an SDK some where?

      Magnus

      Reply

      • Carl Webster Says:

        There are there for me:

        PS C:\MigrationScript> get-command test-* -module citrix*

        CommandType Name ModuleName
        ———– —- ———-
        Cmdlet Test-AcctDBConnection Citrix.AdIdentity.Admin.V2
        Cmdlet Test-AcctIdentityPoolNameAvailable Citrix.AdIdentity.Admin.V2
        Cmdlet Test-AdminAccess Citrix.DelegatedAdmin.Admin.V1
        Cmdlet Test-AdminDBConnection Citrix.DelegatedAdmin.Admin.V1
        Cmdlet Test-AnalyticsDBConnection Citrix.Analytics.Admin.V1
        Cmdlet Test-BrokerAccessPolicyRuleNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerAppAssignmentPolicyRuleNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerAppEntitlementPolicyRuleNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerApplicationNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerAssignmentPolicyRuleNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerCatalogNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerDBConnection Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerDesktopGroupNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerEntitlementPolicyRuleNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerLicenseServer Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerMachineNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerPowerTimeSchemeNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-BrokerRemotePCAccountNameAvailable Citrix.Broker.Admin.V2
        Cmdlet Test-ConfigDBConnection Citrix.Configuration.Admin.V2
        Cmdlet Test-ConfigServiceInstanceAvailability Citrix.Configuration.Admin.V2
        Cmdlet Test-CtxAppVServer Citrix.AppV.Admin.V1
        Cmdlet Test-EnvTestDBConnection Citrix.EnvTest.Admin.V1
        Cmdlet Test-HypDBConnection Citrix.Host.Admin.V2
        Cmdlet Test-HypHostingUnitNameAvailable Citrix.Host.Admin.V2
        Cmdlet Test-HypHypervisorConnectionNameAvailable Citrix.Host.Admin.V2
        Cmdlet Test-LogDBConnection Citrix.ConfigurationLogging.Admin.V1
        Cmdlet Test-MonitorDBConnection Citrix.Monitor.Admin.V1
        Cmdlet Test-ProvDBConnection Citrix.MachineCreation.Admin.V2
        Cmdlet Test-ProvSchemeNameAvailable Citrix.MachineCreation.Admin.V2
        Cmdlet Test-SfDBConnection Citrix.Storefront.Admin.V1
        Cmdlet Test-UserProfileShare Citrix.UserProfileManager.Admin.V1

        Are you loading all the snapins?

        add-pssnapin *citrix*

        Webster

        Reply

    12. Mike Soucie Says:

      Why do you split the logging and monitoring databases off from the main db?

      Reply

    13. Mike Soucie Says:

      Thanks for the help yesterday, but the situation was not working out and I decided to start from scratch and got rid of the previous Citrix databases.
      Then I created a delivery controller and machine group with one server.

      When disconnecting the Xendesktop services, I get the following error when it tries to execute the SET-ADMINDBCONNECTION command:

      Set-AdminDBConnection : The operation could not be performed because of a configuration logging error.
      At line:16 char:26
      + Set-AdminDBConnection <<<< -DBConnection $null -AdminAddress $Controller
      + CategoryInfo : InvalidOperation: (:) [Set-AdminDBConnection], InvalidOperationException
      + FullyQualifiedErrorId : Citrix.XDPowerShell.Status.ConfigurationLoggingError,Citrix.DelegatedAdmin.Sdk.Cmdlets.D
      ataStore.Commands.SetAdminDBConnectionCommand

      Any advice?

      Reply

      • Mike Soucie Says:

        I found a registry fix for this problem.

        Reply

        • Carl Webster Says:

          What was it?

          Sorry for the delay. Been at two conferences this week.

          Webster

          Reply

        • Andy Says:

          I think he was talking about this:

          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.

          I found it here: http://support.citrix.com/article/CTX139874

          Reply

    14. Mike Soucie Says:

      When I try to run the PS commands to create the logins on the mirrorer sql server:
      Get-BrokerDBSchema –DatabaseName $dbName –ScriptType Login –AdminAddress $controller > add-login.sql
      sqlcmd -S $mirrorServer -Q ‘:r add-login.sql’

      I get the following error:
      The term ‘sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spel
      ling of the name, or if a path was included, verify that the path is correct and try again.

      Reply

      • Carl Webster Says:

        use the full path to sqlcmd.

        Webster

        Reply

        • Mike Soucie Says:

          Thanks, but now when I execute this:

          >> Get-BrokerDBSchema -DatabaseName $dbName -ScriptType Login -AdminAddress $controller > add-login.sql
          >> “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd” -S $mirrorServer -Q ‘:r add-login.sql’

          I get this error:

          You must provide a value expression on the right-hand side of the ‘-‘ operator.

          Sorry, but I’m new to PS.

          Reply

          • Carl Webster Says:

            Try:

            Get-BrokerDBSchema -DatabaseName $dbName -ScriptType Login -AdminAddress $controller > add-login.sql
            “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd -S $mirrorServer -Q ‘:r add-login.sql’”

            Reply

    15. Carlos Beltran Says:

      I tried to install a fresh install with DB in mirror but always get errors like SID conflicts for Computer logins… I dont know why I’m getting this if this is a clean environment… the only way that works is installing a DB with not mirroring…..

      please help….

      thanks
      JC

      Reply

      • Carl Webster Says:

        The ONLY way to install fresh using existing mirror databases is by using PowerShell. I started writing an article many months ago on the process but never got the time to finish it.

        Webster

        Reply

      • Victor McBride Says:

        Carl, you are awesome.
        Your posts are a dream.
        I have it all working except for one scenario.
        When I disconnect the nic of the Principal, to simulate a full failure, the controller will not fail over. Studio fails etc. netstat -na shows syn_sent stuck ports to Principal.

        Any thoughts?

        Reply

        • Carl Webster Says:

          https://msdn.microsoft.com/en-us/library/ms190913.aspx
          Possible Failures During Database Mirroring

          “Some components, such as network components and some IO subsystems, have their own time-outs to determine failures. Such time-outs are independent of database mirroring, which has no knowledge of them and is completely unaware of their behavior. In these cases, the time-out delay increases the time between a failure and when database mirroring receive the resulting hard error.”

          Hope that helps.

          Webster

          Reply

        • Victor McBride Says:

          Thanks Carl,
          But…
          During my test, the database fails over properly, but the controller
          won’t flip to the Mrror. It keeps trying the Principal.
          Have you tested if the flip over actually happens by the controller, under these circumstances, for xa7.6?
          Thanks,
          Victor

          Reply

          • Carl Webster Says:

            No I never tested that scenario. I don’t have mirror setup in my lab right now, trying to get the XD7.x documentation script finished.

            Webster

            Reply

        • Victor McBride Says:

          Cool, Thanks Carl. I’m calling Citrix on this one.
          Let me know if you find anything, and I will post the same here.
          Victor

          Reply

    16. Carlos Beltran Says:

      Hello Carl,
      I have an environment XenApp7.6, My question is if these steps are the same for 7.6 version.

      I have create a .ps1 file with each step to run in powershell… I’m correct with this?

      please let me know your views

      Thanks
      JC

      Reply

      • Carl Webster Says:

        No, I don’t believe this is correct for 7.6. IIRC, Citrix changed the cmdlets to not require a specific order. I have not seen an article from Citrix on the changes necessary for 7.6 yet.

        Webster

        Reply

    17. Marcel Otto Says:

      Hi Carl,

      just in this Moment I found your great documentation and try to configure my Lab as you explained it.

      But I have one question and did not understand one step:
      1. After creating my database I can run “get-brokercontroller” – works fine
      2. I disconnect the database from my Delivery Controller with your script – Works fine
      3. I configure my Failover – works fine
      4. But I cannot run this script, cause “get-brokercontroller” doesn`t work when database is diconnected:

      $mirrorServer = ‘XD71SQL2’
      $dbName = ‘XD71Site’
      $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 $mirrorServer -Q ‘:r add-login.sql’

      Reply

      • Carl Webster Says:

        That is explained in the article.

        When you first run the command to get the $Controllers, I say to leave that PowerShell session open. i.e. Don’t close it. Why?

        Later on I explain:

        “What does this mean? While the database is failed over to the mirror server and the Site database is accessed, neither Controller has permissions to access the database on the mirror server so nothing will work. We can test this by running Get-BrokerController from the PowerShell session.”

        Which shows an image on the Get-BrokerController cmdlet failing.

        I also state that any time you failover, you have to wait a minute or so for the SQL Server to do the failover before you can run Get-BrokerController successfully.

        Thanks

        Webster

        Reply

    18. Dave H Says:

      Great post Carl,

      However I’ve hit a problem when I try to do any type of query on the Monitoring database and it’s connection (MonitorDBConnection).
      I’m receiving an connection error of ‘An invalid URL was given for the service. Failed to connect to the back-end server on port 80 using binding WSHttp’.
      As far as I can determine the monitoring service is up and running, so I’m wondering if we have not configured the service correct in the first place.
      Have you come across this error ?

      Thanks

      Reply

      • Carl Webster Says:

        I receive the same error several times using Excel but it eventually connects and I get my data. If I use LINQPad 4, I never get that error.

        Webster

        Reply

    19. Rui Ribeiro Says:

      Thanks for the insight Carl,

      My Scenario is a bit diferente, i have a Always On Availability Group, and Citrix is pointing to the SQL SERVER A, and Server A is doing a synchronous replication to SQL SERVER B. After some testing with a new lab i ahve managed to get the listner working and using the lister´s name to do the connectivity, this is not a MIRROR per say Citrix is still connected to 1 SQL NAME connection but failover does existe, if ia failover to the SQL SERVER B all still works.

      My situation now is, i need to replicate this configuration into the production environment and the Studio is also on a diferente vm group also in cluster mode does this change avaeryhintg?

      So looking at your doc, i need think i just need to concern my self with:

      1. DDC Accounts on SQL (Need to Match)
      2. When re-conecting use the Listener´s name.
      3. teste and try failover

      Is this somewhat correct? 🙂

      Reply

      • Rui Ribeiro Says:

        Carl

        Just to let you know that all went well, the only thing is the unconfigure script worked well on my configuration but the re-connect script could not do anything i had to go to each of my 3 DDC and run individually the re-connect lines.

        But i have now a fully redundant XEN7 infra with AAG running on Windows 2012, thanks to your blog all other 3 links you posted… all of them helped, if you want screen shots i am glad to send them.

        Cheers

        Reply

        • Rui Ribeiro Says:

          My Config is:

          2 x Gateway
          3 x DDC Poiting to the SQL Listner
          2 x SQL with a Listner and AAG configured Sync and Automatic Failover
          1 x Lic Server
          1 x Netscaler

          Supporting only 250 Xenapp Sessions and 60 Desktops for now… we are small on this parts 🙂 not like you guys with thousands…

          Just wanted to share.
          Cheers

          Reply

    20. sandeep kaushik Says:

      Hi How can i change Site database like if i configured my site with DB1 database and i want to configure my Site with DB2 then what is the command i use here.

      Reply

    21. Will Says:

      An absolutely phenomenal write-up with necessary screenshots. If only all documentation was this well written!

      Reply

    Leave a Reply