Best practice: Store images in DB or on File System?

We are building an ASP.NET MVC web application where users continually upload images -- several hundreds per day. The images are somewhere between 200 - 800K.

The two alternatives, as I see it, is to store the images in the DB (SQL Server) or on the File System. One of the most important issues is performance, for which the File System seems to be the better choise. However, storing images on the File System makes Backup/Restore far much more complicated (and possibly risky).

Are there any Guiding Principles or Best Practices out there as to what is the best approach? Do you wish to share any experiences you have in this area? Finally, if there are alternatives to DB vs File System, I would also like to hear this.

Thanks and best regards.

Tim Sawyer, Mar 27, 2011

My experience has been that uploading large images or other binary objects to SQL Server is not very performant and is not completely reliable. The SQL Server team has made it a little better over the years (varbinary(max), etc.) but they haven't made a lot of improvement in performance. If you are going to do this, I would suggest chunking instead of uploading the entire image at once. Also, SQL Server 2008 has a new option (service) that you can run that allows the engine to stream Blob data to the file system. I haven't implemented this service as yet but I believe it works in conjunction with the Shadow Copy service and streams the file to the file system. I'm not clear on how backups would work but since a pointer would be stored in the table there's a good chance that the engine would know to backup the file along with the other data in the table. Check it out on MSDN or the SQL Server site.

Apr 01, 2011

Reply 1

Greetings, Tim.

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.

Dan Corbett, Mar 29, 2011

Reply 2

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.

Good Luck!

Robert Blixt, Mar 28, 2011

Reply 3

This is always a debatable topic, and the answer would depend on your specific circumstances. If performance is your criteria and these images could be edited in future, then storing it on filesystem is a better option.

But then backup and other things are not that seamless and easy as you have already highlighted.. If you have good infrastracture around support activities then this may not be that big an issue.

But if security if of utmost importance, then DB could be a good option though. You may need to implement caching in this case to optimize loading of frequently used images though.

Related to this I am also looking at NAS for storing images (but haven't yet started the full evaluation yet).

There is an opensource NAS available at
http://freenas.org/FreeNAS

More about NAS at
http://en.wikipedia.org/wiki/Network-attached_storage

But as of today I will go with file system for images.

Do share your thoughts as well. Thanks.

Rajesh Pillai, Mar 28, 2011