Migrating Citrix XenApp 6.5 data store database from one SQL server to another

I’ve been meaning to write a blog post for migrating Citrix a XenApp 6.5 data store database from one SQL server to another as this is quite common to do whether it’s due to server refreshes, disaster recovery or other similar situations.  First off, there are various Citrix documents available on the internet that describes the process and they are found in the following links:

Data Store Migration Strategies
http://support.citrix.com/article/CTX123111

How to Move or Migrate Data Store on XenApp 6 to another Server
http://blogs.citrix.com/2010/06/03/how-to-move-or-migrate-data-store-on-xenapp-6-to-another-server/

Migrating a Farm Data Store from MSDE to SQL Server Express
http://support.citrix.com/proddocs/topic/xenapp5fp2-w2k3/ps-datastore-migrate-msde.html

The problem I have with these documents are that they don’t describe the process in details and some of them omit various steps.  In a situation where you’re pressed for time or have already been working for extended hours having to response to an emergency, it’s not pleasant at all to fill in the gaps and this is why I thought I’d blog the steps incase I ever find myself in such a situation.

Let’s start with a bit of background information about the environment:

Citrix XenApp version: 6.5

Production SQL Database: SQL Server 2008 R1 | Build: 10.0.5500

Production SQL Database Server and instance: SVR-SQL-04\Citrix

Destination SQL Database: SQL Server 2008 R2 | Build: 10.50.1600

Production SQL Database Server and instance: SVR-SQL-06\Citrix

XenApp Servers:

• SVR-CXA-01
• SVR-CXA-02

Stop the IMA (Citrix Independent Management Architecture) Service

The Citrix documentation doesn’t explicitly state that you need to stop the IMA service but logically thinking about it makes me believe that it should be stopped to avoid any writes to the SQL database.  I’ve tried this in a production environment and have not seen any issues but whether you choose to stop it or not will be your choice.

Back up XenApp data store Database

Once the IMA service has been stopped on all of your XenApp servers in the farm, proceed with launching Microsoft SQL Server Management Studio on the production SQL server and navigate down to the database:

Right click on the database and select Tasks –> Backup:

From within the Back Up Database window, remove the default Destination path:

… then click on the Add button:

In the Select Backup Destination window, click on the … button and navigate to the destination path where you want to backup the database:

For this example, we’ll be using the C: and naming the file XenApp65.bak:

Click on the OK button and then again in the Select Backup Destination window:

In the Back Up Database window, confirm the settings and click on the OK button to proceed with backing up the database:

Create the XenApp database service account on the new SQL server

Prior to restoring the database, we’ll need to create the XenApp database server account on the new server so that when we restore the database, the restore will retain the service account dbowner assignment:

Restore XenApp data store Database

With the XenApp database service account created on the new production SQL server, begin by copying the backed up database file onto the new server:

… and then proceed with restoring the backed up database by right clicking on the Databases node and select Restore Files and Filegroups…:

From within the Restore Files and Filegroups window:

… select From device and click on the … button:

Within the Select backup devices window, ensure that the Backup media type is selected as File then click on the Add button:

Locate the backed up database and click OK:

Continue and click on the OK button again:

From within the Restore Files and Filegroups window, type in the same XenApp database name into the To database field, ensure that the Restore checkbox is selected and then click on the OK button to commence the restore:

Note that the database isn’t immediately revealed after the restore so use the F5 button to refresh the list:

Updating the DSN for XenApp’s connection to the new SQL server

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

From here, the Citrix blog post:

http://blogs.citrix.com/2010/06/03/how-to-move-or-migrate-data-store-on-xenapp-6-to-another-server/

… suggests that we use the ODBC Data Source Administrator:

to reconfigure the mf20.dsn file but what I’ve found in the past is that using this tool adds and somewhat changes certain values in the mf20.dsn.  Note the following differences:

Original mf20.dsn:

New mf20.dsn created by the ODBC Data Source Administrator:

Note how the ordering is a bit different and the new and modified fields:

• WSID
• APP

Since the mf20.dsn is simply a text file, I choose to just use Notepad to edit the parameters.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

With the previous stated, I personally prefer to just open up the mf20.dsn file located in the directory:

C:\Program Files (x86)\Citrix\Independent Management Architecture

… with notepad and edit the following fields:

• Server

Simply update the server FQDN to the new server:

Start the IMA service and execute dsmaint config

With the mf20.dsn updated, proceed with starting the IMA service:

… then use the dsmaint config command:

dsmaint config /dsn:"c:\Program Files (x86)\Citrix\Independent Management Architecture\mf20.dsn"

… to connect to the data store with new configuration settings:

C:\>dsmaint config /dsn:"c:\Program Files (x86)\Citrix\Independent Management Architecture\mf20.dsn"

Attempting to connect to the data store with new configuration settings.

Successfully connected to the data store.

Configuration successfully changed.

Please restart the IMA Service for changes to take effect.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note that if you receive the following error:

C:\Program Files (x86)\Citrix\Independent Management Architecture>dsmaint config /dsn:mf20.dsn

Attempting to connect to the data store with new configuration settings.

Failed to connect to the data store. The settings will be reverted to the previo

us configuration.

Unable to change configuration settings.

Please verify parameters and data source.

C:\Program Files (x86)\Citrix\Independent Management Architecture>

It’s because you need to specify the full path to the mf20.dsn file and that simply changing the directory to its location will not work.  The following Citrix KB mentions this: http://support.citrix.com/article/CTX108699

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Once the dsmaint config executes appropriately, proceed with restarting the IMA service:

Verify New Data Source

With the data source updated to point to the new server, proceed with taking the original production database offline:

… then opening up the registry on the XenApp server, navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Citrix\IMA\

… then review the value of the DataSourceName key (Type: REG_SZ) to ensure that the path and file is correct:

Once the data source registry key has been verified, continue by testing the XenApp server’s functionality but launching applications through the Web Interface.

Repeat for other XenApp Servers

Once we have verified that the updated XenApp server is in working order, proceed by repeating the following steps on each of the other servers in the farm.

Copy the mf20.dsn file to the other servers:

Run the following command:

dsmaint config /dsn:"c:\Program Files (x86)\Citrix\Independent Management Architecture\mf20.dsn"

Restart the IMA service:

Verify the registry DataSourceName key value:

Test the XenApp Server by launching applications.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hope this helps anyone out there looking for more detailed instructions on how to migrate a XenApp 6.5 farm’s SQL database to another server.