Access to SQL Data Store conversion

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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: (or simply Citrix Support article CTX677542)
                 </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.  

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MutleyFDIAuthor Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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).  
MutleyFDIAuthor Commented:
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).
MutleyFDIAuthor Commented:
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).
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.  
MutleyFDIAuthor Commented:
Good approach.  I thought there might be a hitch (or worse) referring to a remote DSN vs a local DSN...thanks for confirming!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.