Dienstag, 17. April 2012

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:

Kommentare:

  1. Very cool idea, but I can see that the link would not be correct for files inside folders.

    AntwortenLöschen
  2. Hi,
    yes, you a right. There are some ways to solve it. You can only view files, no folders with a WHERE clause in the view… “…WHERE filetyp is not NULL” for example. Another option can be aggregating the correct links with an IF clause in the SQL view which generate separate links for folders. Or you use a separate BCS confection for subfolder. It really depends on the structure and the nesting of the file system. What I show is a demo of how it can work, not a final solution. For really complex enterprise environments a 3th party product basing on this technique would be the best. For example the DocAve Content Connector or so…
    Regards Nicki

    AntwortenLöschen