Project to replace desktops with a server

Question

This may sound crazy, I've gotten a few horrified looks when explaining it to others before, but here's the setup right now:

We have A a group of desktops dedicated to running reports all day, using a bunch of Access DBs (over 100) that currently reside on a file server (so the server holds the file, but the PCs do all the work). The desktops use the scheduler to activate the DBs and the DBs use an "autoexec" macro and VBA Modules to run the reports/processes in them (some have multiple reports & processes, some have just one). The desktops take roughly 6 hours to complete the processing each day.

The project I'm trying to write up is to move all of it to a single server, hopefully to save time and definitely to free up the desktops for use.

Here's what I'm trying to establish: What kind of server We can look into getting so that We could just move the DBs from the PCs to the server, schedule a run-time on the server, and it will run them like normal (but faster)? Any idea of the "specs" that would be enough? I wish I could be more specific, but I do software, not hardware, and every time I ask anyone from the IT Dept they respond with "1st of all, stop using Access!" and it goes downhill from there...

Answer 1

Yes, 1st of all, stop using Access! (for this task) and ... What kind of server do you need?: if you plan to move the access DBs to the server, you only are moving CPU time from desktops to the server. Nothing more will change. So, prepare a server with many cpus.

If you consolidate all those access databases in a sql server database, perhaps, you can take advantage of stored procedures, views, etc to balace the executing process between database and the client program.

That client program could reside in another server and execute the logic that a sql database can't do. SQL Server have reporting services that could help you.

Answer 2

Since we don't know how you procuded these Access databases and why you need so many, I can't comment on how wise it is to do that job with Access.
Let's assume you have good reasons and can't do otherwise, although there is probably a lot to be done there to improve your situation.

1. Do the data crunching on the machine that hosts the database files themselves.
   Instead of moving all data through the slow network, doing the work where the db resides should be faster.

2. Compact the databases before your do your reporting. This will reduce their size and fragmentation. Look at the /compact command line switch.

3. Do you know exactly which queries are slowest? Measure each query and find out which ones take the most time, then optimise them!

4. Once you know which queries need optimisation, make sure your database indexes are optimum for them!

5. Same with the SQL queries and the VBA code: are you sure you can't optimise things more?

6. If during the data crunching your databases don't need to be accessed by anyone else, open them in exclusive mode, again using the command line /excl.
   This makes Access generally faster because it doesn't have to lock stuff as much.

7. Move all your data into SQL Server, or MySQL or PostgreSQL to do your data crunching.
   You can still link your Access client front-ends to these databases.