How to migrate a XenApp 6.5 Data Store to a new SQL server

Migration of XenApp data store to a new SQL server  can be done using different scenarios. In this article we will explain how to move a datastore to a new SQL server using 3 different set of tools. We will follow the scenarios below:

• Scenario 1: Migration using only dsmaint command
• Scenario 2: Migration using mix of SQL Console and dsmaint command
• Scenario 3: Migration using IMA Helper Tool

Scenario 1

Summary

In this scenario we will migrate a data store located on server XA03 to a new database located on SQL Express installed on server XD01. To perform a migration we will use dsmaint command. SQL management studio will be used only in order to create a empty database on new SQL server and assign user name and permission to the database.

Action plan is the following:

1. Take a backup of your exisiting datastore.
2. Migrate the content of data store to a new database.
   • Create empty database on target server
   • Create new dsn file pointing to the new SQL server
   • Migrate the content of data store to a new database.
   • Run dsmaint config command
3. Reconfigure remainig servers
   • On remaining servers create a new file dsn pointing to the new SQL server.
   • Stop IMA Service
   • Run dsmaint config command
   • Recreate local host cache
   • Start IMA Service

Take a backup of your exisiting datastore.

When XenApp Server Role Manager is used to install SQL Express a standard configuration is used and database engine is configured with the following settings:

• SQL Instance name: CITRIX_METAFRAME
• Database location folder: C:\Program Files (x86)\Citrix\Independent Management Architecture
• Database name: MF20.mdf

It is a best practice to always backup the XenApp database before performing any operation on data store. To preform the backup we will use dsmain command. The XenApp Server DSMAINT Reference contains syntax and use details.

Important note: In many posts or articles you can find an information that dsmaint command can be used to backup only data store stored in MS Access database. Please remember that starting from XenApp 6 MS Access is no longer supported and dsmaint backup command can be used to perform the backup of data store hosted on SQL Server Express.

From the command prompt the following command is executed:

dsmaint backup c:\backup

The result is shown below:

The command dsmaint with parameter backup” makes a copy of the MF20.mdf SQL Server Express data store to the specified location.  Please note that  dsmaint backup must be run on the XenApp 6.x server hosting the SQL Server Express data store. This command will not work when you try to backup database located on remote server hosting SQL Server Express and will not return and error message as it is shown below:

Migrate the content of data store to a new database.

To start the database migration process we have to create an empty database on target SQL Express server. Open SQL Server Management Studio and login with valid credentials and expand Databases node in the left panel

Right-click Databases node and select New Database

Click OK and the new data store database is created and ready for use as shown below:

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

Go to  Start -> Administrative Tools -> Data Sources (ODBC) and navigate to File DSN tab. Change the Look in to C:\Program Files (x86)\Citrix\Independent Management Architecture as shown below:

Click Add button to create a new dsn file. New dsn file must be pleaced in C:\Program Files (x86)\Citrix\Independent Management Architecture folder.

Click on SQL Server and click Next

Click browse and navigate to C:\Program Files (x86)\Citrix\Independent Management Architecture and type the name for new dsn file.

 Click Next and Finish and configure the connection parameters as description and server name.

Click next when completed

 Click Next. If everything is configured correctly you will be switched to the next page of the connfiguration wizard as it is shown below

If there is a problem with connection go to Client Configuration, uncheck Dynamically determine port and enter the value for the static port number. Please remember that SQL Express is  listening on dynamic ports unless it is configured to listed on a specific port (standard or non standard). Please check how to configure SQL Express to accept remote conections. Additional information on troubleshooting SQL Server 2008 R2 ODBC can be found at http://msdn.microsoft.com/en-us/library/ms131415.aspx.

When connection is established click Next

Click Finish and test connection

 To initiate the migration of data store open the command line window

In the command line window type the following command:

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\XA65Small.dsn” /dstuser:domain\administrator /dstpwd:password and press Enter.

/srcdsn is the SQL Server Express MF20.dsn that was created when the XenApp server was installed

/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

Note: I had a problem with dsmaint migrate command presented above.  The command syntax is correct and should work but in my case it does not. I used simple trick with ms dos variable:

set H=c:\Program Files (x86)\Citrix\Independent Management Architecture

dsmaint migrate /srcdsn:”%H%\MF20.dsn” /srcuser:citrix /srcpwd:citrix /dstdsn:”%H%\XA65Small.dsn” /dstuser:lab\administrator /dstpwd:password

 When finished click Yes on the Dsmaint confirmation popup

 The result of dsmaint command is displayed in the command prompt window:

 In the next step IMA service needs to be configured to connect to the new data store. Open the command prompt and stop IMA service:

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

Type  dsmaint recreatelhc and press Enter. This command will recreate Local Host Cache file.

Start IMA service

At this stage the migration of the server hosting data store is completed. If your XenApp farm consist of more than 1 server all remaining servers must be configured to connect to new data store.

 Reconfigure remainig server

On the server migrated in the previous step open folder “C:\Program Files (x86)\Citrix\Independent Management Architecture” and edit new dsn file – in my case fileXA65Small.dsn

 Remove the computer name from the WSID line.  The WSID line is not used.

Copy modified XA65Small.dsn to all farm’s member servers  into C:\Program Files (x86)\Citrix\Independent Management Architecture folder and follow update steps.

Open the command prompt and stop IMA service:

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

Type  dsmaint recreatelhc and press Enter. This command will recreate Local Host Cache file.

Start IMA service

Scenario 2

Summary

