Store documents / videos in a database or as separate files?

Question

Is it better to store multimedia files (documents, videos, images and, ultimately, executable files) in the database itself, or just put a link to them in the database and save them as separate files?

Answer 1

Read this white paper on MS research (before BLOB or not BLOB) - it details the question.

Summary - if you have many small (150 kB or less) files, you can also save them to the database. Of course, this is correct for the databases that they tested, and using their test procedures. I suggest that you read the full article to at least get a good understanding of the trade-offs.

Answer 2

This is an interesting article that Oded is associated with - if you are using Sql Server 2008 with FileStream , the output is similar. I cited a few important points from a related FileStream document:

"FILESTREAM storage is not suitable in all cases. Based on previous research and FILESTREAM behavior, 1 MB or more BLOB data that will not be available through Transact-SQL is best suited to be stored as FILESTREAM data."

“You should also consider the update workload, since any partial update of the FILESTREAM file will generate a full copy of the file. With an especially large update workload, the performance may be such that FILESTREAM is not suitable

Answer 3

Two requirements answer your question:

• Are there several application servers that read binary files from a database server?
• Do you have a database connection that can binary write and read stream?

Multiple application servers that retrieve binary files from a single database server really interfere with your scalability. Consider that database connections usually - necessarily - come from a smaller pool than the application server request service pool. And, binaries with data volume will consume sending from the database server to the application server via the channel. The database server will most likely send requests to the queue because its connection pool will be consumed with binary delivery.

Streaming is important so that the file is not completely in the server’s memory when reading or writing (it looks like @Andrew’s answer about SQL Server 2008 FILESTREAM can talk about this). Imagine a file the size of a few gigabytes - if it is completely read in memory - it will be enough to crash many application servers that simply do not have physical memory to host. If you do not have connections to the streaming database, then storing in the database is really unsafe if you do not limit the file size so that your application server software is allocated at least as much memory as the maximum file size * the number of requests per connection maintenance + some additional overhead.

Now let's say that you do not put files in the database. Most operating systems are very well suited for caching frequently used files. Thus, from the very beginning you get additional benefits. Also, if you are a web server, they are pretty good at sending the right request headers, such as mime type, content length, electronic tags, etc., which you end up coding yourself. The real problem is replication between servers, but most application servers are pretty good at this, using HTTP streaming read and write, and another responder pointed out that the database and file system were synchronized for backup.

Answer 4

Storing BLOB data in a database is not considered the right way if it is not very small. Instead, preserving their path is more appropriate. this will greatly improve database performance and search performance.

Answer 5

Here is a detailed comparison that I made http://akashkava.com/blog/127/huge-file-storage-in-database-instead-of-file-system/