Yesterday I presented a case for not storing BLOB data in SQL Server. Dave Shared in the comments an good case against my generalized position against using blobs. His primary concern was the sprawl of files by storing each instance of BLOB data as a separate file. His product was a self-contained image management tool. By using SQL Server as storage access to the blob data was strictly handled by SQL Server, keeping consumers of the software from bypassing the system when accessing the files.
Dave also pointed out that if you used individual files you would still probably have a table indexing the files and their locations. Moreover, the individual files would be accessible to users outside of SQL Server.
Without being argumentative, I would like to pose the question, “Why is that bad?” If you want to isolate the files from being manipulated outside of your application that can be handled with file access credentials.
Having a ton of files is probably a non-issue because you are using SQL Server to index the file locations for accessing it. The only reason to access the files on an individual basis outside of the application is for disaster recovery. In fact, disaster recovery is where you get the best benefit because you can generate reasonable backups without backing up every single file every time. Should you need to recover a single image that can be performed without recovering every single file.
These are all things that can be highly valued in an implementation. But, when I think about what would be needed to do this in a canned product, there is a lot to be said for not using individual files. Many companies have the resources to implement a file based solution. Many do not. A self-contained system may not have all the advantages of individual files, but the simplicity of installation, configuration, and management could easily persuade me to give up some of the performance enhancements for reduced cost of support.
Dave, thanks for providing insight into the fact that one size does not fit all situations.
Cheers,
Ben