When I first read about sql server 2008 filestream I don’t know why (or I know )
but my mind started to think about WSS/MOSS 2007 so why don’t try to use this new feature
for document libraries storage?

 

Before start, if you want to know all the detail about FILESTREAM read FILESTREAM Storage in SQL Server 2008
from MSDN site where you can find all the information you need.

 

OK let’s start:

 

First of all (if you didn’t enable FILESTREAM during SQL Server 2008 installation),
you have to perform these steps to enable FILESTREAM support to your SQL Server instance:

1. Open SQL Server Configuration Manager (Start-All Programs-SQL Server 2008-Configuration Tools).
2. Select SQL Services on the left, right click on SQL Server instance you are interested on and click properties
3. Select FILESTREAM tab. In this tab you have three options (I Choose all three options):
   • Enable FILESTREAM for Transact SQL Access: you can perform TSQL SELECT, INSERT UPDATE and DELETE operations
   • Enable FILESTREAM for file I/O Streaming access: you can access FILESTREAM data using WIN32 API
   • Allow remote client to have streaming access to FILESTREAM data: you can store/access data on/from a remote file share
4. Open SQL Server Management Studio and configure SQL Server to support FILESTREAM using this script:
      
       EXEC sp_configure filestream_access_level, 2;
       GO
       RECONFIGURE;
       GO
5. At this point open Sharepoint central administration, create a new Application with a content Database name (for exampe: WSS_Content_Filestream) and create a site collection for testing (I create a blank site).
6. Now you have to crate a FILESTRAM Filegroup for the new content database:
   • Go to management studio, right click on the new content database and select properties
   • Select Filegroups and create a new Filestream Filegrop name (for example FILESTORAGE)
7. Open SQL Server management studio and run this script:
     
     ALTER DATABASE WSS_Content_Filestream
     ADD FILE(
      NAME=FSGroupFile1,
      FILENAME=’c:\MyDBData\FSDATA’
     )
     TO FILEGROUP FILESTORAGE;
     GO
     Note: use your WSS content database name. Replace FILENAME value with your directory name. Replace the name FILESTORAGE with your Filegroup name.
     If all runs well you can go under your directory (c:\MyDBData\FSDATA) and notice the presence of filestream.hdr file for metadata and the
     $FSLOG directory for FILESTREAM database transaction log.
8. With management studio, open your WSS_Content database, expand Tables folder and right click the dbo.AllDocStreams table and generate
   the two scripts in new query windows for drop and create table (Script Table As – Drop And Create To – New Query Windows).
   If you go to the CREATE TABLE part, you will notice that dbo.AllDocStreams table has a column called Content of type image.
   For filestream usage, you have to use the varbinary(max) data type. You have also to add a new column to the table of type
   uniqueidentifier of type rowguidcol unique, not null and with default value of NewId() (I called this column DocumentId).
   So you have to change the CREATE TABLE part for the Content column from this:

     [Content] image NULL

       To this:

     [Content] varbinary(max) FILESTREAM NULL,
     [DocumentId] UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID()

          You also have to specify the FILESTREAM filegroup for that table:
          locate the ON [PRIMARY] instruction at the end of the CREATE TABLE and replace with this instruction:
  
          ON [PRIMARY] FILESTREAM_ON FILESTORAGE (NOTE FILESTORAGE is my FILESTREAM Filegroup name).

 

Et voilà, if you go to your filestream directory (c:\MyDBData\FSDATA) you will notice the presence of a new directory and a subdirectory.
One is created for the table and the other is created for the filestream column.

Now you can go yo your site collection, create a new document library, upload some documents,
go to the filestream directory on the file system and you will see your document files stored outside SQL Server!

 

Hope this helps
Vincenzo