• 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 datastore:

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

    The disadvantages to using SQL Server Express for the datastore:

    • If the XenApp server hosting the datastore 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 datastore:

    • 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 datastore
    • This VM will use SQL Server 2008 R2
    • Static IP Address

    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 datastore
    • Static IP Address

    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 datastore
    • Static IP Address

    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
    Figure 1

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

    Figure 2
    Figure 2

    It is a Best Practice to always backup the datastore 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
    Figure 3

    BEFORE YOU START THE MIGRATION, make sure the account 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:

    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
    Figure 4

    Login using the necessary credentials (Figure 5).

    Figure 5
    Figure 5

    Expand Databases in the left column (Figure 6).

    Figure 6
    Figure 6

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

    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
    Figure 8

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

    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
    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
    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
    Figure 12

    Click the Add button (Figure 13).

    Figure 13
    Figure 13

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

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

    About Carl Webster

    Webster is a Sr. Solutions Architect for Choice Solutions, LLC and specializes in Citrix, Active Directory and Technical Documentation. Webster has been working with Citrix products for many years starting with Multi-User OS/2 in 1990.

    View all posts by Carl Webster

    24 Responses to “Learning to Migrate a Citrix XenApp 6.x Data Store from SQL Server Express to SQL Server”

    1. Mike Says:

      Hi ,

      We would like to Migrate a Citrix XenApp 6.x Data Store from SQL2008 Cluster Server (the virtual SQL name) to SQL2008 Virtual Server with same IP address and server name.
      Do the we need to update the .dsn and lhc on the targeted SQL2008 Virtual server?




      • Carl Webster Says:

        The DSN only has the SQL Server name and database name. As long as those don’t change, I don’t believe you will need to update the DSN. I would restart the XenApp servers so they can rebuild their LHC and verify the connection to the database still works.



    2. Alex Says:

      Carl, great article. I have a question regarding use of the qfarm cmd to test connectivity to the new datastore. In your lab you have migrated the datastore from a server called ‘xenapp65-1’ to a server called ‘sql’ yet the qfarm screenshot at the end of your article still shows ‘xenapp65-1’ as holding the datastore. My understanding of qfarm is that it will not show non farm servers in it’s output therefore will be of no use if the datastore has been moved to a dedicated sql server. Is that correct? Not trying to pick holes, just trying to understand where I might be going wrong as my servers seem to not pick up the new datastore.


    3. Brian Mooney Says:

      This documentation is fantastic. I do have one question though. This outlines a SQL migration from Express which is local to a remote SQL instance. What if we wanted to migrate from a SQL Server to SQL Server? Is the process still the same or similar?


      • Carl Webster Says:

        Should be the same. Or you could use the old backup and restore (from SQL Studio) the database to move it to a new server.



    4. Leo Fernandez Says:


      Great documentation, probably the best one in the web. Question:

      How about if you XenApp farm is provisioned? do you put in private then do the changes?

      Is it better if I backup DB then restore in the new SQL servers, then point my ZDC which is in private mode using ODBC to the new server, without using dsmaint /migrate, what are you thoughts?

      Thanks for any feedback


      • Carl Webster Says:

        If using PVS 6.x or 7.x use Maintenance mode.

        The backup and restore method works but if you require a Citrix supported method, use DSMAINT.

        Thanks for the kind words.



    5. Mike Says:

      Just wonder if you can just edit the dsn file for the new db location. I’m only moving the db to a 64-bit server. Same version, same OS (except 64 vs 32 bit), even same subnet.


      • Carl Webster Says:

        I would think that is possible. I would make a copy of the original DSN file just to be safe.



    6. simsum Says:

      Hi and Thank you very much, realy worked as a charm. Big THX!
      I migrated from one ms-sql-express to another (separate one)…


    7. Sander van den Brom Says:

      Thank you very much Carl!
      In my humble opinion this the best guide for XenApp database migration.

      Keep up the good work.



    8. Tom Says:

      I need to move to a new server both running SQL 2008…Is there much diff in the process? BTW awesome job on the doc.


      • Carl Webster Says:

        Yes, same process applies. I know people who do SQL stuff like dropping a DB and then attaching it to a new server. BUT, the method I cover is supported by Citrix. If that is important to you, then do the method I document.

        Thanks for reading.



    9. vandamage Says:

      Hi, little typo in the part you describe to dsmaint config, you point to the 64bit program files directory, ima still checks the 32bit part so that wont really work at all for those readers who don’t know what they are doin’ cheers mate 😉


    10. David Says:

      Today it works! Excellent Article, thank you very much.



    11. David Says:

      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… is all I get, do I need to subscribe or something to get the rest of this doc?


    12. shivakumar biradar Says:

      thanks, it really helps.. what is the best way and best practices to migrate legacy PS4.5 to the XenApp.


    13. Preisser Günter Says:

      a really great post thanks for your effort.

      i have a question can you deeper explain why i should change from sql express to sql server in sight of replication WAN new zones and so on logging database
      there are no edocs or ctx artikels

      its for me not so clear

      many thanks


    Leave a Reply