FILESTREAM Feature on SQL Server 2008

How To Enable FILESTREAM Feature On SQL Server 2008

FILESTREAM is a new feature of SQL Server 2008 that supports BLOB file storage on NTFS file shares managed by SQL Server.  The FILESTREAM feature is disabled during the default SQL Server 2008 installation.

To enable FILESTREAM feature on SQL Server 2008:

1. Open SQL Server Configuration Manager (Start > Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager)
2. Navigate to the SQL Server Services node and select the SQL Server instance you want to modify SQL Server (MSSQLSERVER)
3. Click the FILESTREAM tab and select the checkboxes to enable FILESTREAM and enter a share name for the files, as shown

Next

 In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

 Click Execute.

 Restart the SQL Server service.

Create a database FileStreamEUE with file Stream

The following code example creates a database that is named Archive. The database contains three filegroups: PRIMARY, Arch1, and FileStreamGroup1. PRIMARY and data1 are regular filegroups that cannot contain FILESTREAM data. FileStreamGroup1 is the FILESTREAM filegroup.

T-SQL

CREATE DATABASE FileStreamEUE

ON

PRIMARY ( NAME = data1,

   FILENAME = 'c:\data\data1.mdf'),

FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = FileStr,

   FILENAME = 'c:\data\filestream1')

LOG ON  ( NAME = FileStr1,

   FILENAME = 'c:\data\fileStream_log.ldf')

GO

Here an example to create tables and inserting. we will create a table to store a FILESTREAM, then insert a few rows into it.

Before we create a table, some requirements necessary for FILESTREAM to work. First must have a special column that FILESTREAM uses to uniquely identify the stream. It must be a unique not null identifier of type ROWGUIDCOL. If we specify a default of NEWSEQUENTIALID the column becomes self  maintaining. When we insert a new value into the row, SQL Server takes care of creating a GUID for us and we essentially can ignore the column.

USE FileStreamEUE

GO

CREATE TABLE DocRepository(

 ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY

, FileStreamID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID()

, DocumentExtension VARCHAR(10)

, DocumentName VARCHAR(256)

, Document VARBINARY(MAX) FILESTREAM DEFAULT(0x)

);

GO

Here the column “FileStreamID” will become the required column for FILESTREAM.

Note the column name is not important, I could have called it “FSID”, “FSIdentity”, or any. You will also note I created an ID column for our use in normal day to day operations. This is not a requirement of FILESTREAM, just good practice.

There is a second requirement however. For the column that will be storing the documents, it must be VARBINARY(MAX), and it must add the FILESTREAM clause.

You will also note the default of “0x” (hex 0). This will be important if you wish to insert a new row without supplying the document at the time the row is created. It will create a file to act as a placeholder until such time as the real document is supplied.

You can also alter an existing table to add FILESTREAM capabilities. Simply use the ALTER TABLE command, add the unique identifier column (in this example “FileStreamID”) and the VARBINARY(MAX) column to hold your data (“Document” in the above example).

Inseting a record

INSERT INTO DocRepository(DocumentExtension, DocumentName, Document)

SELECT

'doc' AS DocumentExtension

, 'Databases Auditing.doc' AS DocumentName

, * FROM OPENROWSET(BULK 'G:\EMEADocs\Databases Auditing.doc', SINGLE_BLOB)

  AS Document;

GO

Here we inserted a new row into the table, and have ignored the “ID” and “FileStreamID” letting SQL Server create the values. The “DocumentExtension” and “DocumentName” columns are straightforward.

To supply the document however, we need to use an OPENROWSET. This will supply the data from a disk file as a single BLOB (Binary Large OBject).

We will verify what was just inserted with this query:

SELECT ID,FileStreamID, DocumentExtension AS Ext, DocumentName

    , CAST(Document AS VARCHAR) as DocumentData

FROM DocRepository;

Output:-

ID	FileStreamID	Ext	DocumentName	DocumentData
2	B92A4C03-CDCA-E211-98B0-92FFB6238584	doc	Databases Auditing.doc	ÐÏࡱá

As mentioned previously, it is also possible to insert a new row, then add the document later. To insert a new row into our example table, we simply use with xls format.

INSERT INTO DocRepository(DocumentExtension, DocumentName)

VALUES ('xls', 'PROD SQL DBSES.xls');

Output:-

ID	FileStreamID	Ext	DocumentName	DocumentData
2	B92A4C03-CDCA-E211-98B0-92FFB6238584	doc	Databases Auditing.doc	ÐÏࡱá
3	3DB8E4A6-CDCA-E211-98B0-92FFB6238584	xls	PROD SQL DBSES.xls

Now that the row exists, we can update it.

UPDATE  DocRepository

SET     Document = ( SELECT *

                    FROM OPENROWSET(

                     BULK 'G:\EMEADocs\PROD SQL DBSES.xls',

                     SINGLE_BLOB) AS Spreadsheet)

WHERE   ID = 2 ;

This will uses in client applications.