Storing BLOB Data: Data Base vs NTFS File System

BLOB/Image/Videos are unstructured data. RDBMS like SQL Server is made to store relational data. File stream is new feature of SQL Server since 2008 to store large data. This is not always the best solution to store images on db. Store the pictures on the file system and picture locations in the database. My previous experience is also saying. Why? Because...

DATABASE

Pros:

1.       It gives Integrity and consistency @ business layer & application layer.

2.       Data Migration bit easy because it can accommodate other technology like SSIS.

3.       Reading images via Database is faster than slower NTFS Disk because we can index it at Db level.
4.       If objects are under 256 kb in huge number than DB is best.

Cons:

1.       Putting all of those images in database will make it very, very large. This means DB engine will be busy caching all images. So it can impacting to Relation Data processing.

2.       Dumping data in database means slower backups and other operations.

3.       Slower Data Distribution to various location using any HA Technology (Log ship/Replication).

NTFS File System

Pros:

a.       File systems are made to store files and are quite efficient at it.

b.      We will be able to serve the pictures as static files.

c.       No database access or application code will be required to fetch the pictures.

d.      The images could be served from a different server to improve performance.

e.      It will reduce database bottleneck.

f.        The database ultimately stores its data on the file system.

g.       Images can be easily cached when stored on the file system.

h.      No server-side coded needed to serve up an image, just plain old IIS/Apache.
i.       If object is  larger than 1 MB on an average than NTFS is best.

Cons:

1.       There is no direct way to do an INNER JOIN between the product table and your images 

           older to determine what orphaned files you have left.

2.       Integrity may fall in slow network between files and actual data.

Reference

To Blob or Not To Blob (http://research.microsoft.com/apps/pubs/?id=64525) ..

http://stackoverflow.com/questions/1234202/storing-images-db-or-file-system ..

http://stackoverflow.com/questions/561447/store-pictures-as-files-or-in-the-database-for-a-web-app ..