SQL Server 2012 File Tables feature and the Benefit With SharePoint

SQL Server 2012 comes with an amazing new feature called “File Tables”.

MICRO SQL

That means that you can use a file system folder as a database table with all the fun and of cause nearly all the function of a database table. You have to enable the prerequisites for FileTable (as described here:) and the result is a new table in your database with the following properties:

NICK 1

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

Column Name

Data Type

stream_iduniqueidentifier ROWGUIDCOL
file_streamvarbinary(max) FILESTREAM
namenvarchar(255)
path_locatorhierarchyid
creation_timedatetimeoffset(7)
last_write_timedatetimeoffset(7)
last_access_timedatetimeoffset(7)
is_directorybit
is_offlinebit
is_hiddenbit
is_readonlybit
is_archivebit
is_systembit
is_temporarybit

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

Column NameData Type
parent_path_locatorhierarchyid
file_typenvarchar(255)
cached_file_sizebigint

You can query, update, insert or delete this table the same way as a “normal” table from you database with T-SQL. You also can use a fulltextindex and so on.

So in the context of SharePoint, especially with BCS you are now able to integrate file system data in you SharePoint without writing code and without growing up you SharePoint contend database with BLOB data. You can use file system data like documents, multimedia files or whatever as a SharePoint External List, integrate it in you Search environment and using SharePoint Features.

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

SQL Server

For example we can do this using SharePoint Designer. First step is 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:

So here is my solution:

SQL Server

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:

SQL Server

Clicking the “Link” given to each entry opens the document / folder. The access happens as shown in the picture 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.

Webcast with hands on system demos: http://youtu.be/ZwoO98btDPQ

Nicki Borell (http://nbsharepointtalk.blogspot.com  ) is an evangelist & consultant in the Experts Inside team. He has worked for more than 9 years in Microsoft enterprise environments and also as a trainer and consultant for the SharePoint and SQL Server products. His expertise extendes from technical consulting all the way to project management, with his core competencies covering KMU, enterprise environments and government data management. Nicki is Microsoft Certified System Engineer (MCSE), Database Administrator (MCBA), IT Professional (MCITP) and Trainer (MCT).”

Check out our resource centre for more SharePoint content from our SharePoint specialists!

Gain instant access to our SharePoint content by following us on twitter or facebook.

Share this on...

Rate this Post:

Share: