I'm overhauling the entire IT infrastructure for a small office. They had a Dell T410 doing nothing but serving as a Win2k8 file share, so I figured I could replicate its functionality on an unused desktop running Vista while I took the server down for a rebuild.
That's all fine and good, but one thing they were doing was simultaneously accessing an Access database off of the share. It worked, slowly, while on the T410 but once I migrated it to the temporary server only one user can use it at a time-- additional users drag it to an unusable halt. There are 30-60 second pauses for queries. Everything else runs well enough.
This was something my predecessor had to deal with, and the office ladies are saying he had done something on the server to lessen the query time to 5-10 seconds. They instantaneously run in single-user mode, but there is no documentation of what he did to mitigate this for multiple users. The server had no RAMDISK, RAID setup or anything special from what I could tell.
First I thought the hard drive might be slow so I set up a RAMDISK and am hosting that one particular file on it. Single-user access is now blazing fast but multi-user access is still just as slow. Next I thought the network card might be a bottleneck but I've watched the resource monitor and nothing is anywhere near capping out. My next thought was to blame hardware/Vista but it's a Dell somethingorother Core 2 Duo with 3GB of RAM, and Vista Business is the only OS I have a spare license for. Should be enough for the job at hand.
I've seen a lot of similar complains about poorly-designed Access databases being unusable in multi-user environments, followed by suggestions to split the database into front-ends and back-ends but I'm not willing to spend much time tweaking Access considering my next project after rebuilding the server is redesigning the entire back-end in SQL Server. Plus, the last guy was able to semi-fix the problem without overhauling anything (this much I do know). In the meantime though, this is killing productivity.
What might have changed between the old server and the new? Are there any simple things I can change to bring the query time back down to a reasonable delay?