NAS Advice for MSAccess DB Backend

[mcwhorter]

I'm looking to buy a NAS to store my MS Access 2010 database backend for my small business.

There are 10 client pc's needing to connect to it. The database itself is relatively small, less than 10mb at this time.

I want a single or 2 bay NAS. I dont need 4TB of space. I was thinking of adding a 80 or 160GB SSD drive. Would there be any speed benefits with an SSD in a NAS? Are the speedy access time of SSD's realized when used in a NAS?

Again, I need to share only this small database and maybe a couple training videos, so 80GB is all I need. No music library or dvd libraries here...





Any recommendations?

 

[thiggins]

I wouldn't bother with SSD's in the NAS. Network protocol overhead will wipe out any speed advantage.

With databases, you might want to experiment with Oplock settings. If I remember correctly, QNAP NASes let you set Oplocks, folder by folder.

Check the TS-219 series.

 

[teknojnky]

readynas has oplocks in the advanced share settings

in general though, NAS are not a good solution for database files.

 

[mcwhorter]

If not NAS, then what?

The Access database that will reside on the NAS is just the backend tables. The Frontend (on each client) will contain all of the forms, queries, etc...

I thought it would be better to use a NAS rather than simply share a folder on one of the office PC's.

We're not running a Domain server or Active Directory, just a simple Workgroup.

If sharing a folder on an office PC will be sufficient, I suppose I could just go that route and back it up like I would the NAS. That would also save us a few hundred bux.

 

[thiggins]

What have you been using up until now?

If you are not constantly hammering on the database with all 10 users simultaneously I would think you would be fine with a decent NAS.

teknojnky- can you elaborate why NASes are not a good solution for databases?

 

[teknojnky]

can you elaborate why NASes are not a good solution for databases?

latency and file locking (and caching, ie oplocks) issues.
Often file databases use temp files to lock the database, the constant creation/deletion of these lock and other temp files causes problems.


see for example, quickbooks on nas

The best way to run a database on a nas is via mysql or similar running as a service on the nas, not dropping an access/sqlite/whatever database file and trying to access it simultaniously with multiple users over CIFS/SMB.

But obviously managing a mysql server is quite a step up from a simple access database.

It may work, but you will want to keep constant hourly/daily backups in case of corruption.

 

[thiggins]

Thanks, tekno. Would he have any better luck using a Windows PC to share the database?

 

[teknojnky]

hard to say, but in my limited experience, windows file and sharing does work better than linux based nas's, but I am sure it depends on the database and the load (simultaniously accessing users). At some point a local/file-based database will need to hosted on an actual database server (sqlserver/mysql/etc).

In any case, I would suggest as much testing as possible along with generaous backups for any mission critical data.

 

[mcwhorter]

Thanks for the information. I guess I'll save some money for now and try it out on a Windows PC (Shared folder). I'll just have to edit its power settings so it doesn't sleep.

 

[toro]

I agree with you, is better save money for now.

 

[Dennis Wood]

Wow, old thread...but for reference:

Access tables (backend) will run just fine on a NAS. Just make sure your front end references the tables using UNC, and that your clients are accessing via 1GbE network. SSD drives must first be supported in your NAS trays (Qnap TS-470 Pro I know for sure as am using one) and will reduce latency on queries where multiple users are hitting the data at the same time, but a decent NAS over 1GbE is wire limited to about 110MB/s. I personally like using SSD drives in NAS because they have zero moving parts, and have proved very reliable in my experience. We're running a few virtual machines from an SSD in a TS-870 Pro to allow the larger RAID5 disk array (4TB disks) to spin down. The VMs hosted are 24/7 and would otherwise have the disk array spinning 24/7 too...a waste of power. Just make sure if you go NAS, that it is capable of decent performance. Tim has plenty of reviews here. I'm a fan of the TS-x70 Pro boxes as they are fast, allow very easy virtual machine hosting, and make offsite backup very easy via rsync.

If using a NAS as above, and Windows 8x, you'll also see performance improvements from SMB3, and asynchronous file access, so highly recommended if you can swing the client updates.

Up to a few months ago, we also used Access front end queries to pull data from remote (web server) MySql tables, so this is quite easy. You define an ODBC data source using a (free download) MySQL driver in C:\windows\SysWOW64\odbcad32.exe (for 64 bit windows) or c:\windows\system32\odbcad32.exe (32 bit) ...and use this data source on your front ends to link tables. I point this out because QNAP (and likely others) support MySQL and PHPmyAdmin out of the box. Don't be afraid of moving your tables there and using ODBC as above to link. Our Accpac ERP front ends work the same way to MsSql server. Database backups using MySQL dumps are also well documented, making disaster recovery pretty straightforward. If you decide you need remote access to the tables, your tables will be accessible if desired remotely by setting up ODBC as above, but pointing to your WAN IP and setting up a router port forward on 3306 to your NAS.

Also, in a small network, QNAP's QTS4.1 firmware supports domain controller functionality via SAMBA...so a free/easy way to centralize account/permissions management via the NAS without using a Windows server. I mention this as you are doing the peer to peer thing, ... Active Directory run by your NAS is there if required.

Hope that helps,
Cheers,
Dennis.

 

[maruhi]

Reviving an old thread ... hopefully someone takes interest as I'm confused

