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.
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.
You can check this via command prompt, using “Net Share”, you will get an output like this.
Using Filestream in the Database.
For using Filestream in your database you have to add file group in New Database screen.
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
- filestream.hdr – This is the FILESTREAM metadata for the data container.
- 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.