New Features - SQL 2008: Filestream

August 21, 2010 by 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:

  • Go to Start menu
    • Navigate to All Programs
    • Navigate to Microsoft SQL Server 2008
    • Navigate to Configuration Tools
    • Click on SQL Server Configuration Manager
  • Within SQL Server Configuration Manager
    • Click on SQL Server Services
    • Right click on the SQL Server instance you wish to enable filestreaming
    • Select properties
  • Within Properties
    • Click on the Filestream tab
    • Tick Enable FILESTREAM for Transact-SQL access



      • Enable FILESTREAM for file I/O streaming access tick shares the filestream within Windows
      • Allow remote clients to have streaming access to FILESTREAM data tick allows remote users to access the share
    • Click on Ok

Run the following commands on the master database of the SQL server instance you enabled the filestream feature on (note that its required to restart the instance after running these commands).

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

For the purpose of this post create a folder named demo, the following query will create a filestream enabled database.

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'
)

A table using the filestream will look something like this:

CREATE TABLE [dbo].[documents](
	[documentID] [uniqueidentifier] PRIMARY KEY ROWGUIDCOL NOT NULL DEFAULT NEWID(),
	[name] [varchar](255) NULL,
	[data] [varbinary](max) FILESTREAM  NULL
)

That's pretty much the basics around setting up the Filestream feature within SQL 2008.

If you're unsure about how to retrieve/stream/add files to the filestream, click here for some clues.

Additional Reading:
http://research.microsoft.com/apps/pubs/default.aspx?id=64525
http://msdn.microsoft.com/en-us/library/cc949109(SQL.100).aspx


Leave a Comment