0

Background
I have been tasked to write an application that will be a combination of document and inventory management in VB.net which will be used to store document images in TIFF, PDF, XPS, TXT, DOC, PPT and so on as binary data that can be retrieved for viewing, printing, and possible OCR to be searchable as well along with meta data such as sender, recipient, type of document, date, source, etc. So the table would probably be something like:

DOC_NAME, DOC_DATE, NOTES, ... DOC_BINARY (where the actual document will be put inside)

Help Please
I need help with understanding how to evaluate my database options.

What my concern is finding a database solution that will not become unstable due to size restrictions, records limitations and performance. Some of the options are MS_SQL, SQL Express, SQLite, mySQL, and Access. Now I can pretty much eliminate Access right off the bat as it is just too limiting and not scalable. I can further eliminate SQL Express because of the 2 GB limit and again scalability.

So I believe that leaves me with MS_SQL, SQLite and mySQL (note, I am open to alternatives). And this is where I need help in understanding how to evaluate those databases.

The goal is that the data is all in one place (a single file) that will make backup and portability easier. For small volume usage, pretty much any solution will hold for a while, but my goal is to think ahead and make sure its able to withstand heavy large volume usage as well. Another consideration is also the interoperability with .NET and stability of such code to avoid errors and memory leaks.

How should I evaluate my database options for this scenario?

  • 2
    Have you considered storing the binary outside the database and only storing a path to it? – FrustratedWithFormsDesigner Oct 22 '13 at 21:11
  • @FrustratedWithFormsDesigner Yeah but for modularity they want it to be a single file store. I am just trying to accommodate. Personally I would have rather not put it in the file either, just assign an ID to it, link it inside and be done but that's not what is needed so here we are :) – GµårÐïåñ Oct 23 '13 at 3:52
2

2gb limit is very old -- IIRC that was MSDE, which you could call Sql Express 2000. 2005 had 4gb, 2008 had 8gb and I think 2012 has 10gb limits.

Anyhow, since all SQL Express is is a hamstrung version of SQL server the path I would take is to build / test against and distribute for SQL Express and let folks upsize to full-blown SQL Servers when they need it. As long as the database connection is configurable this is a zero code impact change.

The SQL Server family has some advantages for what you want to do -- there are free SKUs that should get you out of the gate, the VB.NET integration is sweet (though it won't help you avoid memory leaks) and FILESTREAM support will help you handle the BLOBs.

  • Glad to hear that, as I was thinking along the same lines but hearing it from others lends more weight to it. As far as the code is concerned since its going to be connecting to the db the same way be it Express or Server, you are right, code impact should be minimal if anything at all. Thank you for the suggestion, I will try to roll out a simple POC of it and see how it goes and then go from there. Thanks again. – GµårÐïåñ Oct 23 '13 at 3:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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