What are the advantages and disadvantages of storing binary data in a SQL table vs a network drive? [duplicate]

Question

Possible Duplicate:
Files - in the database or not?

I've been reading "Programming Entity Framework Code First" (http://www.amazon.com/Programming-Entity-Framework-Code-First/dp/1449312942).

There are a number of examples given where information about a particular person is stored in a SQL table, including the person's photograph (a digital image stored in a VARBINARY field).

In all of the systems that I have worked on, any kind of file associated with a record in SQL is stored on an ordinary network drive; not in SQL. I would imagine that there must be some reasons for this, so here's my question:

What are the advantages and disadvantages of storing binary data in a SQL table vs storing the same binary data on a network drive given that the binary data is associated with a record in SQL?

Answer 1

One advantage is that if the program that needs to access the data does not have access to the file system, it's not a problem! If the database is accessed by multiple applications on different systems, they probably shouldn't have file-system access to your database server. You could always store a path and they create a web service to allow these applications to request a file on a specific path, but that's a bit more work so you'd better be sure it's worth it. Keeping the binary data in the database and returning it in the record set is simpler.

A disadvantage is that your database will now require more storage space and this will impact backups and other things. Sometimes having large amounts of binary data in a database can also have an impact on performance. Some DBAs handle this by moving BLOB data to tables in separate schemas that are managed differently from the main "data" schemas. While such techniques may work, you might need a deeper understanding of database management and tuning to get it working well, if you have LOTS of binary data.

Answer 2

File systems as databases is an old tact and not always invalid depending on the purpose.

The big differentiator is your use, if you're going to have frequent writes (especially updates) such as in a highly transactional system, you do not want to use a file system as this will require a lot of synchronizing multi-user access. Furthermore databases are made specifically for this purpose having optimizations like caching multiple updates/writes to thunk bulk-writes for better network throughput not making users wait on disk IO completions, caching in memory highly transactional data etc as well as countless tricks tuned and optimized for decades now to make use of disk IO in the most efficient way as possible.

Now then, when is this approach valid? For largely read-only blobs, and especially when you are capable of a strategy where you cache them in-memory in a way that minimizes how often they are read from disk, especially if there is any form of serious load. For instance, if it's a web-server, most web-servers have built in well-tuned strategies for doing this with frequently requested static data which you merely need to turn on for the URL those blobs are available from.

In either scenario, the network location of your database and or files related to database are to be treated the same: Locked down as all get out. You do not allow access to these resources as someone may trash your database, or those files which are related to the database and therefore a part of the database in truth; moving/deleting/renaming/altering them is the same as doing so on a database record. Treat security accordingly.

Further knowledge regarding blobs on a per database system basis is also important, for example in SQL Server if you have a blob column in a table depending on how it's setup, it may use an entire page for every single record regardless of actual record size, further it may move the blob portion of the record off the page to another page of the data file altogether, these factors should be studied when designing a table with blob columns to make sure you aren't causing the database to work less efficiently than it could with a different table design.

Side note, when storing blobs in the database, whichever tables they go in it's a good idea to place those in separate file groups from other portions of the database so they can be moved to larger LUNs over time as their growth outpaces the rest of your database, and to help them not interfere with IO or query performance of the rest of your database.

Answer 3

Two reasons I really like database over filesystem storage are:

• Databases support transactions so you can be truly ACID in writing the files.
• Backup / restore / data transport -- if all the user data is in the database than you only need to worry about backup of the database. Stateless web servers are awesome especially with this cloud thing.

The usual knock against this sort of thing is based on performance which probably has less and less merit as time goes on. Moreover, in many cases, you can still use the filesystem to serve the images if that makes a bit more sense with some fancy caching options.

Finally, for my app I would definitely choose SQL over a network drive since you are taking on one of the typical complexities and bottlenecks of database storage -- working with a remote store.