SQL Server 2005 - Storing Images

scargill

Hi,

I have a requirement from a client to help them organise a library of images they keep. We provide them with software that manages their day-to-day business.
They have thousands of images related to their business stored in various folders and the are finding it difficult to manage them. (searching for particular images, inserting them into reports, etc)

I am trying to decide if it is best to store the images directly in the DB or to just store the URL pointing to them (and leave them sitting on disk).


Storing them in the DB has advantages and disadvantages...

Advantages
URL and folder contents could get out of synch if images are stored on disk.
Everything gets backed up together.

Disadvantages
DB could get very big.
Would need to write interface to get images in and out of database.

I am trying to decide the best course of action. If someone has done this before I'd appreciate your experience of it !

Thanks.

fasty

I've gone down this route a few times and I've learned that storing media files in the database is a nightmare. I've dealt with images, documents and mp3s and it just ends up being more trouble than it's worth.

DJB

Leave images in the file directory and store the location link within the database. You'll save so much trouble in development and down the line.

scargill

thanks fasty and djb,

What particular problems did you have ?

Client is leaning towards storing media in the DB, I think its a potential can of worms. All of the docs and articles I have come across on sqlmag.com, etc seem to be split 50/50.

A big problem that they are concerned about is that images will get stored in folders that other users don't have access to. (offices are spread worldwide)

And also they are concerned that users are sloppy about naming files, moving them around, etc. Forcing them to store the images in the DB will make sure this is properly controlled.

Ginger

Having written a document management system that was file agnostic, i can tell you its easier to store the files in a filesystem with a filesystem link to the file in the database. Storing them in the database is quite troublesome at times

Or use SharePoint for example so that they can index them with document/image libraries...

scargill

Ginger wrote: »

Or use SharePoint for example so that they can index them with document/image libraries...

Tell me some more about SharePoint. I had a quick look through the product guide on Microsoft website.

Will the users of my application still have to reference files on disk or can they reference media in the SharePoint repository ?

Thanks

Ginger

Files will get uploaded to the SharePoint server via a web interface. They will then be able to reference them via a URL which you can control security to via AD

You can store meta data about the image and then use the search facility to find it later

mewso

scargill wrote: »

And also they are concerned that users are sloppy about naming files, moving them around, etc. Forcing them to store the images in the DB will make sure this is properly controlled.

Force their hand on where it is saved and how it is named. No worse than forcing them to save it in sql. Either way I would assume there will be an interface. From there you can let them upload a file but how it is named and where it is saved is up to you. It might also be worth writing a web service to serve up the files to any remote location.

YellowMan

What particular problems did you have ?

First at all, it going to be image type, i.e. BLOB.
Your client has to know how to work with them - opening, reading, writing etc...
You also will face BLOB-related limitations of the ODBC drivers, for example, only one BLOB can be open per connection, etc...

Changing data will be a nightmare - you will need to extract object from the image file, write a stream into temporary file, get the link to this file to the client application then, when they finish changing your .doc or .jpg - read it and save back to the DB.

Don't even know how to keep ACID for images.

You also need to keep all images on the fast expensive DB disks - instead of keeping files on some cheap HDD.

Think about backup and resore time here.

Etc,etc,etc...

stevenmu

Was thinking sharepoint reading this too, seems ideal for your problem. All you'll need for the moment is a simple Windows Sharepoint Services 3 (WSS) install. And just add an image library to it. They can upload all their images into it, with or without folder structure, or you could use multiple image libraries just as easily. You can add extra columns simply to hold any extra metadata you want and search by file names, descriptions, meta-data etc.

There's a sample image library here, but note that it's setup very basically, and you won't see some features (uploading, editing, deleting etc) for permissions reasons.

If you have a windows server 2003 box (or VM) around it's simple to just download WSS, install it (pick default simplest options), open the site when done, go to "Site Actions", "Create", "Image Library" and then upload some images to try it out.


(There's also a demo of using silverlight to diaplay images from an image library, not sure what's involved with setting that up: http://www.wssdemo.com/Pages/silverlight.aspx)