Learning to Migrate a Citrix XenApp 6.x Data Store from SQL Server Express to SQL Server

When a Citrix XenApp 6.x Farm is created, by default, the data store is created on the first server of the Farm using an SQL Server Express database.  This article will teach you how to:

• Backup an SQL Server Express data store,
• Create a new File DSN for a SQL Server data store,
• Migrate to the new data store,
• Configure all XenApp servers to communicate with the new data store,
• Stop and restart the IMA service on a XenApp server, and
• Using AppCenter, verify communication with the new data store.

There are a couple of advantages to using SQL Server Express for the data store:

• Easy to setup during the XenApp 6.x installation.
• No complicated ODBC DSN file to create or configure.

The disadvantages to using SQL Server Express for the data store:

• If the XenApp server hosting the data store goes down or is restarted, Farm functionality is severely crippled.
• Only recommended for small Farms because of performance reasons.
• No High Availability options.
• Rarely included in backup or disaster recovery planning.

The advantages to using SQL Server for the data store:

• Is not run on a XenApp server.
• Can be used for any size Farm.
• High Availability options.
• Usually included in backup and disaster recovery planning.

For this article, XenServer 6 will be used with Windows Server 2008 R2 Standard and Citrix XenApp 6.5 Platinum.  The following Virtual Machines (VM) will be used:

SQL Server: SQL

• The VM will be assigned two processors, 2GB of RAM and 24GB of Hard Drive space
• This VM will host the migrated SQL Server data store
• This VM will use SQL Server 2008 R2
• Static IP Address 192.168.1.150

XenApp 6.5 #1: XENAPP65-1

• The VM will be assigned two processors, 4GB of RAM and 24GB of Hard Drive space
• This VM will host the original SQL Server Express data store
• Static IP Address 192.168.1.153

XenApp 6.5 #2: XENAPP65-2

• The VM will be assigned two processors, 4GB of RAM and 24GB of Hard Drive space
• This VM is used to show how to change additional XenApp servers to use the new SQL Server data store
• Static IP Address 192.168.1.154

XENAPP65-1 has a folder named CTXBACKUP that will be used to store the data store backup.  XenApp 6.5 was installed on XENAPP65 and a new XenApp Farm named Webster was created during the installation.  After restarting the VM, XenApp 6.5 was installed on XENAPP65-2 and joined to the Webster Farm.  XenApp 6.5 has a new configuration option.  A XenApp 6.5 server can be configured to either host sessions only or host sessions and be a data collector.  Both servers are configured with the Controller and Session-host mode enabled.  Two applications were published on the Farm: Notepad and Paint (Figure 1).

Figure 1

Both applications are configured to run from both XenApp servers (Figure 2).

Figure 2

It is a Best Practice to always backup the data store before performing the migration.  The SQL Server Express based data store, by default, is located in C:\Program Files (x86)\Citrix\Independent Management Architecture and is named MF20.mdf.

From a command prompt on XENAPP65-1, the following command, as shown in Figure 3, was run to generate a backup:

dsmaint backup c:\ctxbackup

Figure 3

The command “dsmaint backup” makes a copy of the MF20.mdf SQL Server Express data store to the location specified.  “dsmaint backup” must be run on the XenApp 6.x server hosting the SQL Server Express data store.

BEFORE YOU START THE MIGRATION, make sure the account being used for this procedure is a Full Farm Administrator.

Note:  If you have a Microsoft SQL Server DBA available, please reference the appropriate Citrix eDocs sections:

• XenApp 6.0 – http://support.citrix.com/proddocs/topic/xenapp6-w2k8-install/ps-database-ref-sql-srvr.html
• XenApp 6.5 – http://support.citrix.com/proddocs/topic/xenapp65-install/ps-database-ref-sql-srvr.html

To create the SQL Server data store database, open SQL Server Management Studio.  Click Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio (Figure 4).

Figure 4

Login using the necessary credentials (Figure 5).

Figure 5

Expand Databases in the left column (Figure 6).

Figure 6

