New Features - SQL 2008: Filestream
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 filesystem. 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 filesystem, 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
)
Thats 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
Posted by - Christoff Truter
Date - 2010-08-21 19:31:56
Post comment