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?