Menu
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:
Does anyone have a similar situation or have any input on what would be recommended? Thanks in advance for the help!
886
Storing images in a database table is not recommended. There are too many disadvantages to this approach. Storing the image data in the table requires the database server to process and traffic huge amounts of data that could be better spent on processing it is best suited to.
The first is to store the file as a blob within the database. The second is to save the file (image, whatever) in a folder (which may be within the database folder structure) and then store the filename in a text field within the database.
Store in Couchbase a metadata JSON document for each object, maybe a small thumbnail image at most. In that document is data you need about that object in your application quickly, but also a pointer to a purpose built object store like S3, a file system or HDFS. You will get the best of all worlds.
Images are stored in binary in a table cell. The data type for the cell is a binary large object (BLOB), which is a new SQL type in SQL3 for storing binary data.
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:
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!
62
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
