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.