My application (classic asp yay!) has about 2.1 million images @ 25GB and that only represents 90 days of data and I'd like to go 365 at a minimum. I need to get these under control and am considering all options. What are your thoughts on the pros and cons of the following practices:

  • SQL Server Pros: Easy to back up Cons: Performance ?
  • File System Pros: Speed Cons: Redundancy, Back up is slow (currently researching doing Synthetic full back ups instead which might make that better)
  • S3 and the like Pros: Bandwidth is shifted from my datacenter to Amazon, virtually unlimited storage. Cons: Cost, Cost Analysis is tricky (estimating 80% of my bandwidth is images for ROI purposes), Difficult/Costly to swtich service providers should that become necessary

Does anyone else deal with the multi-million image challenge and how did you address it?

  • 4
    Do not not not not not not not not store the image data (blobs) in the database. We made this mistake many years ago and have been paying for it ever since. Database is great for metadata though. Jul 15, 2010 at 23:02
  • See my post about the FILESTREAM datatype - it might change your mind.
    – Dan Diplo
    Jul 26, 2010 at 9:31

5 Answers 5


We don't have millions of images, but do have hundreds of thousands, and we use the hybrid approach - mysql for metadata, images stored on local disk for backup, and pushed to Amazon s3 where they are served to users. We've had no trouble with Amazon and availability. Moving to cloudfront is in our plans, just need to find the time.

This discussion may be helpful to you in your decision:

I would go with metadata in SQL server and files on the filesystem (or s3 or cloudfront). But the best answer depends on some other usage patterns:

  • do the images change often
  • can you serve the images directly from the filesystem (that is, img src="..." ) or do you need them to be access controlled. If the latter, then a database solution is best
  • are you serving a small number of images most of the time (the most recent 10%) or is the distribution relatively widespread.

Backups for millions of images are going to be complicated no matter how you arrange them - it's just a lot of data. I'd want to find a good case study on backing up blobs in SQL server before I committed to that solution. (Here's an article that might be useful: http://www.databasejournal.com/features/mssql/article.php/3738276/Storing-Images-and-BLOB-files-in-SQL-Server-Part-4.htm)

  • Backup is going to be complex, but at least with file-level backups you (generally) don't have to restore the entire backup just to restore one record/image. IMO, filesystem by default unless the database gives you something you can't do otherwise. +1
    – JasonBirch
    Jul 15, 2010 at 23:35
  • File-systems are designed for storing files - you can find file-systems designed for storing millions of files efficiently. Databases are designed for things like your metadata - querying and relating. Unless you have very few images, this is probably the best way (excluding the cloud solutions).
    – dmsnell
    Jul 16, 2010 at 12:31

If you decide to go with storing them in the file system, you might want to read up on this ServerFault question for some do's and don't's: Storing a million images in the filesystem.


Ignore people who say, "Don't store images/binary data in the database" as they are basing their answers on old information (assuming you will be storing the data in a VarBinary type column). The performance concerns using SQL Server to store images can now be mitigated by using the FILESTREAM data type in SQL Server 2008. In essence, the FILESTREAM data type allows you to combine the ease of storing data in the database with the performance you get from serving files from a NTFS file store.

To quote SQL Mag:

"SQL Server 2008’s new FILESTREAM support combines the benefit of accessing LOBs directly from the NTFS file system with the referential integrity and ease of access offered by the SQL Server relational database engine."

For more info read this blog by Ravi S.Maniam on MSDN.

  • Does FILESTREAM storage change the backup/restore story at all? That's our biggest hangup right now...if they are stored in VarBinary it'd be relatively straight forward story.
    – Webjedi
    Jul 26, 2010 at 23:57
  • No, FILESTREAM data is treated like any other, so is backed up with the database. To quote MSDN: "you can use all backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database." - technet.microsoft.com/en-us/library/bb933993.aspx
    – Dan Diplo
    Jul 27, 2010 at 8:35

While I don't deal with the multi-million image challenge, I would use Amazon CloudFront. It all the files are stored in an S3 bucket but are server through Amazon's content delivery system. I would not use S3 alone.

My second choice would be file system. Simple and easy, only problem is if all these files end up in one directory the whole thing will crash, hard.

SQL to me would not be an option for a system like this. Not only are you getting charged for bandwidth transfer you will also be charged for the processing of the query --this will very depending on hosting, but I assume that you are using a dedicated server or at the least a vps where you will be charged for cycles. Then it will slow down your entire site if it uses the same database as the image server. If not then you add all this complexity of having to manage two database connections.

  • In my scenario, presently everything is on premise on my own servers that I own. So there isn't a transaction cost per se.
    – Webjedi
    Jul 15, 2010 at 22:16

Databases are designed for transactional data / consistency and security.

Media files (images, audio, video) tend to be created and maybe deleted, but very rarely updated. So generally there's no need to keep them transactionally consistent with other data and a database won't give you any real benefit there. Text content maybe a different matter.

As long as you don't have any problem with the concept of someone pulling your file directly if they have the file's URL, then a file system is fine. If you were running something like a photo library, where you expect to charge before people download the file, then that is probably a different matter. That is, once a user has paid, they may get a URL specific to that user or valid for only a short time, and the application handles multiple or temporary URLs pointing to the same image. That could still be handled by the app and a file system, but you end up serving the media through the application rather than as a straight file download (which would mostly rule out any benefits of S3) and there's less difference between DB and file system.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.