How would you setup backend database?

s1guy

Member
Joined
Mar 17, 2020
Messages
5
Programming Experience
Beginner
Hello,

I have to place a backend database on a network drive for multiple users. I want to build the frontend app in C# and have the users connect to the backend database.

I prefer to not use MS Access to prevent corruption, although I've used it for developing databases for years without issue by splitting the frontend and backends.

SQLlite isn't a valid option due to locking that happens with it, multiple users will be editing the database, not necassarily the same record, but my understanding SQLlite locks for any writing that happens.

My next thought is SQL Server Express which allows 4GB of data, I think that would be enough. I can't install SQL Server on the network, it is just a drive that holds files and nothing more.

If SQL Server Express is installed on the users machines and they access the database file on the network drive, will everything work as it should?

What are you recommendations? I have no choice but to use one database file on a network drive as a shared file.

ADMIN - If you disapprove my post, please let me know why and what I need to do. I would like an answer on this, my last post got deleted with no reason emailed to me. Thanks
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,049
Location
Sydney, Australia
Programming Experience
10+
ADMIN - If you disapprove my post, please let me know why and what I need to do. I would like an answer on this, my last post got deleted with no reason emailed to me. Thanks
Apologies for the mistake. We get a significant number of spam accounts on this site and I accidentally rejected your post based on muscle memory. I contacted the site admin and he was able to reinstate your account, hence your ability to repost. He was going to try to recover your original thread but may not have been able to. I'll let him know that there's no longer a need. Again, my apologies.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,049
Location
Sydney, Australia
Programming Experience
10+
As for your question, SQL Server Express is not an option I'm afraid. The data file to attach has to be local, so you wouldn't be able to have multiple users attaching a remote file to a local instance. To be frank, using a common data file on a remote server is not a great setup but, if that's what you have to do, Access may well be your best bet. It has its issues but it will, in general, let you do what you want. You may want to look into using a workgroup file with it. It's not something I've ever done but I think that it can help when multiple users are accessing the same data file.

By the way, the SQL Server Express database size limit was raised to 10 GB some years ago.
 

s1guy

Member
Joined
Mar 17, 2020
Messages
5
Programming Experience
Beginner
Thanks. I was afraid MS Access would be my only option. It's government computers and using SQL Server isn't an option for me. I will use MS Access as the backend. I've been developing in MS Access frontend and backend for years, but want to try C# for a frontend application instead so I can have it installed on client machines without the bugs I get using an MS Access frontend and its issues because everyone might have a different version installed.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
You can use an Access database backend with a C# front end.

There was a point in time in the 2000's when Microsoft removed they recommendation that an Access database not be stored on a file share. Even after that point in time, I still got burned a few times by an Access database on a network file share. Maybe blame it on the network hardware. Or blame it on the antivirus. Personally, I wouldn't recommend storing Access files on a file share and having multiple people accessing it simultaneously.

Does the database really have to be on that network location? Is there a machine at work that will always be on? If so, you can install SQL Server Express on that machine. Multiple people can access that SQL Server Express instance. What is so magical about that network location that a single database server cannot fulfill? If there is some contract legalese that says that database file needs to be shared on the network (and it fails to stipulate it has to be read/write) that it'll be easy to comply: Simply create a file share on the server that presents a read only view of the SQL data folder. Contract requirement complied with.
 

s1guy

Member
Joined
Mar 17, 2020
Messages
5
Programming Experience
Beginner
@Skydiver Thanks for the response, unfortunately I'm stuck with a network drive, otherwise I'd be using SQL Server for sure.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
Read the contract/requirements closely. Do not extrapolate out to what they mean, but stick with what they wrote.

For example, in the company I work for, one of the contracts only stipulated that version control best practices be used. Someone took it to mean that only Subversion could be used as the source control system -- no other -- and there is no migration to any other system allowed. With that word of mouth that only Subversion is allowed, everyone else built up their infrastructure that now completely locked us into Subversion even though there never really was such a requirement.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
Yes, but is it "large IT in the way" like in my example above where are current IT infrastructure thinks that they have to stick with Subversion simply due to momentum and word of mouth instead of someone looking at what is actually required or asked for?
 

s1guy

Member
Joined
Mar 17, 2020
Messages
5
Programming Experience
Beginner
It's large Enterprise level IT that pushes policy down from above.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
Whomever said that a database file needs to live on a network drive as a file share either was a Novell Networks salesman or bought into the Novell FUD, and had never been burned by database corruption due to the file living on a network drive instead of a physical drive.
 
Top Bottom