In XenApp/XenDesktop 7.x environments, an often overlooked element of Citrix best practices is ensuring high-availability of the Citrix databases. Personally, I blame this on the ease by which database high-availability could be enabled in XenApp 6.5. All you had to do was mirror the database, edit the DSN file to add a Failover Partner, then restart the IMA service and you’re done….nice and easy.
But in XA/XD 7.x, the process to setup database HA is much more involved and requires that all delivery controllers be disconnected from the existing database prior to configuring the mirror. Written by Carl Webster, here is an impressive blog post that details the process of changing a production XA/XD site to use SQL mirroring. If you are faced with this task, I hope your Citrix infrastructure is virtual and that you remember to take snapshots before starting.
What I’m going to do here, is detail the process to setup SQL database mirroring for XA/XD 7.x from the beginning. I’m going to break it into 4 posts so as to detail each step and to avoid a single post that’s longer than my attention span. In my lab, I ran SQL Server 2012 on Windows Server 2008R2 Standard so what you see when setting up database mirroring may be slightly different that what is presented here.
First, a few things to remember when installing SQL, when you get to the Server Configuration screen:
- On the Feature Selection screen, the only items needed to support database mirroring are Database Engine Services and Management Tools (Basic and Complete)
- On the Server Configuration screen, configure the SQL Server Database Engine service to use a domain account and use the same account on all SQL servers (Principal, Mirror, and Witness servers). I have seen documentation that says all (3) SQL services should be set to use the domain account but in my experience, I’ve had success when setting only the SQL Server Database Engine to use a domain account.
- Set the SQL Server Browser service to Startup Type to Automatic
If you do not use a domain account for the SQL Server Database Engine service, the database mirror wizard will fail and you’ll likely see an error similar to that shown below as well as a MSSQLSERVER 1474 error in the Application Event Log
- On the Database Engine Configuration screen, choose your preferred Authentication Mode. Personally, I selected Mixed Mode because I like having the SA account as a fallback. Under Specify SQL Server administrators, add the domain account used by the SQL Server Database engine service.
Now that SQL is installed, let’s create our Citrix database.
1. On what will initially be the principal SQL server, open SQL Server Management Studio
2. Right-click Databases and select New Database.
3. On the General page, specify a Database Name and then click Options.
4. On the Options page, you must set the Recovery model to Full in order to support Database Mirroring. But what do you set Collation to? Does it make any difference?
My Collation Story
I’m no SQL admin, I admit it! I’m not ashamed of it….I don’t know if I truly understand the depth of the meaning of collation, nor if I even care to, but I think it has something to do with how data is sorted. I can say this about collation, in terms of setting up a XA/XD environment, it does matter. At this point of my installation, I decided to experiment a little bit by setting collation to different values to see how XA/XD would react when I tried to create my Site. As it turns out, if the collation is wrong for XA/XD, you see an error message saying so, it specifically says that you must use a collation which ends with “_CI_AS_KS”. In general, it is best to use a collation which ends with “_100_CI_AS_KS”.
HA! So, what did I do when creating my database? I set the collation to Latin1_General_100_CI_AS_KS which is in alignment with Citrix support article CTX127359, How to Configure XenDesktop for SQL Database Mirroring, which states in Step 1!!!, “Create an empty database on the principal with the Latin1_General_100_CI_AS_KS collation sequence.
Oh, but if only my collation story ended there…..let’s fast forward again to setting up the XA Site. When running the XA Site Setup Wizard, you are prompted to specify the database server and the database name. After doing so, you click Test Connection ensure connectivity to SQL.
With the collation set to Latin1_General_100_CI_AS_KS, I received an error that my account had insufficient permissions to access the database server:
Upon some troubleshooting and research, there was a suggestion to change the collation. Change the collation!! Why, it’s set according to documentation from Citrix as well as the error within XA/XD itself….I set it to what Citrix has asked for! But I checked everything else I could think of and so, I decided to change the collation to Latin1_General_CI_AI_KS and wouldn’t you know it, the database connectivity test passed.
The good news…I had already setup my database mirror and was able to change the collation on the mirrored database without any problems, without having to delete my mirror and start from scratch, etc.
4. (continued from above) So the point of the collation story is this; when setting up your Citrix database, use the Latin1_General_CI_AI_KS collation method.
5. Click OK to create the new CitrixDB database.
With the database created, the next step (to be covered in Part 2 of this series) in setting up the mirror is to backup the database on the principal and restore it onto the mirror.