Dot Net Thoughts

SQL Server 2008 comes with lots of new features compared to the previous versions of SQL Server. One of the new feature is FileStream, which allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system.

You can get more details about this in MSDN : FILESTREAM Storage in SQL Server 2008

Enable Filestream in SQL Server

By default the Filestream feature will be disabled. You can enable the filestream using SQL Server Configuration Manager under SQL Server 2008 > Configuration Tools. In this you will get all the SQL Server services. Select the Properties of the instance and select the Tab “FileStream”, from that you can enable the FileStream, you can also specifiy the instance name also.

How to enable FileStream

You can also do it via T-SQL statement also

EXEC sys.sp_configure N'filestream access level', N'2'
RECONFIGURE

After doing this SQL Server will create a shared folder in your machine(or in Server) with the instance name specified. (Or it will create the Windows Share name we are specifying in the textbox) Only SQL Server can access the contents.

Network

You can check this via command prompt, using “Net Share”, you will get an output like this.

Net share command output

Using Filestream in the Database.

For using Filestream in your database you have to add file group in New Database screen.

Enable filestream for new Database

Or you can do this via TSQL like this

CREATE DATABASE FileStreamDemo
ON PRIMARY
   (NAME = FileStreamDemo,
      FILENAME = N'D:\DB\FileStreamDemo_data.mdf'),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
   (NAME = FileStreamDemo,
      FILENAME = N'D:\DB\FileStreamDemo')
LOG ON
   (NAME = 'FileStreamDemo_log',
      FILENAME = N'D:\DB\FileStreamDemo_log.ldf');
go

After doing this, SQL Server will create Folder in “D” drive, with name FileStreamDemo under DB directory. This FileStreamDemo folder will contains two files

1. filestream.hdr – This is the FILESTREAM metadata for the data container.
2. The directory $FSLOG. This is the FILESTREAM equivalent of a database’s transaction log.

Creating a Table with FILESTREAM Data
You can create a Table for consuming FileStream like this.

CREATE TABLE SQLFileSystem
(
FileId UNIQUEIDENTIFIER  ROWGUIDCOL UNIQUE DEFAULT NEWID() PRIMARY KEY,
FileName VARCHAR(255),
FileContents VARBINARY(MAX) FILESTREAM  NULL default (0x)
)

Thats it, you have created SQL Server Database and Table with Filestream.