So I have a client that is using a (gasp) WinXP to share a Fox{Pro database file to others on a network. Frontend is a custom application running at the desktops. Other than that, all data created are office productivity kinda' files so went with TS-453A due to QNAP's ability to disable "oplocks" at any root folder level.

Basically created a two root folders in the volume: (1) for the FoxPro database where oplocks is disabled (2) everything else where oplocks is enabled. QTS4.2.1 is installed on the NAS.

Issue: a big latency introduced when accessing the database through the custom application on the root folder where oplocks is turned off.

For $hits-and-giggles, I copied the same folder structure to the root folder where the oplocks is enabled and the latency accessing the database through the custom application is not there.

The latency is night-and-day ... if table lookup operation takes on average 1 second while accessing the database in the folder where oplocks is enabled; the same operation takes a good 15 seconds longer.

Oddly enough the same fats speed is observed when accessing the database on the WinXP machine ... this WinXP is the production.

Any thoughts/advice? Oplocks need to be disabled to avoid corruption of the shared database.

Thank yoU!

 

[sfx2000]

Can't really help much here except to recommend the evaluation of migrating FoxPro to something else before things go wrong... the business impact here could be huge..

FoxPro has been EOL for a long time now, and went EOS back in 2015 - risks will increase as developers move on that understand the schema and reports, along with how it interacts with the network stack...

 

[maruhi]

Thank you.

Current production seems to to be working fine with the WinXP box; I have a "feeling" that reghacks were used to disable everywhere - at WinXP hosting the database and the Win7 & WinVista clients. When I came onboard to plan replacing the aging WinXP, that is what I thought. If oplocks were not disabled, I would imagine they would have not survived this long for years. BTW, what does "OES" stand for?

The TS-453A is in teh staging mode connected to a different L2 GigE switch. I've tried the usual suspects of differing port configurations, trunking or just feeding the NAS single port, etc. but it really seems like oplocks disabled somehow is a problem. Oddly in QTS4.2.0 I could also see options for SMB versions but 4.2.1 doesn't seem to have that SMB version selection anymore. I'm wondering if that - SMB version that SAMBA is emulating - got to do something when oplocks is disabled in 4.2.1 ...

I guess I can mirror a port and listen to conversation between the TS-453A and the non-production desktop I'm using to test ... to see if there are retries or whatnot ...

 

[maruhi]

Thank you! Yes, I've created a support tix last week when doing testing. I wish the livechat support worked better as it mostly goes unanswered (1 out of 10+ tries answered without timing out after 10 minutes so far). Tix are great, don't get me wrong, since we get answers but usually it takes 24 hours for reply/more questions - and this turn based get taxing. I wish we can resolve this quickly

 

[gatorback]

Wouldn't a MySQL database on a AWS EC2 be preferable?

 

[sfx2000]

Wouldn't a MySQL database on a AWS EC2 be preferable?

There's a cost associated with AWS EC2, but the bigger challenge would be the migration, which is never easy or smooth...

My QNAP NAS box does support mysql as a host, at least as an option - I don't run it that way, but it's certainly possible..

 

[maruhi]

I did some packet capturing onsite. I've used wireshark before leisurely to troubleshoot but a novice at best so maybe these stats don't mean anything & I'm just making a big deal out of it and do not want to believe it.

But to a novice something tells me that just turning off the oplocks on the folder doesn't create this much of disparity in the number of packets needed to accomplish tasks ... at least that's what I want to believe. Also if this was a layer 1 problem it should affect communications to both folders the same ... I think.

When a testing utility read and write 5000 times to a database that resides off of the root folder that has oplocks enabled, it created a ~2MB capture file of roughly 6k packets.

When a testing utility read and write 5000 times to the same database but that resides off of the root folder that has oplocks disabled, it created a ~64MB capture file of roughly 267k packets! This has a lot of request/response packets of "Read AndX", "Locking AndX" and "Write AndX". I also saw a malformed packet coming from the NAS.

So, again, it is may be my not wanting to accept that when running the same process against a folder with oplocks enabled vs disabled, that much more packets are necessary to accomplish the communication when oplocks is disabled. Just the difference looked so big and when they said that disabling oplocks does that it was hard to accept that much difference. Is it reality or are there any other stones to overturn?

 

[sfx2000]

When a testing utility read and write 5000 times to a database that resides off of the root folder that has oplocks enabled, it created a ~2MB capture file of roughly 6k packets.

When a testing utility read and write 5000 times to the same database but that resides off of the root folder that has oplocks disabled, it created a ~64MB capture file of roughly 267k packets! This has a lot of request/response packets of "Read AndX", "Locking AndX" and "Write AndX". I also saw a malformed packet coming from the NAS.

So, again, it is may be my not wanting to accept that when running the same process against a folder with oplocks enabled vs disabled, that much more packets are necessary to accomplish the communication when oplocks is disabled. Just the difference looked so big and when they said that disabling oplocks does that it was hard to accept that much difference. Is it reality or are there any other stones to overturn?

Your results scare me - but a much better flex would be to run the same test from 10 clients concurrently - might find not such a good idea to host the FoxPro data files on the NAS...

 

[maruhi]

Thanks.

Yes, that was the other option - basically (1) NAS or (2) another Windoze box were the options. When we found out and confirmed that QNAP can disable oplocks at root folder level, NAS was chosen. Additionally their use case of less than five concurrent clients and rest of data from COTS apps (PDF, docx, xlsx, etc) gave the nod to NAS.

So running Windows 7 through QNAP's virtual station (to host the database files) might not make anything better, other than possibly the disabling of the oplocks being a little more stable as it is not going through SAMBA, because it's still NAS hardware?