Storing images in SQL server and not in a directory

Question

Post reply

Storing images in SQL server and not in a directory

michael.rosqvist

Old Hand

Points: 379
More actions
December 16, 2004 at 5:02 am

#87123

Hi,
I am seeking pros and cons, examples, and opinions whether it is a good idea
to store images in the database instead of in a directory structure.
The images in this case are tif:s, usually less than 50K, and about 100 000
per year.
I want to save the image and its meta data in the same table.
I think the pros are: dataconsistency, the ability to avoid dangling links,
consistent backups.
The drawbacks?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

See, if this helps:

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx

Or search the fora here. This is a FAQ in almost any SQL Server community.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jxflagg Hall of Fame Points: 3817 More actions · Answer 2

I would add to that that one of the motivations for putting images on a file server is cost. Your SQL Server data might be on very expensive SAN disk whereas your file servers might utilize something a bit cheaper.

EdVassie SSC Guru Points: 60472 More actions · Answer 3

There is not an easy answer to this question. You should consider the application and data retention issues in addition to 'pure' SQL Server issues before making a decision. The same issues relate to storing any large object, be it image, report, word doc, etc in a database.

Potential advantages of storing images in a database are:

All changes to the image and its metadata can be recorded in the transaction log, which gives an audit trail for later analysis.
It is normally far harder to accidentally delete an image from a database than in a filestore. On every image filestore application I have come across, there have been issues with images going awol (inadvertant drag and drop, deletion of the wrong image, ad-hoc work to relieve space constraint, etc etc).
It is often far easier to implement data retention and obsolesence policies to rows in a database than if the same data is hosted in multiple locations. This in turn can make legislative compliance easier.

There are a number of disadvanteges from a DBA viewpoint in storing images in a database:

Many DBAs are not familiar with managing image data, and take time to get up to speed with how to do this effectively.
Many SQL Server DBAs are worried about managing the large databases that will result. SQL can cope with the database size, but the use of multiple filegroups is needed. Alternatively try DB2 or Oracle that has a longer track record with VLDBs.
There can be a percieved cost issue in holding images in database-quality storage (e.g. RAID 5). However, if the images are business critical then they need the same resilience as a business critical database. If they are not BC, then the whole database can be stored on non-RAID storage.

You need to look at the problem as a whole, and get advice from the business, before making the technical decision on how to store image data.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara