This might be a bit of a long shot, but if you’re using (or planning on using) SQL Server 2008 I’d recommend having a look at the new FileStream data type.
FileStream solves most of the problems around storing the files in the DB:
- The Blobs are actually stored as files in a folder.
- The Blobs can be accessed using either a database connection or over the filesystem.
- Backups are integrated.
- Migration “just works”.
However SQL’s “Transparent Data Encryption” does not encrypt FileStream objects, so if that is a consideration, you may be better off just storing them as varbinary.
From the MSDN Article:
Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.
FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.
Some applications that manage many TB of images. We’ve found that storing file paths in the database to be best.
There are a couple of issues:
- database storage is usually more expensive than file system storage
- you can super-accelerate file system access with standard off the shelf products
- for example, many web servers use the operating system’s sendfile() system call to asynchronously send a file directly from the file system to the network interface. Images stored in a database don’t benefit from this optimization.
- things like web servers, etc, need no special coding or processing to access images in the file system
- databases win out where transactional integrity between the image and metadata are important.
- it is more complex to manage integrity between db metadata and file system data
- it is difficult (within the context of a web application) to guarantee data has been flushed to disk on the filesystem