Right-click Databases and select New Database (Figure 7).

Figure 7

You may want to consider putting the XenApp Farm name in the database name as shown in Figure 8.

Figure 8

Click OK and the new data store database is ready for use as shown in Figure 9.

Figure 9

On the XenApp server hosting the SQL Server Express data store, a new Data Source Name (DSN) needs to be created for the SQL database.

Click Start -> Administrative Tools -> Data Sources (ODBC) (Figure 10).

Figure 10

Note: What is ODBC?  This is taken from http://support.microsoft.com/kb/110093

“Open Database Connectivity (ODBC) is Microsoft’s strategic interface for accessing data in a heterogeneous environment of relational and non- relational database management systems. Based on the Call Level Interface specification of the SQL Access Group, ODBC provides an open, vendor- neutral way of accessing data stored in a variety of proprietary personal computer, minicomputer, and mainframe databases.

ODBC alleviates the need for independent software vendors and corporate developers to learn multiple application programming interfaces. ODBC now provides a universal data access interface. With ODBC, application developers can allow an application to concurrently access, view, and modify data from multiple, diverse databases.”

Click the File DSN Tab (Figure 11).

Note:  What is the difference between “User DSN”, “System DSN” and “File DSN”?  This is taken from  http://support.microsoft.com/kb/213772.

User DSN: The User DSN is a data source that is user-specific. A User DSN is stored locally but is available only to the user who creates it.

System DSN: Unlike a User DSN, a System DSN is not user-specific. A System DSN is stored locally and is not dedicated to a particular user. Any user who logs on to a computer that has permission to access the data source can use a System DSN. Some programs, such as Microsoft SQL Server or Microsoft Internet Information Server (IIS), require a System DSN.  This DSN must be created on the server where the program is located. System DSNs are stored in the Windows registry under the following key: HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc Data sources

File DSN: The File DSN is created locally and can be shared with other users. The File DSN is file-based, which means that the .dsn file contains all the information required to connect to the data source. Note that you must install the ODBC driver locally to use a File DSN.  File DSNs are not stored in the Windows registry. The .dsn file is a text file that you can view in any text editor, such as Microsoft Notepad.

Figure 11

Change the Look in to C:\Program Files (x86)\Citrix\Independent Management Architecture as shown in Figure 12.  The new DSN must be placed in this folder.  The IMASERVICE service is programmed to look in only this folder.

Figure 12

Click the Add button (Figure 13).

Figure 13

Click on SQL Server and click Next (Figure 14).

Figure 14

Type in C:\Program Files (x86)\Citrix\Independent Management Architecture\WebsterFarmSQLDS.dsn and click Next (Figure 15).

Note: The DSN can be named anything as long as it has a .dsn file extension.  It does not need to contain “MF20″ anywhere in the name.

Figure 15

Click Finish (Figure 16).

Figure 16

Enter a Description, type in the name of the SQL Server and click Next.

Figure 17

If a successful connection to the SQL Server is made click Next (Figure 18).  A common problem is the Windows Firewall is not configured to allow SQL traffic (TCP Port 1433).  To configure an instance of SQL Server 2008 R2 to use a static port, follow the steps described in the “How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)” topic located at http://msdn.microsoft.com/en-us/library/ms177440.aspx.

Figure 18

Note:  If SQL Server is set to use a static port, click Client Configuration, then uncheck Dynamically determine port and enter the value for the static port number (Figure 19).

Figure 19

If the account listed in the grayed out Login ID authenticates successfully to the SQL Server, Change the default database to the new SQL Server data store and click Next (Figure 20).

One problem could be the Login ID account is not setup to access the SQL Server.

Figure 20

Click Finish (Figure 21).

Figure 21

Click Test Data Source (Figure 22).

Figure 22

If the message TESTS COMPLETED SUCCESSFULLY is shown, click OK (Figure 23).   Otherwise, you will need to troubleshoot the ODBC connection to the SQL Server.  Information on SQL Server 2008 R2 ODBC can be at http://msdn.microsoft.com/en-us/library/ms131415.aspx.

