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

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

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

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

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

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

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

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

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

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

  9. Mike Soucie Says:

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

    Reply

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

      • 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

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

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

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

      • 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

      • 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

      • 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

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

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

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

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

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

  18. Will Says:

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

    Reply

Leave a Reply