Migrating a Citrix datastore ( access, sql express ) to Microsoft SQL ( sql , sql express )

I've found this post on one the forums and the procedure works like a charm !

Migrating a Citrix Access data store to Microsoft SQL Server 2005

The following instructions outline the process of migrating a Citrix MetaFrame XP Access data store to Microsoft SQL Server. Please note, this process requires the Citrix servers to be restarted and adequate backup measures should be taken prior to migration in case of failure. citrixInformation in this article has been taken from the following sources: • How to create a database for use as a data store using SQL Server (http://support.citrix.com/kb/entry!default.jspa?categoryID=118&entryID=3412)• Advanced Concepts - Farm Maintenance (http://support.citrix.com/kb/entry!default.jspa?categoryID=118&entryID=2188)
To migrate a Citrix Presetation Server data store to Microsoft SQL Server:

I. Create the target database on the SQL server:

1. Start SQL Enterprise Manager.

2. in the left-hand pane, expand the tree until you reach folder level.

3. Right-click on the databases folder and select New Database

4. In the dialog box, enter a name into the Name textbox and click OK. The name entered is not important.

5. Expand the security folder.

6. Right-click Logins and select New Login

7. A dialog box appears with the General tab displayed. In the Name textbox, enter a username for the new login. Make a note of this username, as you will need it during the migration procedure.

8. In the Authentication section of the General tab, click SQL Server Authentication and enter a password. Make a note of this password, as you will need it during the migration procedure.

9. In the Defaults are of the General tab, change the database to the name you specified in step 4.

10. Click the Database Access tab. In the Database list, select the database name specified in step 4.

11. In the Database Roles list, select DB_Owner. Leave the other selected roles checked. 12. Click OK. You are prompted to confirm your password at this point. 13. The database isnow created. Exit SQL Enterprise Manager.

II. Create a new DSN file pointing to the database created above:

1. Start ODBC Data Source Administrator.

2. Select the File DSN tab, and click Add.

3. In the driver list, select SQL Server and click Next.

4. Click Browse and navigate to the %PROGRAMFILES%\Citrix\Independent Management Architecture folder. Enter a filename for the DSN. Click OK then click Next.

5. Confirm the information entered, then click Finish.

6. The Create a new Data Source to SQL Server dialog, enter a description if required.

7. In the SQL Server box, enter the name of the SQL Server on which the database has been created. Click Next.

8. Click With SQL Server authentication using a login ID and password entered by the user then enter the Login ID and password of the database user created above. Click Next

9. Check Change the default database to and select the name of the database created above. Click Next, then click Finish.

10. In the ODBC Microsoft SQL Server Setup dialog, click Test Data Source button to ensure connectivity. Once finished, click OK to finalise DSN creation.

III. On the host server, start Command Prompt and change to the %PROGRAMFILES%\Citrix\Independent Management Architecture folder.

IV. Execute the dsmaint migrate command with the following parameters:

dsmaint migrate /srcdsn:path and name of existing DSN /srcuser:leave blank /srcpwd:leave blank /dstdsn:path and name of new DSN /dstuser:new DSN login name /dstpwd:new DSN password

V. Once the migration procedure has finished (it can take anywhere from a few seconds to a few minutes), execute the dsmaint config command with the following parameters: dsmaint config /user:new DSN login name /pwd:new DSN password /dsn:path and name of new DSN ds

VI. Stop and restart the IMA service. After the IMA service has restarted, all other servers begin accessing the new data store through the host server. t Please note: Restarting the IMA Service may cause the SNMP service to Dr Watson if SNMP is enabled. This is benign and can safely be ignored.

VII. Copy the new DSN file to all other servers in the farm.

VIII. Execute the dsmaint config command on each server with the same parameters as above to establish a direct connection to the new data store.

IX. Stop and restart the IMA service on all remaining indirect servers in the farm.