Share this post

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

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:

dsmaint-backup

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:

dsmaint - zero

 

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

sql list

Right-click Databases node and select New Database

sql new start

sql new db

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

sql db small

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:

ds - odbc1

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.

ds - odbc1a

Click on SQL Server and click Next

odbc 2

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

odbc3

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

odbc1

Click next when completed

odbc5a

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

odbc6

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.

odbc5

When connection is established click Next

odbc7

Click Finish and test connection

odbc8

odbc9

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

cmd admin

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

dsmaint 2

 When finished click Yes on the Dsmaint confirmation popup

dsmaint comp

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

dsmaint 3

 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:

ima stop

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

dsmaint config

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

dsmaint lhc

Start IMA service

ima start

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

dsn edit 1

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

dsn edit 2

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:

ima stop

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

dsmaint config

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

dsmaint lhc

Start IMA service

ima start

 

 

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

sql list

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

sql backup 1

In section Destination click Remove button to remove default backup file

sql backup 2

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

sql backup 3

Click OK

sql backup 4

Wait for confirmation window.

sql backup 5

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

sql list

Right-click Databases node and select New Database

sql new start

sql new start 2

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

sql restore 1

Select Device option and click Browse button

sql restore 2

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

sql restore 4

Click OK

sql restore 5

In section Destination  select new database name XA65LabNew

sql restore 6

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 !!!!

sql restore 7a

sql restore 7

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

sql restore 8

Press OK

sql restore 9

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:

data colector

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:

ds - odbc1

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.

ds - odbc1a

Click on SQL Server and click Next

odbc 2

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

dsn s 2

 

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

dsn s 2 1

Click next when completed

odbc5a

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

dsn s 2 3

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.

odbc5

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

dsn s 2 3

Click Next and Finish and test connection

dsn s 2 4

odbc9

 

 

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:

ima stop

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

dsmain config s2

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

dsmaint lhc

 Start IMA service

ima start

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

dsn edit 20

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

dsn edit 21

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:

ima stop

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

dsmain config s2

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

dsmaint lhc

Start IMA service

ima start

 

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

helper 1

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

helper 2

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

helper 4

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

helper 4a

 

Wait for confirmation popup

helper 5Click OK and run RecreateLHC button

helper 6 Wait for confirmation popup

helper 7

Start IMA service

ima start

 

 

Related Posts:

How to configure SQL Express 2012 to accept remote connections

Citrix Multistream ICA explained

XenApp 6.5 Performance Counters

XenApp 6.5 load values explained

XenApp server – drain mode maintenance