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.
Before starting I created a backup of my data store take a look at http://www.geekshangout.com/?q=node/32 for instructions
I created a new database and user on the SQL server
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\
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.
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
– 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