SQL Server 2012 comes with an amazing new feature called “File Tables”.
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:
The columns are fixed and base on the property coming from the NTFS file system. The columns are:
| 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 |
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
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:
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 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.