Figure 23

Click OK (Figure 24).

Figure 24

Your new DSN is created (Figure 25).   Click OK.

Figure 25

Click Start, right-click Command Prompt and select Run as administrator (Figure 26).

Figure 26

Type in dsmaint migrate /srcdsn:”C:\Program Files (x86)\Citrix\Independent Management Architecture\MF20.dsn” /srcuser:citrix /srcpwd:citrix/dstdsn:”C:\Program Files (x86)\Citrix\Independent Management Architecture\WebsterFarmSQLDS.dsn” /dstuser:administrator /dstpwd:password and press Enter.

/srcdsn is the SQL Server Express MF20.dsn that was created when the Farm was created

/srcuser and /srcpwd are both, by default, “citrix”

/dstdsn is the new DSN file

/dstuser and /dstpwd are the username and password of the account that has rights to the new SQL Server data store database

Click Yes on the Dsmaint confirmation popup (Figure 27).

Figure 27

The migration starts as shown in Figure 28.

If the following error is received:

“Result: “an unknown error occurred while migrating the table, please verify the destination datasource is clean. [Microsoft][ODBC SQL server Driver][SQL server]violation of primary key constraint ‘CL’. Cannot insert duplicate key in object “keytable”. Indexes have been successfully patched.”

This is usually caused by the default database being set to Master.  That is a huge mistake.  Make sure the default database for the DSN file is set to the new SQL Server data store database.

Figure 28

When the migration completes, answer Yes to the Dsmaint comparison popup (Figure 29).

Figure 29

The comparison runs and completes as shown in Figure 30 and Figure 31.

Figure 30

Figure 31

Next DSMAINT CONFIG needs to be run.  The /pwd option needs to be used and according to the XenApp Administrator’s Guide:

“You must stop the Citrix Independent Management Architecture service before using config with the /pwd option.”

In the command prompt window, type net stop imaservice and press Enter (Figure 32).

Figure 32

Type in dsmaint config /user:websterslab\administrator /pwd:password /dsn:”C:\Program Files (x86)\Citrix\Independent Management Architecture\WebsterFarmDSMF20.dsn” and press Enter (Figure 33).

/user and /pwd are the username and password of the account that has rights to the new SQL Server data store database.

Figure 33

The Local Host Cache file needs to be recreated.  Type in dsmaint recreatelhc and press Enter (Figure 34).

Figure 34

Type in net start imaservice and press Enter (Figure 35).

One cause of the IMASERVICE not restarting is the currently logged in account is not a Full Farm Administrator.

Figure 35

Type in CD “C:\Program Files (x86)\Citrix\Independent Management Architecture” and press Enter.

Edit the new DSN file and remove the computer name on the WSID line as shown in Figure 36 and Figure 37.  The WSID line is not used.

Figure 36

Figure 37

Copy the new DSN file to the remaining XenApp server’s C:\Program Files (x86)\Citrix\Independent Management Architecture folder (Figure 38).

Figure 38

On each remaining XenApp server, as shown in Figure 39, type in:

net stop imaservice and press Enter.

dsmaint config /user:\administrator /pwd:password /dsn:”C:\Program Files (x86)\Citrix\Independent Management Architecture\WebsterFarmSQLDS.dsn” and press Enter.

dsmaint recreatelhc

net start imaservice and press Enter.

Figure 39

On any of the XenApp servers, start AppCenter (Figure 40).

Figure 40

AppCenter communicates with the data store to retrieve the Farm information.

If any errors are received, go to http://support.citrix.com and search for the error number.

An alternate way to test connectivity to the data store is from a command prompt type in QFARM and press Enter (Figure 41).

Figure 41

In this article, you learned how to:

• Backup an SQL Server Express data store,
• Create a new File DSN for a SQL Server data store,
• Migrate to the new data store,
• Configure all XenApp servers to communicate with the new data store,
• Stop and restart the IMA service on a XenApp server, and
• Using AppCenter, verify communication with the new data store.