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?

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  December 16, 2004 at 5:21 am

  #534126

  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

  December 16, 2004 at 12:52 pm

  #534232

  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: 60312

  More actions

  December 17, 2004 at 10:23 am

  #534346

  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