Just a day ago I received phone call from my friend in Bangalore. He asked me
What do I think of storing images in database and what kind of datatype he should use?
I have very strong opinion about this issue.
I suggest to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.
Storing the database location reduces the size of database greatly as well updating or replacing the image are much simpler as it is just an file operation instead of massive update/insert/delete in database.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Yes, it is very much easier to manage the image’s path than the image. But, how can we secure the image from being not accessible without the usage of database. Is that the responsibility of the administrator to protect those images?
There are situations where storing images in the database, alongside the data they concern, is an excellent idea.
For example, in the medical field, it’s very frequent that an image and its metadata are a single logical blob.
You don’t want to have “pointers” to images that can be altered or removed behind your back. You don’t want users to add or delete medical information outside transactions. You don’t want to possibly deal with two different security systems (one for the database, one for the file system). You don’t want to split backup or fall back strategies.
Nothing prevents you to link two separate databases and to keep blobs even on a separate server (DTC is your friend), one for data and one for images. That’s what we do in our application (400GB of data, without images).
Hey,
I used to think that storing the binary files in the file system and the links in the DB columns is the way to go, but there are times when you want more reliability, you don’t want someone changing the file names or moving them somewhere else.
Was just wondering on the performance implication of storing the binary files in a DB versus storing them in a file system. you have any clue about this?
Yes its gr8 idea , i was using that technique for single desktop applications. at present m working on a multi-user distributed application ( centrilized system). And m facing problem with this techinique because the Remote Location is password protected ( the pc where databse is installed ) , and if i do provide access to that Machine , it will allow to access the resources of that computer , will u plz suggest me the settings by which i can restrict users to access resources of that computer and and can allow only reading images from a particular picture folder( contains images )………..plz guide me
I am new in this field
You suggest to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.
How I can do this ?
I am using dreamweaver VB scripting with asp.net to developing the sites.
Thanks
can you please demonstrate on how to do that. I need to store file path to different types of files such as pdf, doc, audio, video, etc. I have never done it and not sure where to even start on that. i would greatly appreciate it.
thanks
emilia
The challenge behind the directory structure is security and if you’re in a SOX environment its hard for the developer to have all of the tools to properly configure the file server, the web server, etc. all in the name of what? Performance?
If performance weren’t the kicker, the database seems like the way to go (assuming its sql 2k5, which argues that its faster than oracle up to the TB). With the DB I know where my files are and I have one less thing I need to do…(assuming your files do not change; such as imaging files).
Therefore, an arguement shouldn’t be made w/o some kind of performance justification…so please would the smart guy in the room tell me how much faster a file server is versus a database server? If its nominal the answer should be what’s the least expense for your organization to maintain? In a SOX cause, I would argue the db approach…
Storing images on the database, consumes the available capacity of the database files.
For example, if you have 2000 medical records, with ten 5MB images for each record (a medical record could have more than 50 images, but I just chose a small number), then 10GB of the database available size is consumed for the images.
For larger databases, that could be a problem.
Correction for the previous post:
It’s 100GB, not 10GB.
can you please demonstrate how to store the location (URL) in table. I have never done it and not sure where to even start on that. i would greatly appreciate it.
thanks
I am trying to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.
How I can do this ?
I am using visual studio 2005 VB.net with asp.net to developing the sites.please guide me how to solve.it’s urgent
You might want to create two fields, one for the path, one for the filename. As an example, store ‘patient\images\’ as the path, and ‘Patientimage1.jpg’ as the image name.
Instead, you could store the path in another table if it is the same for all images, or images of a certain type. That way you could save different paths for different types of files, such as .PDF or .GIF.
I like the idea of storing in the database, as this keeps the system from having orphan images, and keeps backups and restores current (keeping records and images together).
I would have to disagree with the author about a one size fits all of storing images on the file system rather than in the database. It all comes down to my favorite answer, “It depends!”
How about this senario, a shop that has over 20 million images stored on a file system, each of the images are under 1 Mb in size. If you look at it, strictly from a DBA’s prespective, then yes, storing files on the file system and having the pointers stored in the DB sounds reasonable. Unfortunatly, we live in the real world here and it you looked at it from a backup and recovery standpoint, how long would it take a backup system to backup over 20 million small files. Many, many times longer than it would take to backup one large file.
Bottom line, is before you make a descion to go one way or the other, make sure you have factored in ALL options.
Marc –
For your situation, it is acutally recommended to store those images in SQL Sever and not on the file system. SQL Server processes files under 1 MB at a rate of 2x’s faster than that of NTFS. So in your case, it would actually be a benefit to place these images on the server. In contrast, it is not recommended to store files over that 1 MB threshold as you then start to get into potential performance issues and fragmentation of the database.
I’m working on a project. Actually, the software is ment for Radiology department. The software suppose to keep all cinical information, and medical images. (e.g. Ultrasound image, CT, MRI )
I’m intend to use C# 2005 with MS SQL server 2008.
Please where do you suggest I should store the medical images of the patient?
in the database or on the file system.
Please I need help.