Score:0

MS Access database across small company LAN: NAS vs virtualized Windows Server

jp flag

I'm currently developing a small sized Windows application. For compatibility reasons, I'm limited to use MS Access as DB "backend" (migration to SQL Server planned but not doable yet).

In these days I have to take a decision. I have two options:

  1. Host MS Access files on NAS (Synology);
  2. Host MS Access files on a virtualized Windows Server (the hardware is a bit aged).

Which solution is better, in terms of reliable and fast database connection? I know it's difficult to give a 100% guaranteed answer with so little informations, so which tests should I perform to take my decision? Does someone of you have already faced a similar situation?

Thank you in advance.

Jevgenij Martynenko avatar
us flag
What is the size of the DB file? Number of concurrent users? Why SQL Server can't be used now? The performance will depend on your network speeds, and hardware specs of each option (disk type, network, RAM). But if the file is small and number of users is low, it might make no difference at all. The problem with Access file is usually not speeds, but locks and file corruption
FrankIsNotNothing avatar
jp flag
@JevgenijMartynenko thank you for your intereset. Size of databases files is between few KB to 20 - 50 MB. The NAS is a Synology rs2418rp+ and it should have SSD disks and RAID; the virtualized server it's a bit older than NAS (I don't have detailed specs at the moment). They're both connected via 10 Gbps LAN. Estimated concurrent users number varies between 5 to 20. Connection to these dabatases files is done via a .Net application (users don't use nor see Access files).
Jevgenij Martynenko avatar
us flag
I don't think you are going to get any performance issues because of the disk/network. Access DB files can be slow by design, because file locks and computations are done client-side. So network/disk will probably not going to be your bottleneck. I'd highly recommend to migrate to free version of SQL Server ASAP
FrankIsNotNothing avatar
jp flag
@JevgenijMartynenko Today I made a test with LAN Speed Test: it seems that the NAS is actually a bit faster than the virtualized server, but I know I'm just comparing read/write speed. Do you think I'd get any advantages installing SQL server express on an old PC (3rd gen i5, 12 GB RAM 1600 MHz, SSD)? Would this be better than the current MS Access-based solution?
FrankIsNotNothing avatar
jp flag
@JevgenijMartynenko I mean: for newer client pc, having to rely to an old pc would decrease performance compared with the MS Access based solution? Is a SQL server solution always preferable? Forgive me, I'm totally a newbie.
Jevgenij Martynenko avatar
us flag
I believe the hardware mentioned will be more than enough. SQL Server is much more reliable than Access DB files. As for performance, Express Edition will not be able to utilize more than 2 GB of RAM anyway. So 6 GB for the SQL Server machine would be more than enough (2 GB for Windows OS, 2 GB for file cache, 2 GB for SQL). Check other Express Edition limitations here https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15#Cross-BoxScaleLimits
Jevgenij Martynenko avatar
us flag
To add a grain of salt... please understand that migrating from Access DB to SQL Server will require your application to be reconfigured and its code updated. Also, managing SQL Server might require a bit more effort and knowledge. The benefits you will get are reliability and performance
FrankIsNotNothing avatar
jp flag
@JevgenijMartynenko Thank you for your support. I'll prepare that SQL server machine and give it a try, I think that's the best way to achive reliability and performance.
FrankIsNotNothing avatar
jp flag
@JevgenijMartynenko I come back here just to thank you for recommending me to adopt SQL Server. I'm migrating all the Access files to SQL Server in these days, and performing some first try I'm astonished by the performances I get - best part is, I have not yet optimized my application code! So, thank you.
Jevgenij Martynenko avatar
us flag
Happy to help :)
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.