storing images in sql server

Question

I am trying to put together db design for storing images. Many of you might have had experience designing db to store images and the challenges associated with it.

The db might store hundreds of thousands of images eventually. I am planning to use SQL Server 2008 db and entity framework. Planning to use FILESTREAM datatype for storing images. Following is the list of attributes i have considered storing for every image in db.

Image Name, Image Type, Image Width, Image Height, Image Horizontal Resolution, Image Vertical Resolution, Image bit depth and finally actual image data.

Things i am trying to figure out: 1) I am curious, if i have missed out any other crucial attribute of an image. 2) What could be the impact while doing db backup as the images are stored on the file system due to use of FILESTREAM data type?

Thanks! Chan

Answer 1

backup will store the images in it to keep transactional consistency. as for the attribs it depends on what your needs are. i can't think of any other attributes you're missing though.

Answer 2

1) I may be making a wild assumption here, but if your images are going to be digital photo's then you may want to consider storing Exif metadata attributes that all(if not, 99%) digital cameras generate.

2) No impact in performing a backup with filestream, SQL Server does all the heavy lifting here. Filestream data will be part of your database .BAK file, so you wont need to do any other file system backups.

What you may have not considered is that as FILESTREAM is a new feature & thus can't be used with some other sql server features such as:

• database mirroring
• Replication (can't replicate filestream columns)
• database snapshots

Answer 3

I would also put in the encoding (jpeg, gif, png, bmp...), the MIME type (image/png) etcetera, so you don't have to parse the file name every time; also the file size (if you need to display it).