Citrix, SQL, XenDesktop

XenDesktop 7.1 SQL Mirroring

Mirroring in SQL is a great way to protect your XenDesktop infrastructure. In 7.1 deployments this can be a bit challenging since the documentation at Citrix doesn’t reflect an accurate way to accomplish this goal.

First let’s go over some basics. SQL Mirroring is a 3 server setup with a primary SQL server running a database, another secondary SQL server also running the database and a third SQL witness server which does NOT run the database (runs SQL, just no data). If you use local disk, this is an excellent setup. If you have two storage appliances, this is a great setup. If you have one big SAN, this doesn’t make much sense. To make mirroring worthwhile, you need 3 SEPERATE storage locations for each server. If you have two servers on the same storage, mirroring will not provide much value (other than a learning opportunity). I feel this is where people can easily forget why you mirror.

To demonstrate why, let’s say I have a two node management cluster, one host runs my primary SQL server and the other runs my secondary and the witness. I put the primary on the local disk of HOST1 and the secondary and witness (which is lightweight) on the local disk of HOST2. I have an issue. Let’s say HOST1 goes down, HOST2 is up and SQL stays up just fine because we have one of the mirrored servers running PLUS the witness. Let’s say I accidentally shut down the witness. No problem. Let’s say I shutdown HOST2 or do maintenance. Now I’ve got a problem. When the primary SQL server can’t see the witness AND the secondary SQL server, it stops. This is by design, it doesn’t know if it’s orphaned. It assume those two other servers don’t see it but must be serving the data. If I simply use a witness on a third unique HOST and storage area, my mirror is looking great! If I only have 2 hosts or shared storage, this is where a cluster makes sense. Clusters cannot survive storage failures, but mirrors can. However, you are writing to both storage locations at the same time. Often I’ll use two unique SANs and put the witness on local disk. I can now survive a storage appliance failure, however it’s only as good as having three different points of failure instead of one.

SQL Mirror

With that said, my main topic was how to get this done on your XenDesktop 7.1 controllers. This appears to have been posted other places but since I had to do it I thought I’d share also. There is an excellent post at Citrix on this here. here.

I did this manually below but I heavily recommend downloading his script and giving a shot before manually doing this.

I want to add one caveat, you MUST create the machine account logins in SQL on the mirror. So you’ll need to do this after a forced failover to the mirror. In addition, you may also need to delete the machine accounts and add them back if you migrate the SQL database, say from SQL Express to Standard/Enterprise for example. This is what worked for me, hopefully it helps

Now for this next part, I like to do this one controller at a time. If you mess up you can’t really go back and fix things if your controllers get orphaned. This is why during upgrades, you only partially update the farm, in case you need to roll back.


$cs = "Server=YOUR_SQL_SERVER_NAME;Initial Catalog=YOU_SQL_DATABASE_NAME;Integrated Security=True"


Set-LogSite -State Disabled
Set-LogDBConnection -DataStore Logging -DBConnection $null
Set-MonitorDBConnection -DataStore Monitor -DBConnection $null


Set-MonitorDBConnection -DBConnection $null
Set-AcctDBConnection -DBConnection $null
Set-ProvDBConnection -DBConnection $null
Set-BrokerDBConnection -DBConnection $null
Set-EnvTestDBConnection -DBConnection $null
Set-SfDBConnection -DBConnection $null
Set-HypDBConnection -DBConnection $null
Set-ConfigDBConnection -DBConnection $null -force
Set-LogDBConnection -DBConnection $null -force
Set-AdminDBConnection -DBConnection $null -force

Another way to clear the controllers out


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

If the last two won’t work, try adding -force on the end. If they still don’t do the following (may need to reboot)
Get-Service Citrix* | Stop-Service -Force
Get-Service Citrix* | Start-Service

Ok now it’s time to go mirror, once you’re done setting up the mirror set the database on ONE of the servers only and verify it before moving to the next one(s)

You already set the $cs variable but if you opened a new window or lost it, set it again


$cs = "Server=YOUR_SQL_SERVER_NAME;Initial Catalog=YOU_SQL_DATABASE_NAME;Integrated Security=True"
set-ConfigDBconnection -dbconnection $cs
set-AdminDBconnection -dbconnection $cs
set-LogDBconnection -dbconnection $cs
set-AcctDBconnection -dbconnection $cs
set-BrokerDBconnection -dbconnection $cs
set-EnvTestDBconnection -dbconnection $cs
set-HypDBconnection -dbconnection $cs
set-MonitorDBconnection -dbconnection $cs
set-ProvDBconnection -dbconnection $cs
set-SfDBconnection -dbconnection $cs
Set-LogDbConnection -DataStore logging -DbConnection $cs
Set-MonitorDbConnection -DataStore monitor -DbConnection $cs


Set-LogSite -State Enabled


$testString = Get-BrokerDBConnection
Test-BrokerDBConnection $testString | fl

Now make sure you TEST FAILOVER before declaring success.

One thought on “XenDesktop 7.1 SQL Mirroring

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s