How reliable is file sharing on Windows when using it for a database?

Question

We have an application that produces a database on disk. The database is made of thousand of files.

The application can have from 500 to 3000 file's handle opened at the same time. These handle are kept opened and data is continuously written to.

Up until now, it worked really well on local hard drive, but when trying to put the database on a shared disk, we encountered a lot of problems.

Is it simply a bad idea or it can work if we change the design of the database engine to open/close file handle on demand?

EDIT

At this time, we only have one client "connected" to the database.

Answer 1

Based on experience with "shared file" databases (dBase, Paradox and the like) this doesn't scale well. It can also be very sensitive to network errors and bad hardware.

Answer 2

Does the share go to a network disk?

If so then the performance is obviously going to be affected by translation through the SMB protocol, whatever network delay you have and the load on the network disk.

Answer 3

I guess the problems you've encountered is from the "resource-access" family one: some records/tables/file must/should be accessed by multiple users at overlapping times. Network lags when opening and closing files could also be an issue, check that you are NOT using NETbios service to handle this.

This resource-sharing problem is a classical, although only becomes truly apparent when multiple users start using the app... and that's usually "too late".

You should either redesign to use a DB engine, or at least make all requests go through a server application that handles the requests from clients. oh, wait, that would be equivalent of writing a DB engine just for this... dumb of me, to reinvent the wheel.

Unless you're managing real-time video streams (or something even further complicated) I recommend a redesign to include a proper DB engine.

Answer 4

"Is it simply a bad idea or it can work if we change the design of the database engine to open/close file handle on demand?"

Yes it is simply a bad idea. If you want to share your database I would suggest refactoring to a standard database designed to scale well and work with multi-user access.