For a (.net) web application I need to allow users to store many documents (pdf, .docx etc.).
My first thoughts were to save all the documents as a BLOB in a SQL (Express) database.
But how will my database grow? Is it better to save all the documents into the file system?
Personally I still think that I'm better of saving the documents into the database, because of performance. But I'm not sure and curious what you folks think.
Edit - My conclusion: If the file size is less then 1Mb and/or the file is rarely edited you should store the file in SQL server, because of the performance. If the file will be edited a lot and/or is greater then 1Mb you should store your file into the file system.
With many thanks to a_horse_with_no_name ;)
Whether BLOBs in the database are slower or faster than native filesystem, depends on the size of the BLOBs.
This article from Microsoft might be interesting for you:
http://research.microsoft.com/apps/pubs/default.aspx?id=64525
I've done both in the past, and especially with SQL Express as your database you may want to think more about saving them to the filesystem. SQL Express has a max database size of 4 GB which could be a problem depending on how many documents you are talking about, and how large they are.
I've seen some of our databases grow very rapidly after allowing users to upload and attach documents in the database. Users are not going to check the size of the photos or double-check that the file isn't already in the system somewhere else, they'll just add it in, so size is something you have to plan for.
Your database will grow fast. Filesystem is designed to manage files and it does it well, so I suggest you rely on filesystem to do its job of storing and retrieving files, and use database to store and allow you to search on data.
It seems as though the key here us many documents (pdf, .docx etc.). By many we can assume your data will grow large very quickly. You can use both approaches technically. You should consider that if you use database back-end for storage and your database backend is sql express you have size limits of the database to worry about. So you should probably use Sql Server Professional or Enterprise to avoid this. Also if you must use database, use a separate database than your main db.
My recommendation is:
1. use the file system
2. only store links in the database to your files
3. backup the files on the filesystem regularly as well as your database
4. secure the directory you store the files in to prevent unauthorized read or download of files
If database is used for storage it has the advantage that when you backup the db, you have everything backed up, so migrating data is easier, but the benefits of storing on the File system given your requirement outweigh such advantages.
