Question MS Access Server -- i9-9900K Dual-Channel vs i7-7800X Quad-Channel

[MrN1ce9uy]

For a 24/7 MS Access Server, which is better? I know databases take advantage of system RAM, so it seems like quad-channel would be best. But I also don't know much about MS Access.

Will be 64GB of system RAM (or more) to allow a 10-40GB database to be accessed by 1-4 users. Can MS Access run multiple instances? Will it need enough RAM to hold the database multiple times over?

*Edit: I just remembered it will have 2x PCIe NVMe SSD's + a GPU for video editing. I think the extra PCIe lanes make my case for the i7-7800X.

 

[rgd1101]

ms access server? you mean sql?

 

[Deleted member 14196]

MS access is for personal use I think you want SQL Server or better. MS access is a tinkertoy. MS access is not intended for multiple users. Let alone a 40 GB data base LOL

 

[USAFRet]

For a 24/7 MS Access Server, which is better? I know databases take advantage of system RAM, so it seems like quad-channel would be best. But I also don't know much about MS Access.

Will be 64GB of system RAM (or more) to allow a 10-40GB database to be accessed by 1-4 users. Can MS Access run multiple instances? Will it need enough RAM to hold the database multiple times over?

*Edit: I just remembered it will have 2x PCIe NVMe SSD's + a GPU for video editing. I think the extra PCIe lanes make my case for the i7-7800X.

An Access db can be accessed by multiple simultaneous users. Not too many, but more than one.
However, the system requirements depend a LOT on how this thing is built.

Best practices indicate a backend table structure on "the server", and front ends living on the client systems, accessing that backend db.
How is yours built?

 

[MrN1ce9uy]

ms access server? you mean sql?

It's MS Access. And this person wants to use it as a server that can allow a few clients to connect to it.

An Access db can be accessed by multiple simultaneous users. Not too many, but more than one.
However, the system requirements depend a LOT on how this thing is built.

Best practices indicate a backend table structure on "the server", and front ends living on the client systems, accessing that backend db.
How is yours built?

Good question. It's not mine and I don't know much about it. That's basically what I had already told this "customer". That the performance of MS Access mostly relies on optimizing the database itself, which is an art in and of itself. He just needs a system to host the database and wants it to be the best it can be.

He's mentioned wanting to eventually run 5 PCs that could simultaneously run routines on the server’s single database.

Should I be leading him towards a migration to SQL Server?

 

[USAFRet]

SQLServer is obviously a lot more robust. But also requires a lot more infrastructure.
Access can be run on the client systems.

"10-40GB database "...you do realize that MS Access has a 2GB file size limit. Right?
Obviously, this can be worked around with multiple linked db's. But at "10-40GB", you're well into needing SQLServer, and all that entails.

 

[MrN1ce9uy]

He said it's currently 10GB and he expects it to reach 40GB over the next 10 years.

He did say he's having to wait on one process to finish before he can start another. He wants to make that go faster.

Is SQL Server the best solution for him? I'm sure he will have some learning to do or will have to get someone to set it up for him. It's just not my area and he lives across the country from me.

 

[USAFRet]

Access is a great tool, for what it is.
But it is really easy for the uninitiated to get in over their head.

 

[USAFRet]

He said it's currently 10GB and he expects it to reach 40GB over the next 10 years.

He did say he's having to wait on one process to finish before he can start another. He wants to make that go faster.

Is SQL Server the best solution for him? I'm sure he will have some learning to do or will have to get someone to set it up for him. It's just not my area and he lives across the country from me.

It cannot be 10GB for a single Access mdb. So either he is mistaken, or it a a very complex setup.

"He did say he's having to wait on one process to finish before he can start another. He wants to make that go faster."

That sounds like he needs to hire an actual database person, to optimize things.

 

[Deleted member 14196]

Access is a great prototyping tool. But I would never use it for a professional database for any reason

 

[USAFRet]

Access is a great prototyping tool. But I would never use it for a professional database for any reason

I have, to very good results.
But you have to know its limitations, and work within them.

 

[Deleted member 14196]

I think you’ll find the limitations are too restrictive. And yes please hire a DBA Who will then talk sense

 

[MrN1ce9uy]

Thanks to both of you for the info.

While we're here. If you were running an SQL Server with said database size, which system would you prefer? How much does quad-channel memory help databases? Wouldn't that be better than dual-channel with a faster CPU? I wouldn't think the CPU will be utilized nearly as much as the RAM.

 

[Deleted member 14196]

For best performance I’m sure the quad channel is what you’re going to want to go with.

 

[USAFRet]

Thanks to both of you for the info.

While we're here. If you were running an SQL Server with said database size, which system would you prefer? How much does quad-channel memory help databases? Wouldn't that be better than dual-channel with a faster CPU? I wouldn't think the CPU will be utilized nearly as much as the RAM.

Impossible to say either way.

How is the thing built?
What is it doing?

You could have a well optimized 10GB db that would run on a 10 year old laptop.
Or a badly optimized one that struggles on a current i7-9990 and 64GB RAM.

But quad vs dual channel RAM probably matters less than the amount of RAM, and which specific CPU.

 

[Deleted member 14196]

Here’s a decent article about SQL Server performance and what’s recommended

https://sqlperformance.com/2015/03/system-configuration/recommended-cpus-sql-server-2014

And I agree that you should probably have some professional database guru to help in the design and optimization of the database

Also because SQL Server is pretty expensive you can use SQL server express for free up to a 2 GB database limit before upgrading. At least that’s how it used to work

 

[MrN1ce9uy]

So he was mistaken, or mispoke, or I misheard.

What he just told me, "My database is SQL. It’s so big, it blew by the Access limits. All the routines are in VBA through run through Access. "

 

[mdd1963]

Microsoft's SQL Express is free, w/ database size limited to 10 GB)...
https://www.microsoft.com/en-us/sql-server/sql-server-2017-pricing#ft2
SQL Std is NOT cheap....(won't even discuss SQL Enterprise costs)...

Seems as though putting it even on a single server will run at least $2800 or so, unless you feel purchasing 2 core pack increments at $800 each is a better option

 

[USAFRet]

So he was mistaken, or mispoke, or I misheard.

What he just told me, "My database is SQL. It’s so big, it blew by the Access limits. All the routines are in VBA through run through Access. "

OK.
SQLServer backend, and Access front ends. Quite common config.

From that description, the SQL is just the raw table structure, and Access has all the brains in its VBA.

Ideally, you'd have an Access front end on each users PC, all accessing the same SQL backend.
No need for multiple people trying to use the same Access mdb at the same time.