Thinking of moving to BLOBs


We have a website at present based on member subscriptions. Each member can upload a set of photos to their profile. At present we store the photos in the filesystem - there are around several thousand photos in there at present and growing.

All the photos are generally less than 100k.

The site is set to grow rapidly in the next couple of years and is using a SQL Server 2008 database.

I know there are arguments for and against storing images as blobs or files. Last time I went on the server and tried to open the folder containing the images the server hung!... So I am moving towards favouring storing these as BLOBs in the DB.

I read somewhere that small images of this kind of size work well coming from a DB and this would seem to be a logical way to move ahead...

However, the SQL Server and Website are on the same box due to cost limitations at the moment and I know this will cause much more SQL load. The server has 4gb of RAM and a 250GB HD running 2003 Server.

So.. some advice on the way to go would be greatly appreciated. I can see two main options.

1) Continue to store the images in the filesystem until we can 'afford' to move SQL onto a separate server and then do some mass scripting to get all the existing images into SQL. Baring in mind that we expecting the number of images to grow rapidly over the course of the next year, I'm a little unsure about this option.

2) Store all the images in a new SQL table on the current SQL/Web server

If we stick with the file system would it be sensible to limit the number of images in each directory and set up multiple directories for different member ID ranges.?  I know that NTFS has a theoretical maximum number of files in a folder of over 4 million files but how does that work out in real world performance / maintenance?
Who is Participating?
Anthony PerkinsCommented:
>>Last time I went on the server and tried to open the folder containing the images the server hung!... So I am moving towards favouring storing these as BLOBs in the DB.<<
Is this your reason for moving the binary data to a database?  If so, I have to say it is not a very good one, unless you give the option to the user on the website to do the same!  And that would be quite anotehr story...

But to give you some background, I have stored images in the database many years ago and learned my lesson and I will never do that again.  It seemed at first like a very good idea, until you realize you have just created yourself a maintenance nightmare.  There is simply no comparison between a file backup and a SQL Server back up.

If despite all this you are bound and determined on storing BLOBs in SQL Server, then consider a third option and that is using the new (in SQL Server 2008) FILESTREAM attribute.  This is a hybrid approach between storing the images on the file system and storing them in the database.

Here is a good article on the subject and perhaps a tad less biased:
To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem
Jim P.Commented:
I would stick with a file system approach. Storing extensive amounts of binary objects in a database just becomes a nightmare. Any time you do backups it can take hours. Moving a single large file (or even a bunch of large ndf) or bak files is a problematic.

If the customers want (or need) their data moved, you have to build a mini DB as opposed to sucking the files off the drive.

I worked with an imaging system for many years. They stored the data on disk and had the folders broken into CD (and later DVD) sized hives that made it easy to archive the data with a mini-app.

We did have to migrate the database and images to a new system. Because the images/files were on disk we were able to use Beyond Compare (totally worth the $30 license) to copy from SystemA to SystemB (it took 24+ hours) and then from there was able to modify the DB to point to the new location. There was very little disruption to the users.
craig32768Author Commented:
Hi all - thanks for your feedback on this, I will take your advice and keep them in the filesystem then.

Any suggestions / recommendations on the maximum number of images per folder?
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Anthony PerkinsCommented:
>>Any suggestions / recommendations on the maximum number of images per folder?<<
Perhaps you overlooked the previous comment from jimpen which makes sense to me:
They stored the data on disk and had the folders broken into CD (and later DVD) sized hives that made it easy to archive the data with a mini-app.
Ted BouskillSenior Software DeveloperCommented:
Sharepoint stores all it's files in the SQL DB.  The pro is that adding new web front end servers is trivial because local disk storage is barely used.  The con is that the configuration has to be setup correctly to get great performance.

For example, the BLOB has to travel via TCP/IP to the web front end server then is transferred to the remote client.  If the web server is short of RAM or disk space this process can fail or be very slow.  Sharepoint has an optional local blob cache to improve performance.

The ideal setup is using two NIC's, one to connect to the SQL server, the other for HTTP traffic.

In your case with one server the file system is OK, however, you should consider using "Memory mapped files" to get great performance.

If you do plan on scaling to multiple servers then consider a SAN device to provide shared disk storage to all web front end servers.

The key you have to consider is if your are hitting disk I/O thresholds using the disk for both SQL and file access.  That is a LOT of disk activity.  Rather than considering the number of files per folder you should be considering the number and type of physical disks to optimize throughput.
craig32768Author Commented:
Thanks to all your suggestions. For the remainder of this year we will run on the same server in the filesystem. And after that - keep with the filesystem put place the images on another physical disc / server / CDN.

The maximum number of files to store in each directory is dependent on the operating system you are using.  We often limit it to about 10,000 files per directory.  It is definitely not a good idea to store hundreds of thousands of files in one folder.

I think it's a good idea to store the filename and a folder in two columns in the database.  That way if you want to move a bunch of files, you can move them, and do a db2 update to just update the folder name.

Also, regarding Sharepoint post earlier...  Sharepoint by default stores blobs in the database.  It's not a good idea to store large numbers of BLOBs in the Sharepoint database.  Sharepoint has extensions that can be used to store the images outside of the database.  SQL Server does as well.  There are MANY companies that use those extensions.  is just one of many options.  Documentum, FileNet, and other vendors have Sharepoint integration as well.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.