What is the optimal hardware configuration for maximizing the performance of an Access Database?

What is the optimal hardware configuration for maximizing the performance of an Access Database?

I need to buy a server and I'm looking at getting a 4 core Xeon running at 3.4 GHz but I'm also seeing other processor options for 6 to 8 cores or higher (10+) cores.  I know that the Cache size is a factor and the memory speed is a factor but I really don't know exactly what to optimize?  The higher cores don't have as high a clock speed - the highest clock speed is 2.8 or 2.5 GHz.  

I don't know which elements truly matter to maximize the database performance.  

I'm planning to use an SSD drive - Samsung Evo Pro 1 TB - and I'm planning to use Windows Server 2012 R2.  

I am open to any and all suggestions.  

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Lee W, MVPTechnology and Business Process AdvisorCommented:
Access is a file based system.  Database size is a MAXIMUM of 2 GB.  Access itself is SINGLE threaded... meaning you can get all the cores you want, it won't use more than 1.  If you're doing significant reads and writes, you shouldn't be using Access.  You SHOULD be migrating this to SQL.  Even SQL Express can handle this better.
Exactly. The best you can do at the performance field is to remove Access from your plans. Ebentually use Access as a frontend and real database as a backend.

How many concurrent users are we talking about? How many databases? What data size? Knowing these values you may plan the hardware.

But maybe you are talking about single user access... In such case the faster hardware the better Access performance. SSD is highly recommended.

Concurrent users should use database engine which supports multithreading. SQL Express is not good option but higher SQL Server editions are OK. Visual FoxPro also supports multithreading and its main advantage is no client license costs. Of course, VFP is banned by large companies due to the end of support period (which does not affect the performance).
Access runs on your local PC.  It doesn't run on the server the way SQL Server and the other RDBMS's do.  As far as Access is concerned, the server is a place where files are stored and most companies don't even install Access on the file server since there is no reason to.

On your local PC, Access is certainly impacted by memory and cache sizes/speeds.  But it is also very dependent on LAN speed and you should never use an Access FE linked to a Jet/ACE BE on the server over a wireless connection.  Access is extremely vulnerable to blips and wireless networks have them all the time.  Use a hard wired connection.  If you can't then don't use Jet/ACE as the BE, convert to SQL Server as soon as you can before your database becomes corrupted.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Access <> Server.
Ultimately, for performance the backend should be migrated to SQL Server (or the free SQL Server Express Edition) and your question is about hardware optimizing for SQL Server
The front-end should be distributed to each machine running the application, and the server is irrelevant to that equation

If you are running Access through Terminal Services, then you need a question about optimal hardware config for a terminal server.

If you are running Access as the frontend and as the backend, what becomes important is the speed and robustness of the network, and not the capabilities of the server.
CharlieF2Author Commented:
Thank you to Lee W for pointing out that Access - being single threaded - can't benefit from multiple cores.  That is the type of useful information that I'm looking for.  Thank you all for taking a moment to share your ideas.  

I'm asking you to for a moment put aside the "Best Practice" of using SQL Server and just give me the best way to maximize Access's performance - let's assume that in the immediate short term the application cannot be migrated to SQL Server but we're hoping to maximize performance.  

Thank you!
If you are running Access as the frontend and as the backend, what becomes important is the speed and robustness of the network, and not the capabilities of the server.

Using Access as FE and BE, you will hit the limitations of Access's architecture long before you stress any modern server of any kind of configuration.  With a robust 1Gbps LAN, you'll hit Access's limits before thrashing the LAN, too.

