Save documents as blob in SQL or file system

Question

For a web application (.net) I need to allow users to store many documents (pdf, .docx, etc.). My first thoughts were to save all documents as a BLOB in an SQL database (Express).

But how will the database grow? Is it better to save all documents in the file system?

Personally, I still think it's better to save documents in a database because of performance. But I'm not sure and curious what you think.

Edit - My conclusion: If the file size is less than 1 Mb and / or the file is rarely edited, you should save the file on the SQL server due to performance. If the file will be edited a lot and / or more than 1 MB, you must save the file in the file system. Thank you so much a_horse_with_no_name;)

Answer 1

Whether the BLOB in the database is slower or faster than the original file system depends on the size of the BLOB.

This Microsoft article may be of interest to you:

http://research.microsoft.com/apps/pubs/default.aspx?id=64525

Answer 2

I have done this in the past, and especially with SQL Express as your database, you can think more about saving them to the file system. SQL Express has a maximum database size of 4 GB, which can be a problem depending on the number of documents you are talking about and how large they are.

I saw some of our databases grow rapidly, allowing users to upload and attach documents to the database. Users will not check the size of the photos or double-check that the file is not already in the system elsewhere, they will simply add it, so the size is what you need to plan.

Answer 3

Your database will grow rapidly. The file system is designed to manage files, and it does it well, so I suggest you rely on the file system to do your job of storing and retrieving files, and also use a database to store and let you search through data.

Answer 4

The key seems to be many documents (pdf, .docx etc.) here many documents (pdf, .docx etc.) . For many of them, we can assume that your data will be very large. You can use both approaches technically. You should keep in mind that if you are using a database to store data, and your database backend is sql express, you have limitations on the size of the database that you need to worry about. Therefore, you should use Sql Server Professional or Enterprise to avoid this. Also, if you must use a database, use a separate database than your main db. My recommendation :
1. use the file system
2. only store links in the database to your files 3. Regularly save files on the file system, as well as your database 4. Protect the directory in which the files are stored to prevent unauthorized reading or downloading of files.

If the database is used for storage, it has the advantage that when backing up the database you have all the backups, so moving data is simpler, but the advantages of storing in a file system based on your requirements outweigh these advantages.