5

I'm sure this has been asked before, but I am developing an application which needs to have a network share database. I don't have the option of setting up a dedicated SQL server, so my only option is a file based database.

The use will initially be 5-10 users who will primarily read from the database, and only write to it a couple of times per hour at most.

I've read on here that people recommend I stay away from Access, but what other options are there to achieve what I am after?

hshah
  • 842
  • 4
  • 14
  • 35
  • I totally agree with the avoid access, we have had to setup backups hourly of an access database because everytime some people use it it ends up corrupted. Its a reporting tool, and is best left to that. SQL compact would be easiest – BugFinder Aug 29 '12 at 21:35
  • 1
    Go on - use access, it's always more fun when something breaks regularly - you'll feel like a detective trying to put together the pieces! – Charleh Aug 29 '12 at 21:56
  • @BugFinder - SQL Server CE it is :) – hshah Sep 04 '12 at 07:35
  • @Charleh - Made me laugh and unfortunately Access only got one vote... yours :p – hshah Sep 04 '12 at 07:36
  • @hshah Hi, hshah! Did you have success with using SQL Server Compact database on a network share with 5-10 users? I have a very similar scenario, but information that i have found hints that SQL Server Compact is not an option in this case... is that so? http://stackoverflow.com/questions/20229964/sql-server-compact-db-on-network-share-multiple-users-from-different-machines – Prokurors Nov 26 '13 at 23:36
  • @Prokurors - Hi, my project was stopped mid development, so I never got a chance to try it with multiple users. Sorry :( – hshah Nov 29 '13 at 19:32

1 Answers1

2

I would suggest either using SQL Server Compact Edition or SQLite.

Neither requires a server to run (they're file based) and both are more full featured and reliable than an Access database.

Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
  • Do I need to implement file locking etc to avoid data corruption? How will performance fair if my user base goes up to 50? – hshah Aug 29 '12 at 21:27
  • @hshah - You shouldn't have to worry about file locking as long as you're opening/closing connections to the database appropriately and performance should be fine with 50 users as long as the number of transactions stays at what you estimate in your post. – Justin Niessner Aug 29 '12 at 21:28
  • So on the off chance two users try and open a connection at the same time, will one of them get an error? I'm guessing there needs to be some sort of check that ensures the file is available for writing before initialising the connection? – hshah Aug 29 '12 at 21:30
  • @hshah the database engine will handle concurrent connections. There will only be a problem if users try to make conflicting data updates. – phoog Aug 30 '12 at 17:17
  • @Justin Niessner - Apologies for the late reply (been in Belgium for the F1). I have gone with SQL Server CE and have been playing around with a test program which reads and writes to a table. I am learning C# for this so it is slow, but StackOverflow and Google are my friends :) – hshah Sep 04 '12 at 07:35
  • the F1 visit, I used to go but, havent gone to silverstone the last couple of years cos of cost.. There are many examples on google, and we are here should it all go very wrong :) – BugFinder Sep 04 '12 at 09:17
  • @BugFinder - Can you help with this? http://stackoverflow.com/questions/12270326/c-sharp-and-sql-ce-data-source-keyword-not-supported – hshah Sep 04 '12 at 20:51
  • @JustinNiessner I am confused... a lot of posts suggest that it is NOT possible to access SQL Server Compact database for multiple users that is on a network share... is it, or is it not? Have You acctually tried, that it works? http://stackoverflow.com/questions/16955184/problems-faced-in-keeping-sql-compact-edition-database-on-another-machine-in-sam – Prokurors Nov 26 '13 at 23:09
  • @JustinNiessner This place states that opening SQL Server Compact from a network share requires to set mode to Epxlicit, which means that it can not have concurrent users... is that so? http://technet.microsoft.com/en-us/library/ms171817.aspx – Prokurors Nov 26 '13 at 23:34
  • @Prokurors - Where do you see that you're required to set the mode to Explicit (which isn't actually mentioned on that page at all)? – Justin Niessner Nov 26 '13 at 23:36
  • @JustinNiessner There is a "Note" just below the table and it's written there that "If you are opening a database that is located on a file share, use the Exclusive file mode." (sorry Exclusive, not Explicit) – Prokurors Nov 26 '13 at 23:38
  • 1
    @Prokurors - Then I'd go with that advice. But remember, as I mentioned in other comments, that's only while the connection is open. – Justin Niessner Nov 26 '13 at 23:44
  • @JustinNiessner Forgot, that I don't have to keep connection open... acctually it could work... Thanks a lot! :) – Prokurors Nov 26 '13 at 23:50