August 21, 2010 by Christoff Truter SQL
When developing systems, notably ones involving document mis.. uhm management, developers
are confronted with the question of where they should store their files.
A simplistic (easy manageable / codeable etc) approach is to simply store/stream the files
from a database - unfortunately this approach comes with a performance penalty especially
when working with large files.
What is considered large exactly? Well, according to research done by Microsoft -
"objects smaller than 256K are best stored in a database while objects larger than 1M are
best stored in the file system. Between 256K and 1M, the read:write ratio and rate of object
overwrite or replacement are important factors"
from http://research.microsoft.com/apps/pubs/default.aspx?id=64525
The alternative (like seen in the preceding quote) is to store files on the file system, which unfortunately introduces a number of manageability issues
like security, integrity etc.
In SQL 2008 however, Microsoft attempts to give us the best of both worlds in the form of the
Filestream feature, in which files(blobs) are stored/streamed via the NTFS file system
- all managed by SQL.
Lets have a look at how to use/enable this functionality.
By default this feature is disabled, in order to enabled it do the following:
EXEC sp_configure filestream_access_level, 2 RECONFIGURE
CREATE DATABASE FSDemo ON PRIMARY ( NAME = FSDemo, FILENAME = 'C:\demo\FSDemo.mdf' ), FILEGROUP FSDemoGroup CONTAINS FILESTREAM ( NAME = FSDemoGroup1, FILENAME = 'C:\demo\FSDemo1' ) LOG ON ( NAME = FSDemoLog, FILENAME = 'C:\demo\FSDemo.ldf' )
CREATE TABLE [dbo].[documents]( [documentID] [uniqueidentifier] PRIMARY KEY ROWGUIDCOL NOT NULL DEFAULT NEWID(), [name] [varchar](255) NULL, [data] [varbinary](max) FILESTREAM NULL )