CODEWITHSUNDEEP
sql-serverdatabasedatabase-designstorageblob
Gennady Vanin Геннадий Ванин
Gennady Vanin Геннадий Ванин

Reputation: 10424

your reasons to store blobs less than 0.3 MB in database?

[ 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

Upvotes: 0

Views: 509

Answers (1)

nvogel
nvogel

Reputation: 25534

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.

Upvotes: 5

Related Questions