I recently needed to migrate our Citrix data store from a MS Access database to Microsoft SQL Server 2005 (32-Bit), this document shows the steps I went though.

Step 1
Before starting I created a backup of my data store take a look at http://www.geekshangout.com/?q=node/32 for instructions

Step 2
I created a new database and user on the SQL server

Step 3
On one of the Citrix Servers I created a file DSN that pointed to my new SQL database

I saved this DSN as SQL.dsn in C:\Program Files\Citrix\Independent Management Architecture\

Step 4

I ran the below command to begin the copy of the data in the access database to the SQL database

dsmaint migrate /SRCDSN:"C:\Program Files\Citrix\Independent Management Architecture\MF20.dsn" /srcuser:citrix /srcpwd:citrix /DSTDSN:"C:\Program Files\Citrix\Independent Management Architecture\SQL.dsn" /DSTUSER:citrix /DSTPWD:citrix

 

But I received an error

“An unknown error occurred with the destination data store while starting the migration process”

In my case this was because I had forgotten to give my SQL user any permission to the database.

After correcting this I ran DSMaint migrate again but now received the message

“An unknown error occurred while migrating the table, please verify

the destination datasource is clean.

[Microsoft][ODBC SQL Server Driver][SQL Server]The INSERT statement conflicted with the FOREIGN KEY

constraint "FK__INDEXTABL__nodei__300424B4". The conflict occurred in database "Citrix_DS", table "d

bo.KEYTABLE", column 'nodeid'.”

A quick google gave me the solution http://support.citrix.com/article/CTX112866

I had to stop the IMA service before I got follow these steps, I then deleted all the tabled in the SQL database and ran Dsmaint migrate again and this time everything when fine

The migration was successful.

The comparision finished successfully.

The destination data store matches the source data store.

Step 5
I then copied the SQL.dsn file to all of the Citrix Servers and ran

dsmaint config /user:citrix /pwd:citrix /dsn:\\ctxdev\c$\Program Files\Citrix\Independent Management Architecture\SQL.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.

 

After which I restarted the IMA service on each server

Notes:
– Restarting the IMA service does not disconnect any users
– I would try and test as much of this as possible before doing in a live environment