Mar 29, 2011
I have some experience storing documents in SQL Server 2008 usng FileStreaming. It takes a bit of configuring, and the code is a bit different than regular SQL, but it works well. Having both the ability to use streaming to read and write to the files and having the transaction support of SQL Server is great!
If you can use Sql Server 2008 R2, consider that. If you wish to use Snapshot Isolation AND FileStreaming on the same database, it's only supported in this version.
We are preparing to put this into production.
One disadvantage of this approach is that while the files are externally accessible, you still have to query SQL to get the information to access them, so you can't just have the file server serve the images directly.
There's another project I"m involved in, that will be solely images... The number of images, and sizing of the system are such, that putting them into SQL Server isn't really an option. On top of that, we are building this in the "cloud", using Windows Azure. SQL Azure doesn't have support for file streaming as of yet.
Good luck to you.
Mar 28, 2011
If you are going to store a large amount of images, file system (FS), is probably the best route to choose from. This is what I have seen/read that most people use. Many also seem to favor saving the file path (and meta data) in the DB and the file itself on the FS.
I have heard that many people have had issues (locking, backup) with saving large/many files onto DB, and many that originally set up to use DB as storage, eventually changed to FS.
As with any decisions, there are trade offs. Putting the files in a DB will probably make it easier for you to access, than if on a FS. Also you don't require an additional backup strategy if you go for DB only. If transactional integrity is important, you might want to consider putting it in DB.
You might also be intereseted in the filestream datatype (new in SQL Server 2008). I don't know much about it, but it might be worth checking out.