Access to SQL Data Store conversion

Posted on 2007-04-02
Medium Priority
Last Modified: 2012-06-21
Hello Experts~
Per a previous recommendation I am planning on moving our Access data store to a SQL server in our organization.  The SQL server (Server3) does not have any Citrix components installed on it.  The data store currently resides on Server1 (our initial Citrix server) which is also the License server.  Server2 is another PS4.0 server in the farm.  I'm planning to move the data store to Server3 (SQL Server), the License server to Server2 and eventually retire Server1 altogether.

Does Server3 need to have any Citrix components installed on it to permit it to host the data store for the farm?  I've read over a number of posts on converting an Access -> SQL data store but all seem to suggest stopping/starting services on the destination machine that doesn't have (currently) any Citrix-related components on it.

It's quite possible I'm misreading something but any help or suggestions would be appreciated.  Thank you.
Question by:MutleyFDI
  • 4
  • 3
LVL 10

Expert Comment

ID: 18841135
SQL and Citrix are totally separate.  You do not need to have ANY Citrix components installed on the SQL server to convert a database from Access to SQL.  You pretty much have to run DSMAINT MIGRATE (Access credentials, then SQL credentials) on the command line, then run DSMAINT CONFIG (to tell Citrix to switch from the Access DB to the SQL server.  It's been a while since I've done it, but it's well documented here:  http://support.citrix.com/article/CTX677542&searchID=-1 (or simply Citrix Support article CTX677542)
LVL 19

Accepted Solution

BLipman earned 500 total points
ID: 18846543
                 </DSTDSN:dsn2> </DSTUSER:user2> </DSTPWD:pwd2>
                 [/PATCHINDEX:1 to enable] [/RECREATEINDEX]

dsmaint migrate /srcdsn:"c:\program files\citrix\independant management architecture\mf20.dsn" /srcuser:citrix /srcpwd:citrix /dstdsn:"~~path to new dsn~~" /dstuser:~~NewLogin~~ /dstpwd:~~NewPwd~~

With an Access datastore the default username and password is citrix citrix respectively.  Make sure to enter the full absolute path to your DSN and put it in quotes; it will fail otherwise.  Make your database shell and user on the destination server and a DSN to point to this new DB.  Then your migrate command will copy and verify that the DS is moved.  
On this server and then each additional server you must do DSMAINT CONFIG to point to the new datastore, restart the IMA service after and all should be migrated.  You will need to configure your new DSN on each server but migrate only on the first.  
Check your current activity in SQL EM and make sure you see the new connections as you go.  I would suggest making your database and log file for your SQL database start off at ~30MB instead of the initial value so as to avoid any autogrow activity.  

Author Comment

ID: 18847615
Thanks for the specifics on that.  Why doesn't the mf20.dsn appear in Data Sources (ODBC) from Adminitrative Tools?  The only DSN listed there (on both Servers) is IMADirectory which points to ..\imalhc.mdb.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

LVL 19

Expert Comment

ID: 18852232
It is a file DSN, those only show up if you browse to them in your ODBC tool (unless they live in the default location).  

Author Comment

ID: 18852512
Thanks for the clarification...I haven't used up my dumb questions quota yet, have I? :-)
Have created the new SQL db/dsn and will try the DSMAINT MIGRATE later tonight.  After the migration has occured and I run DSMAINT CONFIG I should be able to edit the MF20.dsn on each server and update them to point at the new db, is that correct?
Thanks for your continued help (and patience).

Author Comment

ID: 18853374
Thanks again for the help. Both DSMAINT MIGRATE and CONFIG ran successfully. IMA restart and all seems well.
Do you know if the DSMAINT MIGRATE command would permit the /DSTDSN: parameter to be entered w/UNC format? I opted to create new MF20.dsn's on each of the resp. servers (keeping the /DSTDSN: parameters local) but wonder if the DSN I created on the SQL server could've been used (or not).
LVL 19

Expert Comment

ID: 18858546
I don't think that is a very good idea, simple networking issues could cause you some headaches whereas keeping it locally makes it available at all times.  I just copy the file from server to server.  When I do this I always keep my last DSN so there is a MF20.dsn and a MF20a.dsn.  The lowest letter is the most current DSN.  

Author Comment

ID: 18858793
Good approach.  I thought there might be a hitch (or worse) referring to a remote DSN vs a local DSN...thanks for confirming!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

After several days of searching and hunting for limited documentation, I wanted to share this guide to hopefully save someone the hassle of trying to figure this out on their own. I have tested this on Xendesktop 7.1 and PS 4.5 running simultaneous…
Citrix XenDesktop 7.6 Citrix Policies Disable Peripherals
How to install and configure Citrix XenApp 6.5 - Part 1. In this video tutorial we have explained step by step installation of Citrix XenApp 6.5 Server on Windows Server 2008 R2 is explained in this video. We have explained the difference between…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question