FILESTREAM Data Type

The new FILESTREAM data type in SQL Server 2008 offers a new way of storing unstructured data (images, documents, video, etc.). Up until now the two standard ways to store such data have been either in the database as BLOB objects or as files outside of the database. FILESTREAM provides the best of both worlds, storing unstructured data in the file system while maintaining transactional consistency with other structured data.

Here is a summary of the new capabilities offered by FILESTREAM:

• Implemented as VARBINARY(MAX)
• No limitation of 2 GB
• Stored in the file system
• Fast read access
• Good for objects larger than ~ 1 - 2 MB
• Encryption is not supported
• Setting to NULL deletes the BLOB data
• Transactional consistency
• Windows access via the OpenSqlFilestream APIs (read/write; no delete or rename)
• Uses Windows cache, not using SQL Server buffer pool, more memory for queries
• A ROWGUIDCOL column is required to use FILESTREAM data with Win32 APIs

Enabling FILESTREAM:

EXEC sp_filestream_configure 
     @enable_level = 3, 
     @share_name = "FileStreamShare";

Levels:

0 - Disabled. This is the default value
1 - Enabled only for Transact-SQL access
2 - Enabled only for Transact-SQL and local file system access
3 - Enabled for Transact-SQL, local file system access, and remote file system access

Creating database supporting FILESTREAM and table with FILESTREAM column:

CREATE DATABASE BlobDatabase ON PRIMARY 
(NAME = Blob, FILENAME = 'C:\Data\Blob.mdf'),
FILEGROUP FSGroup CONTAINS FILESTREAM 
(NAME = BlobFS, FILENAME = 'C:\Data\FileStream')
LOG ON ( NAME = BlobLog, FILENAME = 'C:\Data\Blob.ldf');
 
 
CREATE TABLE BlobDatabase.dbo.Images (
 image_id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, 
 image_desc VARCHAR(35),
 image_data VARBINARY(MAX) FILESTREAM NULL);

Update (August 20, 2008)

There have been some changes in the RTM version of SQL Server 2008. To enable FILESTREAM in the release version go to SQL Server Configuration Manager, open the Properties of the instance, on the FILESTREAM tab select Enable FILESTREAM for Transact-SQL access. Then open a query and execute the following:

EXEC sp_configure filestream_access_level, 2;
RECONFIGURE;

The stored procedure sp_filestream_configure used in the prerelease versions has been removed. Now the sp_configure procedure is used as demonstrated above. The new access level values are as follows:

0 - Disables FILESTREAM support for this instance (default)
1 - Enables FILESTREAM for Transact-SQL access
2 - Enables FILESTREAM for Transact-SQL and Win32 streaming access

In addition, one note from the field - turning off the 8.3 name generation on the NTFS volume using the command prompt utility fsutil helps a lot on performance.

Additional resources:

FILESTREAM Overview
http://msdn.microsoft.com/en-us/library/bb933993(SQL.100).aspx

Designing and Implementing FILESTREAM Storage
http://msdn.microsoft.com/en-us/library/bb895234(SQL.100).aspx

Labels: , , ,