In this scenario we will migrate a data store for farm XA_65_LAB located in database XA65LAB on server XD01 into new database XA65LabNew located on the same server. Farm XA_65_LAB consist of 2 server XA01 and XA 02. Both XenApp servers are configured with the Controller and Session-host mode enabled.

Action plan is the following:

1. Take a backup of your exisiting datastore.
2. Restore content of data store to a new database.
   • Create empty database on target server
   • Restore a backup to newly created database
   • Create new dsn file pointing to the new database.
   • Run dsmaint config command
3. Reconfigure remainig servers
   • On remaining server create a new file dsn pointing to the new database.
   • Stop IMA Service
   • Run dsmaint config command
   • Recreate local host cache
   • Start IMA Service

Take a backup of your exisiting datastore.

Open SQL Server Management Studio and login with valid credentials and expand Databases node in the left pane

Right-click database XA65LAB node and select Tasks -> Back Up

In section Destination click Remove button to remove default backup file

 Click Add button and select Backup folder and type name for backup file.

Click OK

Wait for confirmation window.

Restore content of data store to a new database.

To start the database migration process we have to create an empty database. Open SQL Server Management Studio and login with valid credentials and expand Databases node in the left panel

Right-click Databases node and select New Database

Click OK and when new database is created and right-click new database XA65LabNew and seclect Task -> Restore -> Database

Select Device option and click Browse button

 Click Add and navigate to the folder where data store backup was saved and select latest backup file.

Click OK

In section Destination  select new database name XA65LabNew

Select Files in the left pane and in Restore As select database files for new database. Note: This setp is very important otherwise orginal database files will be overwritten !!!!

Click Options in the left pane and select Overwrite the existing database

Press OK

Database has been restored successfully.

On the XenApp server performing the function of data colector, a new Data Source Name (DSN) must be created for the new SQL database. To identify a server working as data colector run QFARM command from command prompt. Data colector will be marked with D as is shown below:

On server XA01 goto  Start -> Administrative Tools -> Data Sources (ODBC) and navigate to File DSN tab. Change the Look in to C:\Program Files (x86)\Citrix\Independent Management Architecture as shown below:

Click Add button to create a new dsn file. New dsn file must be pleaced in C:\Program Files (x86)\Citrix\Independent Management Architecture folder.

Click on SQL Server and click Next

Click browse and navigate to C:\Program Files (x86)\Citrix\Independent Management Architecture and type the name for new dsn file.

Click Next and Finish and configure the connection parameters as description and server name.

Click next when completed

 Click Next. If everything is configured correctly you will be switched to the next page of the connfiguration wizard as it is shown below

If there is a problem with connection go to Client Configuration, uncheck Dynamically determine port and enter the value for the static port number. Please remember that SQL Express is  listening on dynamic ports unless it is configured to listed on a specific port (standard or non standard). Please check how to configure SQL Express to accept remote conections. Additional information on troubleshooting SQL Server 2008 R2 ODBC can be found at http://msdn.microsoft.com/en-us/library/ms131415.aspx.

 When connection is established click Next. Change the default database to the database which will be used as a new data store

Click Next and Finish and test connection

In the next step IMA service needs to be configured to connect to the new data store. Open the command prompt and stop IMA service:

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

Type  dsmaint recreatelhc and press Enter. This command will recreate Local Host Cache file.

 Start IMA service

At this stage the migration of the server hosting data store is completed. Server XA02 must be configured to connect to new data store.

 Reconfigure remainig server

On the server migrated in the previous step open folder “C:\Program Files (x86)\Citrix\Independent Management Architecture” and edit new dsn file – in my case file XA65LabNew.dsn

Remove the computer name from the WSID line.  The WSID line is not used.

Copy modified XA65LabNew.dsn to server XS02 into C:\Program Files (x86)\Citrix\Independent Management Architecture folder and follow update steps.

Open the command prompt and stop IMA service:

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

Type  dsmaint recreatelhc and press Enter. This command will recreate Local Host Cache file.

Start IMA service

Scenario 3

Summary

In this scenario we will migrate a data store for farm XA_65_LAB located in database XA65LabNew on server XD01 into new database XA65DataStore located on the same server. Farm XA_65_LAB consist of 2 server XA01 and XA 02. Both XenApp servers are configured with the Controller and Session-host mode enabled.

Action plan is the following:

1. Take a backup of your exisiting datastore.
2. Restore content of data store to a new database.
   • Create empty database on target server
   • Restore a backup to newly created database
   • Run IMA Helper Tool to create dsn file and recreate local host cache
3. Reconfigure remainig servers
   • On remaining server create a new file dsn pointing to the new database.
   • Stop IMA Service
   • Run IMA Helper Tool to create dsn file and recreate local host cache
   • Start IMA Service

All steps related to backup and data base restore are the same as in scenario 2. Please folow instruction starting from scenario 2. When newly created database is updated with latest backup start IMA Helper Tool as described below.

 IMA Helper Tool

IMA Helper is designed to facilitate the process of running DSMAINT CONFIG on XenApp servers. The tool provides a Graphical User Interface for DSMAINT CONFIG to the XenApp Administrator.

Download IMA Helper Tool from Citrix Website and save it to the server folder. Open command prompt, stop ima service and run IMAHelper.exe

Click Create New DSN to create new connection file. ODBC window will be open

Open File DSN tab and create new dsn file using procedure described in scenario 2. I created XA65DataStore.dsn dsn file.

Enter valid credentials and press Run to execute dsmaint config command.

Wait for confirmation popup

Click OK and run RecreateLHC button

 Wait for confirmation popup

Start IMA service