I'm in a planning phase of developing a web site where public users will be able to store their documents, by nature it could be anything like documents, pdf, audio, video or images.
We are expecting large number of files stored in the database like numbers could be a few millions on average file size of 10 Mbs of one file.
I want to know a direction the way i should start learning/thinking on, for example in above case how many files one table should contain and how many such tables should be contained by one database.
What are the best practices while dealing with such scenario.
What are the major things i should be taking care of while working on such project, I can use MS SQL Server database, My SQL is not a priority unless it has some advantages (its all based on available resources).
Best Answer
Matigo is correct. A file system is designed to handle storing files best, a database system is designed for storing raw data best, not files. You should use the right tool for the right job.
There are multiple issues you can run into with storing files directly in the database, such as bloated backups which waste space and take much longer to recover with when needed. Also database performance issues can occur either at the query level (because your rows are split across multiple pages to support large objects like files) or even at the database level as a result of contention by the constant growth of the database data file.
There are a multitude of other reasons why it's generally not recommended to store files directly in the database either. One good alternative recommendation is store the files directly on a file system somewhere, with the link to their file path stored in a meta-data table about the files in the database.
Finally, you may find this Brent Ozar article Store Files in a File System, Not a Relational Database. informative on the matter as well.