Save Documents As Blob In Sql Or On File System

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) databas

Solution 1:

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

Solution 2:

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.

Solution 3:

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.

Solution 4:

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.