Posted - 03/30/2012 : 12:35:07
| Hello all,
I need some advice since searching Google and forums on this question gives a plethora of related but not useful info.
Due to various reasons (budget and time) we had a remote user develop an app in MS Access using Linked tables back to our SQL 2008 db. This is supposed to act as an interface only to read and update simple tables on the SQL box. This access db is then shared among users in the US and UK thanks to CITRIX.
Where I need input/advice is on the location of where the file should be stored to minimize impact to the SQL server, which happens to also be running web and other services (Business Objects 3.2). Single install on one server was due to resource limitations. This is also a VM (VM4) with NAS based drives (distinct drives for file/data/log/os/swap/etc.).
Where is the optimal location for the MS Access file?
1 - On the SQL server on its own drive
2 - On a shared network resource on the NAS/SAN
3 - On the CITRIX server front end server/core server
This hopefully will go away in late 2012 or next year once we develop the functionality for this thing in the native app it is using data from. In the meantime, I am trying to wrap my head around what is wrong with this and why (we are using option 1 now). I've been a partial DBA since the early 90's and cut my teeth on Paradox and Access, so my gut tells me this is a bad scenario, but I need hard details and I think this might fall under the realm of "no one would ever do this so we can't provide answers" - at least it does to Google...
TIA for any insight.