FILESTREAM storage in SQL 2008 with ASP.net MVC Part 1/2

The FILESTREAM feature of SQL Server 2008, which allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system. It covers choices for BLOB storage, configuring Windows and SQL Server for using FILESTREAM data, considerations for combining FILESTREAM with other features, and implementation details such as partitioning and performance.

1. Enable FILESTREAM support

To enable FILESTREAM support, run the following SQL:

On  SQL Server 2008 Express:

USE master;

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

GO

RECONFIGURE WITH OVERRIDE

GO

 

On SQL Server 2008 Enterprise :

exec [sp_filestream_configure] @enable_level = 3;

This command enables or disables the support according to the parameter. The value 3 means that FILESTREAM will be enabled for Transact-SQL, local file system access, and remote file system access.

2. Create a database with a File Group that contains FILESTREAM

To create a database instance with a file group that contains FILESTREAM, run the following sql command. This creates the database with 3 file groups, but only one of them contains FILESTREAM as you can see in the command.

CREATE DATABASE CSAFMS

ON

PRIMARY (

    NAME = CSAFMS_Primary,

    FILENAME = 'D:\Databases\CSAFMS\CSAFMS.mdf'),

FILEGROUP FileStreamGroup CONTAINS FILESTREAM (

    NAME = FileManagement_FileGroup,

    FILENAME = 'D:\Databases\CSAFMS\CSAFMS')

LOG ON ( NAME = FileManagement_Log,

    FILENAME = 'D:\Databases\CSAFMS\CSAFMSLog.ldf')

GO

This command will create the following directories structure:

In the Data Directory, I could find the files specified above:

and in the CSAFMS directory, I could find the following content:

3. Create a Table with FILESTREAM

Note: FILESTREAM is not a type of a column, but it is a property you put on a varbinary(max) column.

So, in order to create a table with a varbinary(max) column that will be used for FILESTREAM, run the following command. Note that I don't have any additional columns to the file itself.

CREATE TABLE [dbo].[Files]

(

      FileID uniqueidentifier NOT NULL ROWGUIDCOL PRIMARY KEY,

      FileName Varchar(300) NOT NULL,

      Length bigint NOT NULL,

      FileContents varbinary(max) FILESTREAM DEFAULT NULL

)

4. Add Test Data

In order to get the feeling of the experience of working with files, let add an empty file:

insert into Files

(FileID,[FileName],Length,FileContents)

values (newid(),'Dummy File 2',0, CAST ('my test file' as varbinary(max)));

If we now query the database to see the files in it:

select * from Files

We will get the following result (the GUID will probably be different...)

FileID                               FileContents

------------------------------------ ------------------------------------------------

8247CE78-74DF-4BDE-A08D-9760AE0B1555 my test file

and If we look at the FileManagement directory, we can see that a new directory has been created