Citrix – Migrating an Access data store to SQL

I recently migrated a local Access data store to SQL and I didn’t find a lot of detailed instructions on the net, so here’s my tutorial.

First, determine which server holds your data store.  This article helped (CTX105257).  I basically looked on a couple of server at the registry value HKLMSOFTWARECitrixIMAPSServer.  This value was my data store holder.

First step in the migration is to create a DSN file for the new SQL connection.  This can be done in wordpad and should contain this:

[ODBC]
DRIVER=SQL Server
UID= <user id>
LANGUAGE=us_english
DATABASE= <database name>
WSID= <Citrix server name>
APP=Citrix IMA
SERVER= <SQL server name>
Description= <description of connection>

Save the file as a *.DSN extension.  Citrix stores its DSNs in the IMA folder.  After creating this DSN, I placed it in c:program filescitrixindependent management architecture.  I read that some people recommend renaming the existing from mf20.dsn to something like mf20old.dsn and then naming the new DSN to mf20.dsn.  In my case, the original DSN file was not named mf20.dsn, so I just named my new DSN to mf20.dsn.  In any case, it is wise to backup the existing datastore in case something bad happens.  Run this command:

DSMAINT BACKUP c:

This will backup the existing datastore to a MDB (probably mf20.mdb) at C:. Next, we need to run “DSMAINT MIGRATE” to move the data from the local data store to SQL.  My command looked like this:

DSMAINT MIGRATE /srcdsn:”C:Program FilesCitrixIndependent Management Architecturemf20old.dsn” /srcuser:citrix /srcpwd:citrix /dstdsn:”C:Program FilesCitrixIndependent Management Architecturemf20.dsn” /dstuser:userid /dstpwd:userpw

THe default username and password for a local access data strore is citrix/citrix.  The dstuser and dstpwd should be your SQL username and password.  Don’t forget that this user needs dbowner priveleges to the database for Citrix to work properly.  The command will take the existing data in the local access data store and migrate it to SQL. Once ran, we see the following migration status box:

After the migration is complete, a prompt to compare the data is offered.  I selected Yes.

The comaprison will begin:

If everything goes smoothly, a confirmation will show in your command window:

Migration Complete

The next step is to point this server to the new data store.  This is done with the DSMAINT CONFIG command.  This will point the IMA service to use the new DSN.  My command looked like this:

DSMAINT CONFIG /user:userid  /pwd:userpw  /dsn:C:Program FilesCitrixIndependent Management Architecturemf20.dsn”

After execution, a message shows that changes will take effect after stopping and starting the IMA service.  I stopped the IMA service, ran DSMAINT RECREATELHC and then started the IMA service.  This way I knew I was rebuilding my local cache from my new datastore.  After the IMA service started successfully, it was then time to point my other farm members to the new datastore.  If you don’t point them to the new data store, they will continue to access the data store indirectly through the server we just changed.

Use the DSN we created above, but change the WSID to match each server  it is copied to. Once that DSN is copied to that server, run the DSMAINT CONFIG command to use the new DSN, then stop/start the IMA service.

I’ve read that if there are a lot of servers in your farm, the PsExec utility is a great way to execute these commands remotely on your servers.  I didn’t have too many, so I used RDP to run the commands on each server.

Advertisements