Carl Webster Accessibility Statement

Carl Webster is committed to facilitating the accessibility and usability of its website, carlwebster.com, for everyone. Carl Webster aims to comply with all applicable standards, including the World Wide Web Consortium’s Web Content Accessibility Guidelines 2.0 up to Level AA (WCAG 2.0 AA). Carl Webster is proud of the efforts that we have completed and that are in-progress to ensure that our website is accessible to everyone.

If you experience any difficulty in accessing any part of this website, please feel free to email us at info@carlwebster.com and we will work with you to provide the information or service you seek through an alternate communication method that is accessible for you consistent with applicable law (for example, through telephone support).

  • Changing a Production XenDesktop 7.1 Site to use SQL Mirroring

    February 11, 2014

    PowerShell, XenApp/XenDesktop 7.0 - 7.7

    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.

    Figure 1
    Figure 1

    The members of the SQLServers security group.

    Figure 2
    Figure 2

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

    Figure 3
    Figure 3

    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.

    Figure 4
    Figure 4

    Change the Recovery model to Full and click OK.

    Figure 5
    Figure 5

    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.

    The 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.

    Figure 6
    Figure 6

    Leave the Backup type as Full and click OK.

    Figure 7
    Figure 7

    Click OK.

    Figure 8
    Figure 8

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

    Figure 9
    Figure 9

    Change the Backup type to Transaction Log and click OK.

    Figure 10
    Figure 10

    Click OK.

    Figure 11
    Figure 11

    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.

    Figure 12
    Figure 12

    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. 🙂

    Figure 13
    Figure 13

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

    Figure 14
    Figure 14

    Change Source to Device and click the browse button.

    Figure 15
    Figure 15

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

    Figure 16
    Figure 16

    Click OK.

    Figure 17
    Figure 17

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

    Figure 18
    Figure 18

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

    Figure 19
    Figure 19

    Click OK.

    Figure 20
    Figure 20

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

    Figure 21
    Figure 21

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

    Figure 22
    Figure 22

    Click Configure Security.

    Figure 23
    Figure 23

    Click Next.

     

    Figure 24
    Figure 24

    Verify Yes is selected and click Next.

     

    Figure 25
    Figure 25

    Verify Witness server instance is selected and click Next.

     

    Figure 26
    Figure 26

    Click Next

     

    Figure 27
    Figure 27

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

     

    Figure 28
    Figure 28

    Click Connect to authenticate and connect to the mirror server.

     

    Figure 29
    Figure 29

    Click Next.

     

    Figure 30
    Figure 30

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

     

    Figure 31
    Figure 31

    Click Connect to authenticate and connect to the witness server.

     

    Figure 32
    Figure 32

    Click Next.

     

    Figure 33
    Figure 33

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

     

    Figure 34
    Figure 34

    Click Finish.

     

    Figure 35
    Figure 35

    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.

     

    Figure 36
    Figure 36

    Click Start Mirroring (and pray that everything works).

     

    Figure 37
    Figure 37

    WHEW, it worked.  Click OK.

    Figure 38
    Figure 38

    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…).

    Figure 39
    Figure 39

    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.

    Figure 40
    Figure 40

    Click the Failover button.

    Figure 41
    Figure 41

    Click Yes.

     

    Figure 42
    Figure 42

    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).

    Figure 43
    Figure 43

    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.

    Figure 44
    Figure 44

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

    Figure 45
    Figure 45

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

    Figure 46
    Figure 46

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

    Figure 47
    Figure 47

    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'
}
    Figure 48
    Figure 48

    Login accounts created on the mirror server

    The new logins now exist on the mirror server.

    Figure 49
    Figure 49

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

    Figure 50
    Figure 50

    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
}

     

    Figure 51
    Figure 51

    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&nbsp;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 mileage 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
}
    Figure 52
    Figure 52

    Yep, everything tests as OK.

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

    Figure 53
    Figure 53

    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 principal 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.

    Figure 54
    Figure 54

    Enter a name for the Logging database and click OK.

    Figure 55
    Figure 55

    Click OK for Studio to create the database.

    Figure 56
    Figure 56

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

    Figure 57
    Figure 57

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

    Figure 58
    Figure 58

    Do you see a Logging database on the mirror server?  I don’t.   It appears eDocs is incorrect and “both the principal 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…

    Figure 59
    Figure 59

    and no Monitoring database was created on the mirror server.

    Figure 60
    Figure 60

    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.

    Figure 61
    Figure 61

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

    Figure 62
    Figure 62

    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 = &nbsp;"Data Source=XD71SQL1; Failover Partner=XD71SQL2; Initial Catalog=XD71_Logging; Integrated Security=True; Network=dbmssocn"