Access does not play well with high-latency, high-jitter networks (i.e. most typical ADSL-based VPN's) But on a solid LAN with the architecture you are on, you'll be good for 20-35 users and no amount of money will get you reliably past that.
CharlieF2Author Commented:
Thank you Nick67 - however - FYI for everyone - the FE and BE are collocated to the same box and RDS is used for multi-user access.  Network issues are not applicable to this situation.
Dale FyeOwner, Developing Solutions LLCCommented:
each user should have their own copy of the front end.  sharing the front end across multiple users is a recipe for database corruption!
CharlieF2Author Commented:
Actually Dale Fye - a single FE is not exactly shared when using RDS and it works great - never had any corruption in 15 years with dozens of client installations.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"and real database as a backend."
Oh boy ... here we go :-(
Lee W, MVPTechnology and Business Process AdvisorCommented:
FYI for everyone - the FE and BE are collocated to the same box and RDS is used for multi-user access.

this is a KEY piece of information that, had you mentioned to start, would have altered my response and (and likely many others).  When asking a question, please provide ALL the information if you want a good answer.

NOW, you're asking how do I size an RDS server for use with an access database.  The FIRST thing we need to know is user count.  Multiple cores NOW may be useful since each user has their own thread for access.

FYI: I've worked with several access databases over the years - though none that were multi-user entirely on an RDS server.  Network shared databases, I've found, are HORRIBLY unreliable.  I've heard Access experts swear it's stable when done right - but I've also had "access experts" create databases that corrupt regularly... so if some experts are correct that it's stable, I have to surmise that Access CAN be stable when done correctly, but that it's VERY difficult to do it correctly so that few people actually can.

SQL, for all it's complexity, is INCREDIBLY stable in my experience.  Making it run optimally may require a SQL Expert, but using it will all but guarantee you don't have corruptions unless you have a disk fail.
CharlieF2Author Commented:
Lee, thanks very much for your comments - very useful - let me share more information - for the site that I'm specifically attempting to optimize a server for - they have between 5 to 10 concurrent users each day - with a possible peak number of current users between 10 and 16.  

The database is large - more than 1 GB.  

And please know that I have 15 years of multi user solutions entirely on RDS servers and I gotta hand it to Microsoft - it all works perfectly - even global variables.  I did a lot of research and use no bound forms - everything is read/written via ADO recordsets.  

We plan to migrate to SQL Server and have made many accommodations for it in our current design but it is a big project and for now I'm just trying to be as smart as possible with my hardware choices to eek out as much performance as possible.  

Any more advice - anyone?  So should I be looking at a 6 or 8 or 10 core E5 Xeon processor?  

Any other thoughts on cache size, bus speed, memory speed, use of SSD?  I have referred to the attached document and it is saying to use SSD and max out the clock speed but I want to really make the best choices here.  

Thanks again to you and everyone for their inputs!
Lee W, MVPTechnology and Business Process AdvisorCommented:
With 5-10 users, 16 max, I would be comfortable with a single hex core (12 thread) XEON processor.  When running particular queries or reports, you might end up loading a thread to 100%, but with so many other cores available, I wouldn't worry about 1 thread - more typical user activities shouldn't overtax a 12 thread capable system.   I'd probably want to ensure about 1 GB of RAM PER USER.

If I were speccing out an RDS server for what I'm understanding your needs are, I'd get something with 8 cores, 24 GB of RAM (I might make it more like 32 if money wasn't too tight) and a mirrored SSD (Intel preferrable; Samsung MAY be fine, but there's a possible (unconfirmed) issue with data corruption in some potentially unique environs using Samsung drives.  (https://blog.algolia.com/when-solid-state-drives-are-not-that-solid/)

AND I'd make sure the server was expandable - ability to add a SECOND matching CPU, RAM expandable to AT LEAST 64 GB.  (Lower end servers only support single processors and 32 GB RAM).

And OF COURSE, I'd make this virtual to take advantage of DR options and the potential high availability options that VMs offer..  Then, you can always get new hardware and Upgrade by just adjusting RAM allocation and CPU allocation and moving the VM.  And as a VM, DO NOT assign ALL the cores to it.  I'd start with 1/4 of the available cores and increase slowly.  This is because scheduling of the processors COULD negatively impact performance if the host or other VMs are need too many threads at any given moment.

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
CharlieF2Author Commented:
Thanks Lee W!
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
Microsoft Access

From novice to tech pro — start learning today.