Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sometimes, it can take months to get a proper database server up and running in large companies. They have all kinds of policies, which means that this process takes months. An alternative in the meantime is to place an SQLite database directly on a file server. This works nicely for a limited number of users (I have had success with serving 100 users, using one such database), and it is only recommended for non-critical data. Now I have been thinking about ways to get a database, which is placed on a file server to be able to handle, say ten times as many users. The basic idea I have is to utilize the many, mostly idle desktop computers around the company. Again, the data would be non-critical, database access could be slow, and uptime of 99.0% is sufficient.

Do you see any way of utilizing these, mostly ideal desktop computers to help reduce the load on the file server? Do you have any idea of DBMS, which would be most suited, SQLite was just my first thought. It would be best with an open-source, one so I could hire someone to adapt it for this purpose. Alternatively, I would be interested in building a DBMS for this purpose from scratch.

I know that any experienced DBA will pull their hair out over this question, but would really have value due to the policies of these companies.

share|improve this question
    
I think the answers here are just as applicable on dba.se - in short, those policies are there for a reason –  Jack Douglas May 30 '11 at 12:00
    
Thanks Jack, I was wondering whether to refer to that post, but it seemed like the whole discussion there derailed and no one understood my problem. I really do not want to discuss the policies, nor how to solve my problem in other ways than technical. –  David May 30 '11 at 12:05
1  
"One should note that POSIX advisory locking is known to be buggy or even unimplemented on many NFS implementations (including recent versions of Mac OS X) and that there are reports of locking problems for network filesystems under Windows. Your best defense is to not use SQLite for files on a network filesystem." sqlite.org/lockingv3.html –  Shannon Severance Jun 2 '11 at 20:25
    
@Shannon Severance That's an excellent point. This is really shaky technology! –  David Jun 2 '11 at 20:42
add comment

2 Answers 2

This is highly situational and depends on specific criteria about the actual organizational policies (why does it take so long to provision), data characteristics (is the data mostly read, or is there a lot of updating being done (by a few or many users), can the data be sharded or locally cached?) and systems-level issues (does the solution have to be light-weight in terms of memory, what privileges does the software need to be installed/run, how can clients connect to it (via file-level access, via sockets, firewalling issues), etc.

One possibility is installing a database server in a virtual machine (which could be replicated if necessary for sharding or caching). This would allow the setup to eventually be migrated/relocated to a physical/dedicated server as part of the organizational provisioning process, while minimizing changes due to roll-out. Another solution might be cloud database services (low cost for low performance, though number of simultaneous connections might be an issue). If you're looking for an all-purpose solution, there might not be a good one, but there are many different options depending on the specifics of the case. If there are many small tables which are updated infreqently, then file system caching may be a useful approach.

share|improve this answer
add comment

The issue with this is that as you scale SQLite, you run into the fundamental problem that it doesn't have row-level locks, only table locks. The "thing" you run out of first isn't a raw resource like CPU or IO or memory - it's that your app will get bogged down in lock management. You can have lots and lots of users sharing an SQLite database, but only your readers can tolerate waiting for your writers. If this database is going to be on a a shared drive, then that will only make your scalability problems worse as it introduces additional latency, making the lock acquire/release cycle longer!

Also, in a business, there is no such thing as "non-critical data" unless you are literally talking about people MP3 or recipe collections! If people use it to get their work done and for whatever reason it "goes away" then they will wonder why, and perhaps escalate it up their reporting line. I totally sympathize with how long it takes to get a "proper" database server (be aware that the DBA feels exactly the same way, when he's waiting for the procurement department to buy the servers!) but unless you are prepared to support something forever, don't deploy it. Or if you do, develop it with something like the free versions of Oracle or SQL Server, so that when the time comes, it can easily be migrated into the datacentre.

share|improve this answer
    
So from what I understand, if one could have a computer merge several transactions into one, then this would make SQLite scale better? With regards to "non-critical data", we often come across data, where one could loose as much as 2-3%(!) of the data without causing problems. This is especially, when the data is only used for statistics. I am glad that you can confirm that I am touching on a problem with regards to the time it takes to get a proper server. Would the free versions of Oracle or SQL Server handle things better if they were placed on a file server? –  David May 31 '11 at 8:48
    
Well, they would be true client-server databases; but you could easily run one of them on a PC. Oracle XE includes APEX, so you can serve applications as websites very easily. –  Gaius May 31 '11 at 8:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.