$csM = &nbsp;"Data Source=XD71SQL1; Failover Partner=XD71SQL2; Initial Catalog=XD71_Monitoring; Integrated Security=True; Network=dbmssocn"
foreach ($controller in $controllers)
{
    Write-Host "Processing&nbsp;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.

    Figure 63
    Figure 63

    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

    Figure 64
    Figure 64

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

    Figure 65
    Figure 65

    Rerun the preceding script and verify the output is correct.

    Figure 66
    Figure 66

    Failover all three databases again.

    Figure 67
    Figure 67

    Rerun the preceding script again and verify the expected output.

    Figure 68
    Figure 68

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

    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:
      August 16, 2017 at 1:49 pm

      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.

      • Carl Webster Says:
        August 16, 2017 at 1:51 pm

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

        Webster

    2. François Gagné Says:
      August 9, 2017 at 7:43 am

      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

    3. Datta Says:
      March 6, 2017 at 10:35 pm

      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

    4. Knight Says:
      August 31, 2016 at 10:11 am

      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

      • Carl Webster Says:
        September 7, 2016 at 4:59 pm

        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

    5. Fred Says:
      May 12, 2016 at 2:44 pm

      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.

      • Carl Webster Says:
        May 17, 2016 at 9:51 am

        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

    6. Kyle Woodbury Says:
      March 29, 2016 at 3:57 pm

      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.

    7. Luciano Says:
      February 10, 2016 at 10:37 am

      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.

    8. Michael Dyer Says:
      January 4, 2016 at 4:48 am

      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.

    9. Salim Says:
      October 31, 2015 at 3:52 am

      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 ….

      • Carl Webster Says:
        October 31, 2015 at 8:11 am

        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

    10. Goran Jozic Says:
      October 10, 2015 at 9:10 am

      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.

      • Carl Webster Says:
        October 10, 2015 at 9:15 am

        Yes, it is MUCH better and easier if you setup the mirror databases from the beginning.

        Thanks

        Webster

    11. Magnus Says:
      August 26, 2015 at 3:03 pm

      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

      • Carl Webster Says:
        August 26, 2015 at 4:14 pm

        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

    12. Mike Soucie Says:
      May 12, 2015 at 9:02 am

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

      • Carl Webster Says:
        May 15, 2015 at 5:00 pm

        Different types of data, quantity of data and data access frequency.

        Webster

    13. Mike Soucie Says:
      May 12, 2015 at 7:44 am

      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?

      • Mike Soucie Says:
        May 12, 2015 at 8:39 am

        I found a registry fix for this problem.

        • Carl Webster Says:
          May 15, 2015 at 5:02 pm

          What was it?

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

          Webster

        • Andy Says:
          July 10, 2015 at 5:43 pm

          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

    14. Mike Soucie Says:
      May 11, 2015 at 10:13 am

      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.

      • Carl Webster Says:
        May 11, 2015 at 10:21 am

        use the full path to sqlcmd.

        Webster

        • Mike Soucie Says:
          May 11, 2015 at 10:55 am

          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.

          • Carl Webster Says:
            May 11, 2015 at 11:42 am

            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’”

    15. Carlos Beltran Says:
      February 16, 2015 at 10:11 pm

      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

      • Carl Webster Says:
        February 17, 2015 at 7:08 am

        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

      • Victor McBride Says:
        March 12, 2015 at 7:43 pm

        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?

        • Carl Webster Says:
          March 12, 2015 at 8:57 pm

          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

        • Victor McBride Says:
          March 13, 2015 at 8:30 am

          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

          • Carl Webster Says:
            March 13, 2015 at 8:33 am

            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

        • Victor McBride Says:
          March 14, 2015 at 2:49 pm

          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

    16. Carlos Beltran Says:
      February 16, 2015 at 10:08 pm

      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

      • Carl Webster Says:
        February 17, 2015 at 7:06 am

        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

    17. Marcel Otto Says:
      January 21, 2015 at 6:58 am

      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’

      • Carl Webster Says:
        January 21, 2015 at 7:06 am

        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

    18. Dave H Says:
      January 12, 2015 at 9:52 am

      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

      • Carl Webster Says:
        January 15, 2015 at 7:22 am

        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

    19. Rui Ribeiro Says:
      September 18, 2014 at 10:44 am

      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? 🙂

      • Rui Ribeiro Says:
        September 18, 2014 at 3:45 pm

        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

        • Rui Ribeiro Says:
          September 18, 2014 at 3:48 pm

          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

    20. sandeep kaushik Says:
      August 19, 2014 at 3:15 pm

      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.

    21. Will Says:
      July 7, 2014 at 8:53 pm

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