Wednesday, November 14, 2012

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.

imageimage

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:

image

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

image

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

image

… then click on the Add button:

image

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

image

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

image

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

image

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

imageimageimage

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:

image

image

image

image

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:

image

image

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

image

From within the Restore Files and Filegroups window:

image

… select From device and click on the button:

image

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

image

Locate the backed up database and click OK:

image

Continue and click on the OK button again:

image

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:

imageimage

image

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

imageimage

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:

image

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:

image

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

image

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

image

… with notepad and edit the following fields:

  • Server

image

Simply update the server FQDN to the new server:

image

Start the IMA service and execute dsmaint config

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

image

… 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.

image

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

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>

image

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:

image

Verify New Data Source

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

image

… 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:

image

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:

image

Run the following command:

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

image

Restart the IMA service:

image

Verify the registry DataSourceName key value:

image

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.

18 comments:

Ganesh said...

This is very helpful! Thank you for posting detailed steps!

amsterdamned said...

this is a great walkthrough of the process. I'd add one more step, at the conclusion, run dsmaint recreatelhc after changing the DSN ... that is, unless your service stop at the beginning, then re-start at the conclusion is accomplishing the same thing?

Anonymous said...

Very clear walkthrough. Great job! Thx

Anonymous said...

Thank you so much for your walk through. You filled in quite a few of the information gaps that were lacking from the similar document that Citrix provided. The only thing that I also needed was the IMA Helper tool from CTX133983 which will help in updating the DNS file automatically.

Kailas S said...

Hi Thank you for your post.

after migrating the Data store form one server to another, when i checked the HKLM_Software_Policies_Citrix_IMA_DatabaseSettings_Initial data baseserver name still showing onld SQL name

Randy said...

Terence,
Thanks so much for the clear and precise documentation on migrating a SQL Datastore. SO MUCH APPRECIATED! Keep up the great work.

Matt said...

Still getting:

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.

Any suggestions?

John Michle said...

Its really informative, some facts and other points given here are quite considerable and to the point as well, would be better to look for more of these kind for efficient results.

Field Service Management Software

Gregory Van Der Mewve said...

Thank you this was extremely helpful.

Anonymous said...

dsmaint command line did not work for me. But i downloaded the IMA helper GUI and it worked like a charm.

Pete Petersen said...

Terrence, you're the best. Had trouble with adding a service account as a part of the move, but we're golden now. You made the whole thing easy!

Anon said...

Hi Terrence, great article I have just successfully migrated my XenApp65 database from SQL2008R2 to SQL2014 including around 30 XenApp servers.
One issue that I came across with some XenApp servers failing on the dsmaint command which may be useful for others to know:

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

This error occured a few times for me and it turns out I needed to install the SQL Client Native driver, after that they ran the dsmaint command fine. Hope that helps somebody else!

digital signature certificate said...

EXCELLENT information. Your directions are clear and concise, and easy to follow. Thanks for your hard work in posting this info.

BK said...

Is it possible to rename the data store database on the new sql server? Or is not recommended? If the answer is "yes u can change the DB name " what would be the steps for it?

Aryan Bayas said...

Hi Terence,

is it not necessary to create an empty database on the new SQL servers, before restoring? Will it create the database at the time of restore only? Please reply.

Keith Winter said...

Wow, I have been searching for days regarding the issue of properly moving our citrix database; you really did a great job.

thank you.

Brian Mooney said...

This is by far the best article I have seen with detailed steps. I have a questions regarding the maintenance window. So generally speaking can this process be done during production hours while users are actively connected to Citrix? To my knowledge existing users connected will be non impacted but any new user attempts to connect will not work.

Is the general recommendation to do this after hours during non production usage? We have a 24x7 environment which is why I am asking.

Thanks for the article.

Anonymous said...

when i try to migrate the data store from SQL 2008 to SQL 2012, i end up with error " unable to connect to the destination data store". all permission on the destination DB are correct & same as source DB.any thoughts ?