SQL Server 2012 Filestream, Filetable and the added value with SharePoint 2010

I wrote about the new SQL Server 2012 Filetable feature based on Filestream, which was already been coming with the last version SQL Server 2008, before (see here: LINK ). Now let’s see Filetable together with SharePoint in action.

First we have to activate Filetable which is not been active per default after installing SQL Server 2012. How to activate this can be found here: LINK .

My settings are:

-          There is a database named “FileStreamDemo”.

-          In the “options” menu you can see that there is a Filestream directory named  “FileStreamData”

-          There is a table called “dbo.FileTableData” which points to the FileStreamData directory:

CREATE TABLE [dbo].[FileTableData] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [FileStreamDemoGroup1]

WITH

(

FILETABLE_DIRECTORY = N'FileTableData', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS

)

-          NON_TRANSACTED_ACCESS = FULL, that allowed us interact with the data in the filetable directory bypassing the SQL Server API and use the windows explorer direct.

-          The script in the background shows the create script for the database. We can see that the database files *.mdf and *.ldf are placed in a different directory than the Filestream data.

I placed several DEMO files in the Filestram directory:

If we now query the table “dbo.FileTableData” the result is:

The columns are fixed and base on the propertys coming from the NTFS file system:

Column Name	Data Type
stream_id	uniqueidentifier ROWGUIDCOL
file_stream	varbinary(max) FILESTREAM
name	nvarchar(255)
path_locator	hierarchyid
creation_time	datetimeoffset(7)
last_write_time	datetimeoffset(7)
last_access_time	datetimeoffset(7)
is_directory	bit
is_offline	bit
is_hidden	bit
is_readonly	bit
is_archive	bit
is_system	bit
is_temporary	bit

Each FileTable includes also these computed (read-only) columns:

Column Name	Data Type
parent_path_locator	hierarchyid
file_type	nvarchar(255)
cached_file_size	bigint

So we see that the content from FileStream directory is returned as BLOB data placed in the column “file_stream” as varbinary(max). By the way: the data placed in here is automatic part of the backup and restored process.

If we now use BCS to connect SharePoint to this database table we have two issues:

1.       We are not able to bring the content directly to the user without writing code that extracts / transforms the BLOB data.

2.       Using SharePoint to unwrap the BLOB data generates load on our SharePoint. The data must be load from SQL Server / Filesystem to SharePoint and from the SharePoint Server to the Client.

These two issues will have the same answer:

Let’s point to the data in the file-system using the given information. That means only the metinformation where the file is placed is transferred and the client picks up the data directly from where it is stored.

-          In scenario 1 the BLOB data is transferred via SharePoint.

-          Scenario 2 shows the way only the metinformation (blue arrows) is transferred via SharePoint and the user access the content directly.

Scenario 2 is what I will show now. The metinformation where the data is located is not part of the data in the “dbo.FileTableData” table. Also there are some columns we do not need to transfer via BCS to our SharePoint. So let’s write a SQL query which is given as the needed and filtered information:

1.  SELECT

2.  stream_id,

3.  name,

4.  file_type,

5.  cached_file_size,

6.  creation_time,

7.  last_write_time,

8.  last_access_time,

9.  is_directory,

10. 'file:\\DEMO2010A\FileStream\FileStreamData\FileTableData\' + name AS Link

11. FROM dbo.FileTableData

The line 10 brings the “magic” within this. This T-SQL call generates for every entry in the “dbo.FileTableData” table a link to the original data in the Filesystem. This link is attached as a new column called “Link”:

This query is now used to create a view called “FileTableView”.

Next step is now connection this view via BCS to SharePoint. I do this using SharePoint Designer to create a new External Content Type. How to create External Content Types and handling BCS is not part of this post. If you need help within this just contact me or have a look here: LINK .

So here is my solution:

As you can see I created an External Content Source called “FileTableData” which is based on the view “FileTableView”. For this use case I only need a ReadList and a ReadItem operation.

Publishing this External Content Type to a SharePoint External List (and of course doing some XSL customizing) generates a list looking like this which shows exactly the content of the FileStream directory:

Clicking the “Link” given to each entry opens the document / folder. The access happens as shown in PIC 4 directly to where the content is stored.

Resume:

This is an interesting way bringing data into a SharePoint environment without blowing up the content databases of SharePoint. This data can also be protected with SQL Server backup and recovery tools. But of course there a several 3th Party Tools doing this ore similar thing with more features around. Anyway, for me it’s a create feature and an additional option in data management.

See this post as webcaste here: