| Author |
Topic |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
 Posted - 2010-01-26 : 07:55:24
|
| Hi Folks, A recently posted question has prompted me to ask this. What in your opinion is best way to store images which is involved in project. Is it best to store in db itself using BLOB data type or store it in file system and just store the path alone in a varchar variable? Or what about using FILESTREAM access in sql 2008? My guess is that by storing in file system modifications etc will be much easier rather than by means of BLOB update operations. Also size of db will be much less if images are kept outside.I would like to hear your opinion on this |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 08:01:27
|
In DB - Cons:Images are static (mostly) so bloating database, and backups. If images are on filesystem then they can be backed up using differential systems (to detect just the files that have changed)Very unlikely to be cached in the user's browser, so worse performanceIIS will have to retireve the iamge from DB, so more bandwidth used between IIS Server and SQL Server.Pros:Atomic - add image and associated data to the database in a transactionImages cannot get "lost"  . Move database to new server that is all data.For secure images (e.g. only seen by people who are logged in) then the fact that the image is "served" from the APP means that there is no direct URL to it. (however, we do the same thing with images that are just ina separate folder that is NOT part of the WWW sub-folders, so this is a moot point)FILESTREAM:I don't know anything about this, but I've seen a few questions posted here about images having been deleted from disk location and the FILESTREAM thingie then having a bit of a hissy-fit! |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-26 : 08:15:23
|
I remeber reading a "Best Practice" not long ago, that images up to 256 kb are best stored in the database as VARBINARY(MAX), and images larger than 1MB are best stored in the file system. All images between these two values have to be individually determined.It seems to me FILESTREAM solves a lot of problems.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 08:21:03
|
| "It seems to me FILESTREAM solves a lot of problems."I am guessing, but does that provide for this?:"virtualise" the image through a DB request (e.g. for Office Application for CRUD maintenance) but still get the image direct from disk (e.g. for web site pages that have <img src="/images/ImageName.jpg"> in them)?I just don't imagine storing ANY image IN the DB because of the risk of it not being cached by the browser (and the cost of IIS pulling it across the wire from SQL, rather than just grabbing it of its local disk) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-26 : 08:34:54
|
With FILESTREAM, the image is stored on OS file system and thus direct accessible from web pages (if you set correct filestream access level). The file is also accessible from with T-SQL and can be used within transactions.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 08:36:41
|
| Best of both worlds then - although still allows someone to delete an image (from O/S file system) whereas an image within DB cannot "disappear" in that way) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 09:41:03
|
| Even better than, thanks for that.I thought I had read (on SQLTeam recently) of some files that had "gone missing" on FILESTREAM ? (I had assumed they had been deleted outside SQL Server, obviously not; I wonder how that came about ...) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-26 : 10:51:32
|
| Thanks for the valuable inputs guys |
|
|
|
sagitariusmzi
Posting Yak Master
113 Posts |
Posted - 2010-01-31 : 10:09:26
|
| Hi, Recently , i was doing the testing for storing the images in Filestream, i tried to Delete the filestream file at os level, that file was deleted successfully.I think there was a problem of some kind of access level,which access level should be used, so that file shouldn't be deleted from os level ?Regards |
|
|
|
|
Storage of Images for project