0

[ 1 ], [ 2 ] and many other references gives conclusion that BLOBs with size less tha 0.3-1 MB should be stored in database while BLOBs larger than 1 MB - in file system (possibly through FILESTREAM).

FACEBOOK.com stores 6.5 (30 with thumbnails and size versions) billions pictures in files system [ 3 ].

Randal's white paper [ 2 ] reasons it by:
alt text

Well, this does not convince me about necessity to store blobs in database but just about the fact that they should not be accessed/streamed externally through T-SQL but rather through Win32.

Other reasons, I did read, also do not look as convincing to me.

What are your reasons to store blobs in database instead of in file system?

[ 1 ]
Ray Barley . Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008
http://www.mssqltips.com/tip.asp?tip=1489
[ 2 ]
Paul S. Randal
FILESTREAM Storage in SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc949109(SQL.100).aspx
[ 3 ]
James Hamilton's Blog article. Facebook: Needle in a Haystack: Efficient Storage of Billions of Photos
http://perspectives.mvdirona.com/2008/06/30/FacebookNeedleInAHaystackEfficientStorageOfBillionsOfPhotos.aspx

Gennady Vanin Геннадий Ванин
  • 10,136
  • 11
  • 75
  • 106
  • 1
    Don't forget the [`To BLOB or not to BLOB`](http://research.microsoft.com/apps/pubs/default.aspx?id=64525) research paper by MS. – Oded Dec 08 '10 at 12:41
  • @Oded, I did not, it is heavily cited in [2]. Really all reasons are duped from there – Gennady Vanin Геннадий Ванин Dec 08 '10 at 13:59
  • @Mitch Wheat, I am glad you asked it. The question is the phrase/statement before the interrogation symbol "?" http://www.google.ru/images?hl=ru&newwindow=1&q=interrogation+symbol&um=1&ie=UTF-8&source=univ&ei=7pD_TOWcDMSZ8QP2taiMCw&sa=X&oi=image_result_group&ct=title&resnum=4&ved=0CEAQsAQwAw . And "?" is just before citation [1] in my post – Gennady Vanin Геннадий Ванин Dec 08 '10 at 14:12

1 Answers1

4

There can be many advantages to storing such data in a database rather than a filesystem:

Data integrity - you can implement constraints on it

Transaction support

Manageability - backup and restore options

High availability - databases tend to have more sophisticated options for mirroring and clustering than file systems do.

Security - you get the kind of fine-grained security controls that you do not with a filesystem

Accessibility - tables within a database are accessible to all sort of applications for presentation and analysis purposes. Databases can easily be interrogated with SQL statements

Audit - you can record and monitor changes to data

I expect there are many, many other reasons but these are the ones that first came to mind.

nvogel
  • 24,981
  • 1
  • 44
  • 82