7 Replies - 6401 Views - Last Post: 29 June 2011 - 01:25 AM Rate Topic: -----

#1 danny_kay1710  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 28
  • View blog
  • Posts: 358
  • Joined: 27-April 08

Multi user database running from network share

Posted 07 April 2011 - 07:48 AM

Our engineers use an application developed internally to perform risk assessments at a customers site. Sometimes on larger jobs a group of engineers will all carry out risk assessments together and will all need access to a single database file to all work on at the same time.

I need some help in meeting the below criteria

Criteria
  • There is no guarentee of an internet connection (small workgroup like local area networks are to be set up by engineers (they are not IT experts))
  • Engineers must be able to create this network themselves at a customers site with little to no configuration needed (we have a batch file for changing network configuration at the moment)
  • Good all round performance, there is some heavy processing (both read and writes) in places such as report generation - worst case reports could be ran in single user mode when required
  • No file size limits (within reason - can't see it ever needing to go above 200-300mb)
  • Must be able to be given to the customer at the end of the job along with software. Some clients lack IT departments so end user requirements need to be kept to the minimum.
  • Minimal cost - preferably free. Must be able to be redistributed.


These are my findings from current testing...

Solution 1: SQL Server
  • Industry Standard - fully scalable, powerful etc
  • Requires a centralised server which would not be available on a customers site
  • Not portable, although worst case is an import/export routine
  • Client would need an SQL server to be able to view the data we give them which is not an option

Not Suitable

Solution 1a: SQL Server Express (File based Database)
  • Very Fast - full scalable
  • Requires a SQL Server Express to be installed on all client PC's - more to support for us, more hassle for customer
  • Portable (a two file solution .MDF and log) however cannot be placed on a network share and accessed. Throws a not support exception

Not Suitable

Solution 2: VistaDB
  • Single DLL support for application
  • Fast performance when running locally and in single user mode on network. Painfully slow even for light work when in multi-user mode. Make's application unusable
  • Portable end result

Not Suitable

Solution 3: SQLite
  • Simple DLL support for application
  • Fast performance all round
  • Portable end result
  • Prone to corruption when running in multi-user mode on a network (we have tried running this and seen a lot of corruption)

Most Suitable - Need a solution to help prevent corruption

Solution 4: Microsoft Access
  • No x64 Support
  • Slow but stable performance all round - workable in most cases. Make reporting and other heavy duty tasks take significantly longer
  • Portable end result
  • Prone to corruption when running in multi-user mode on a network but from previous experience is relatively rare in comparison to SQLite - old legacy application used it and it has only happened twice in 3 years compared to 4 times in 2 months with SQLite

Slow but reliable - limits architecture support

In summary I cannot find a single database solution to meet my requirements does anyone have any suggestions for alternative databases or solutions to help minimise corruption/ configurations for the options currently marked as not suitable.

This post has been edited by danny_kay1710: 07 April 2011 - 08:10 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Multi user database running from network share

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9197
  • View blog
  • Posts: 34,551
  • Joined: 12-June 08

Re: Multi user database running from network share

Posted 07 April 2011 - 08:02 AM

Well you have some interesting contradictions in your requirements.

Quote

There is no guarentee of an internet connection

is opposed to this:

Quote

will all need access to a single database file to all work on at the same time.


I was going to suggest something like MSSQL Express installed on each PC, but if everyone needs to talk to everyone... well.. Hell bring in a rig with a database on it. Plunk it down on a power connection, setup a big ol' router on it and have everyone connect to that.

I would avoid Access.

You need to figure out how to reconcile some of these requirements.

Quote

Must be able to be given to the customer at the end of the job along with software. Some clients lack IT departments so end user requirements need to be kept to the minimum.

For what? Isn't that what the reports are for? So someone will have this usb drive with a file they don't know how to open or what for just.. laying around for ever?
Was This Post Helpful? 1
  • +
  • -

#3 danny_kay1710  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 28
  • View blog
  • Posts: 358
  • Joined: 27-April 08

Re: Multi user database running from network share

Posted 07 April 2011 - 08:07 AM

It doesn't contradict - they set up a local area network without an internet connection - you don't need an internet connection to talk to each other... they will all be in the same place when typing up information.


Some of our reports have been 5-6 lever arch files of double sided A4 in the past. We don't want to have to always print this and in most cases it isn't what the client wants either.

The report puts it all in a readable fashion. The client should then carry out remedial work and can update the software so they will be absent from future reports by implemented the risk.

[EDIT] I need to learn to proofread!!

Edit 1
Also we carry well over 30KG of equipment with us in most cases anyway - this is over most airlines baggage allowances. Carrying anything else bigger than maybe a router so we can DHCP is also a no go I'm afraid. If I could guarentee it was always going to be in the UK it wouldn't be too bad but from a business point of view portable server equipment isn't really too viable.

Edit 2
To clarify it's not everyone talking to everyone - one user will host the file with everyone else connecting to that person at present.

I also heavily agree with the staying away from Access.

This post has been edited by danny_kay1710: 07 April 2011 - 08:13 AM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9197
  • View blog
  • Posts: 34,551
  • Joined: 12-June 08

Re: Multi user database running from network share

Posted 07 April 2011 - 08:18 AM

Ah - well you didn't specify people were setting up networks and what not. Regardless you need someone with the file and people to get to that location. Draw straws on who's pc will be the "bitch" on that job? Do you leave your equipment there with the client then?

Clearly I am not getting how your business operates, and will probably tap out of this discussion.

Side note - you can get a really small server running for cheap:
http://lifehacker.co...re-file-sharing

Throw on MYSQL and apache and off you go.
Was This Post Helpful? 1
  • +
  • -

#5 danny_kay1710  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 28
  • View blog
  • Posts: 358
  • Joined: 27-April 08

Re: Multi user database running from network share

Posted 07 April 2011 - 08:31 AM

We don't leave any equipment. All engineers have laptops which they configure using an automated script to connect to each other on a small workgroup like networks with statically assigned network addresses. (They are all actually joined to our corporate domain but at that point no AD servers are contactable)

Once the file is finished with on site we store it on our servers - in some cases we have a legal requirement to do this. We start a new database for every job we do.

Our business has a high level of security requirements so opening SQL server ports on laptops is probably a non starter as much as the solution sounds appealing. At the moment only port 80 and 443 is allowing for outgoing traffic and nothing is allowed incoming unless on specific IP ranges which are within our corporate LAN. It still doesn't address portability but like I said import/export isn't out of the question. The big problem is the support element. Ultimately smaller clients are going to need support setting our software and SQL up and the more complicated it is the worse it is for us and the more the customer gets annoyed.

I can't see carrying extra equipment being viable either mainly due to objection to even carry cables and a network hub from management at the moment.

Clients generally have a legal requirement to implement control measures to the risk assessments we give them as we are often highlighting areas where there factories/plants etc don't comply with current European legislation.

We therefore don't want to be seen to get in the way and will ultimately supply the report whichever way best suits them. Initial PDF reports are always sent out with the database so the client has an immediate file they can open and read. Small jobs are often also printed out.

The software all comes with instructions we are in most cases (all but really small jobs where we probably won't even include the software) we will send someone to site to go through using the software with them for free as part of the job (these are not IT people simply as the questions are often not IT related and engineering related).

Thanks for your input modi123_1, it is appreciated. Any more suggestions from anyone is welcomed!
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5826
  • View blog
  • Posts: 12,678
  • Joined: 16-October 07

Re: Multi user database running from network share

Posted 07 April 2011 - 09:20 AM

Quote

Solution 4: Microsoft Access
Slow but reliable - limits architecture support


Nope, entirely unsuitable. It is not a multi user database, period.

You seem stuck in a "single file good" mentality. But you're providing the software to the client as well, so who cares how many files it is?

Ignore the database, you're writing a program. The program needs to hook into a central data store. Make that central data store a service. A web service would work. Now the data store is irrelevant, all transactions go through a middle tier.

You can run a Ruby on Rails project from a command line. A Sqlite database works well for this. I've never seen them corrupt, but then I'm not letting random users access the thing across a network. A single point of entry makes all the difference.

It's really down to the software front end. You could have the software check to see if anyone else was accessing a given file. If they are, you elect them as the primary point of entry and process through them. If there is no one, you become the server and start work.

Actually, a peer to peer thing sounds appropriate, where each user has their own copy of the data. They have the capacity to sync with other nodes... Source management systems behave like this; you can store anything in some of them.

CouchDB offers good syncing, too.
Was This Post Helpful? 1
  • +
  • -

#7 danny_kay1710  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 28
  • View blog
  • Posts: 358
  • Joined: 27-April 08

Re: Multi user database running from network share

Posted 08 April 2011 - 07:41 AM

View Postbaavgai, on 07 April 2011 - 05:20 PM, said:

Quote

Solution 4: Microsoft Access
Slow but reliable - limits architecture support


Nope, entirely unsuitable. It is not a multi user database, period.


I completely agree, however previous experience with a legacy application seemed to prove me wrong. Whether I liked it or not it was stable even when multiple users were working off the same database however towards the end the speed and architecture issue caused problems

View Postbaavgai, on 07 April 2011 - 05:20 PM, said:

You seem stuck in a "single file good" mentality. But you're providing the software to the client as well, so who cares how many files it is?


It doesn't matter really but for simplicity keeping it low is preferable. We don't bundle it all into one setup at the moment so the client still has to copy it off CD to their system.... I have had support calls last in excess of 45 minutes just to guide them through copying one file off a CD so that the database could be opened in non read only mode.

The software's primary purpose is for our engineers. It is a software system aimed at making their jobs easier. In fact without it their jobs would be impossible in the time-scales we are used to. It would take easily ten times as long on paper. The fact we give out the software is a way of reducing how much we have to print and an extra selling point none of our competitors can really match

The company doesn't make enough from software sales to warrant loads of manpower with support so complicating it is really not desirable in that respect. Web services and such could be a real support issue with help getting it set up etc.

It is worth noting that our clients also expect to be able to use the single database file across a network with potentially multiple users as well.

Thanks for your reply and input it is appreciated.... I don't really think there is an easy solution nor one that will tick all boxes but it is good to get opinions and ideas for solutions.
Was This Post Helpful? 0
  • +
  • -

#8 cew3  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 11-May 11

Re: Multi user database running from network share

Posted 29 June 2011 - 01:25 AM

@danny_kay1710:

I share your requirements for a file based database and evaluated VistaDB to be used with my application.
I ran in several problems with multi user access and problems when located on a network share...

Did you find a suitable solution?

Best regards
cew3
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1