XenDesktop – SQL Mirroring with Witness

I'm Oscar Mas and I want to show you how to implement a Mirror system with Witness based on Microsoft SQL Server, which is a perfect and recommended example to have our XenDesktop database in high availability. In this way we will be able to have a mirror with Microsoft SQL failover.

To have an automatic failover, The mirror must be set to "High-Safety Mode", This is done automatically by selecting the witness in the configuration wizard that we will see later. We are to provide him with a third servant, in our case called witness.ilba.cat, which will make us the functions of witness. In the following image we can see the high-safety mode with Witness:

The entire post was made with SQL 2012 Server and SQL 2012 Express. Databases on sql1.ilba.cat and sql2.ilba.cat servers have SQL 2012 Server and Witness is SQL-based 2012 Express. I have chosen the version 2012, but the procedure is almost identical in all versions of Microsoft SQL. I leave you with the Matrix of SQL versions that support Citrix products: http://support.citrix.com/article/CTX114501

The logical scheme is as follows:

Once the SQL is finished installing on the three servers, it is highly recommended that you update your servers using the Windows Update tool, WSUS etc....

During the installation process of both SQL Server and SQL Express-based systems, have been configured to support systems in Mixed Mode (SQL Server authentication and Windows authentication). Installing SQL Express, It has been done by leaving the default options provided by the installation wizard, but in the SQL Server installation, we have enabled the replication option on the two SQL servers (sql1.ilba.cat and sql2.ilba.cat), as shown in the figure below:

It is highly advisable, Install the Admin Tool (Management Studio) on all servers, in this way if there is any incident with any of the servers, We could manage and see what happens.

We will create our database in the sql1.ilba.cat, which we will call CTX_STORE. Which later when we install XenDesktop, We will indicate this database the one we want to use to deploy our broker(s).

Remember that if we create the database manually, then when deploying our XenDesktop we will have to update the database manually with two scripts provided by the Wizard of the installation of our Delivery Controller. One script will be launched from the primary SQL server and the other from the server that does the mirror functionalities. For more information on how to deploy our Delivery Controller with a SQL mirror-based, see: http://support.citrix.com/article/CTX127359

The procedure we will perform to be able to configure our Mirror system with Witness, will be next:

Creating the Server Pool
SQL Port Check
Database export
Importing the Database
Setting up the mirror

Server group creation

Before you proceed with the Mirror configuration, we will create a group in our ADUC (Active Directory Users and Computers). Since later on, The moment we create the mirror, asked us for a group. You can see this at the end of the section on configuring the mirror. In my case I have created a group that I have called SQLServers:

And in this group we will add the three servers that will form our Mirror, as indicated in the following screenshot:

SQL Port Check

The default version of SQL Server allows us to access the SQL port, but in SQL Express we will have to enable it to listen through the default port. The default port of SQL is the 1433 TCP. To verify that our SQL server is actually accepting requests, We'll launch the following command from a console:

C:Windows>netstat -ano | Find "1433"

Database Export

This procedure will be done from the first server (sql1.ilba.cat). What we will do during the database export, is to export both the database that we have created previously (CTX_STORE) and database logs. This is necessary in order to be able to configure the SQL Server mirror later

We will enable our database the "Full Recovery Mode", since by default the databases we create in SQL, are from "Simple Recovery Mode". We'll do this from the database properties as follows:

To export the database, We will select our database (in our case CTX_STORE) and in the "Task" option, we will tell you "Back Up...."

We will indicate that the backup we want to make is Full and the location where we will create the backup of the database

To export database logs, We will select our database (in our case CTX_STORE) and in the "Task" option, we will tell you "Back Up...."

We will indicate that the backup we want to make a backup of the Transactional Log and the location where we will create the backup of the database logs

Database Import

This import procedure will be carried out from the second server (sql2.ilba.cat). We will copy the data that we have exported in the previous point, both the server database, How the logs from the server sql1.ilba.cat to our mirror server: sql2.ilba.cat. It is advisable that the paths of origin and destination are the same.

From the server that is going to act as a mirror we will restore the database. To import the database we will go to the "Task" option and we will indicate "Restore Database...."

We will select the "Device" option and select both the database and the logs that we have previously exported, so that they are imported:

We will indicate in "Recovery State" that it is a "RESTORE WITH NORECOVERY", as follows:

And we will be able to observe how the restoration of the database begins:

Configuring the Mirror

In this section, what we'll do in the Mirror settings. We will select our database from the server (sql1.ilba.cat ). To create the database mirror we will go to the "Task" option and we will indicate "Mirror...."

A window will appear, in which we will go to the option "Configure Security....", which will launch a wizard to be able to configure our Mirror system

We will tell the Wizard that we want Witness, Pressed “Yes”:

Then, the server will ask us to perform the functionalities of "Principal Server", in our case sql1.ilba.cat

Subsequently, asked us for the server to perform the functionalities of "Mirror Server", in our case sql2.ilba.cat

And finally, the server will ask us to perform the functionalities of "Witness Server", in our case witness.ilba.cat

In this step, We will indicate the server group we have created at the beginning of the post, which we have called SQLServers:

We will verify that the configuration of our mirror system is correct, Once the setup is finished:

We will instruct you to start the mirror, as indicated on the following screen:

Once the mirror is started, we will be able to see the configuration of our system, in which the main one is our server sql1.ilba.cat, The server that will act as Mirror is the sql2.ilba.cat and the server that will act as Witness is the witness.ilba.cat: