SQL mirroring for Citrix

So, you’re building a XenDesktop environment and you want to mirror the database for it.  while you’re at it, why not mirror PVS and XenApp, too?  They both support it.  But how do you do it?  You’re a Citrix admin, not a database admin!

Start by installing SQL Standard (or Enterprise) on two different servers.  Do NOT put the databases on the system drive!  Otherwise, just a vanilla install is fine (make sure to install SQL management studio).

For XD – create an empty database on the first SQL Server and call it whatever you want (XenDesktop is nice, but whatever).  The collation needs to be LATIN1_GENERAL_CI_AS_KS , and the recovery model needs to be set to full.  Also, make sure you have configured the proper permissions for your XenDesktop service account to access the database.  Then – create a new query and enter the following:

BACKUP DATABASE [XenDesktop] TO  DISK = N'C:\temp\XenDesktop.bak' WITH NOFORMAT, NOINIT,  NAME = N'XenDesktop-Full Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Then right click in the query window and select execute.  Copy the file XenDesktop.bak to the second SQL server.  Right click Databases and select Restore Database.  Enter XenDesktop as the database name, then select From Device, and browse to the file.  Then, check the box in the display next to the file name.  Then click Options in the left pane, and then make sure to select “without recovery”.

Now, go back to the first SQL Server and right click the database and then in the left pane, click Mirroring.  Click Configure Security.  Make sure the box for Witness Server is unchecked (if you want a witness, you can read more about it on MSDN, but this is a Citrix blog, not a SQL blog, so we’re keeping it simple).  Then enter the information for both the SQL servers.  Then for the account, use your SQL service account for each server (your SQL Servers do use a service account, right?  If not, go change it now – because if you want to use local system or network service, you need to use certificates, and I HATE certificates!).  Since the SQL servers in my deployments are generally dedicated to Citrix databases, I like to use the same service account for both servers.  If you use a different one for each server, just make sure you enter the correct one for each server.  After the wizard completes, click start mirroring.  That’s it.

Repeat the process for XenApp and PVS, changing the database names and .bak file names appropriately.  PVS and XenApp don’t much care about the database collation type like XenDesktop does.  For XenApp, though, you need to download the Native SQL client for the version of SQL Server you are using (2008 R2 is still the easiest with all the additional restrictions imposed by 2012).  After it’s downloaded, stop the IMA service, install the new client, and then open MF20.dsn and modify it thusly:

The Driver line should read

DRIVER={SQL Server Native Client 10.0}

Then, under the line beginning SERVER=, add the following line:


Obviously, put the name of your SQL Server in there.

Then just start the IMA service back up.

Nothing special should need to be done with PVS, provided the databases are correct.