Images in database vs file system
We have a project coming up where we will be building a whole backend CMS system that will power our entire extranet and intranet with one package. The question I have been trying to find an answer to is which is better: storing images in the database (SQL Server 2005) so we may have integrity, single replication plan, etc OR storing on the file system?
One issue we have is that we have multiple servers load balanced that require to have the same data at all times. As of now we have SQL replication taking care of that but file replication seems to be a little tougher. Another concern we have is that we would like to have multiple resolutions of the same image, we are not sure if creating and storing each version on the file system would be best or maybe dynamically pulling and creating the resolution image we would like upon request.
Our concerns are the with the following:
- Data integrity
- Data replication
- Multiple resolutions
- Speed of database vs file system
- Overhead load of database vs file system
- Data management and backup
Does anyone have a similar situation or have any input on what would be recommended? Thanks in advance for the help!
Answer
There was a nice research paper published by Microsoft Research called To Blob or not to Blob where they looked at all sorts of variables and impacts.
Their finding in the end:
- up to 256 KB in size, blobs are stored in the database more efficiently than in the file system
- for 1 MB and larger, the file system is more efficient
- in between it's a toss-up
Since that paper was published, SQL Server 2008 has also added the FILESTREAM attribute which makes storing stuff in the file system, but under transactional control, a reality. Highly recommended you check that out!