One of the cool things about my job is that I get to work on the latest technologies earlier than most people. I recently stumbled upon an issue related to Filetables, a new feature in SQL Server 2012.
To start with, a Filetable brings you the ability to view files and documents in SQL Server, and allows you to use SQL Server specific features such as Full-Text Search and semantic search on them. At the same time, it also allows you to access those files and documents directly, through windows explorer or Windows Filesystem API calls.
Setting up Filetables
Here are some basic steps for setting up Filetables in SQL Server 2012:
CREATE DATABASE FileTableDB ON PRIMARY ( NAME = N’FileTableDB', FILENAME = N'C:\FileTable\FileTableDB.mdf' ), FILEGROUP FilestreamFG CONTAINS FILESTREAM ( NAME = FileStreamGroup1, FILENAME= 'C:\FileTable\Data' ) LOG ON ( NAME = N'FileTableDB_Log', FILENAME = N'C:\FileTable\FileTableDB_log.ldf' ) WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTables' )
ALTER DATABASE [FileTableDB] ADD FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM(NAME = FileStreamGroup1, FILENAME= 'C:\FileTable\Data') GO
ALTER DATABASE FileTableDB SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTables' ); GO
SELECT DB_NAME ( database_id ), directory_name FROM sys.database_filestream_options; GO
SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc FROM sys.database_filestream_options; GO
CREATE TABLE DocumentStore AS FileTable WITH ( FileTable_Directory = 'DocumentTable', FileTable_Collate_Filename = database_default ); GO
SELECT OBJECT_NAME(parent_object_id) AS 'FileTable', OBJECT_NAME(object_id) AS 'System-defined Object' FROM sys.filetable_system_defined_objects ORDER BY FileTable, 'System-defined Object'; GO
So you get the best of both worlds: Accessing files through SQL, searching for specific words/strings inside the files from inside SQL, etc. while retaining the ability to access the files directly through a windows share. Really cool, right? I think so too.
A few points to remember:
Some useful references for Filetables: http://msdn.microsoft.com/en-us/library/gg492089.aspx http://msdn.microsoft.com/en-us/library/gg492087.aspx
Hope this helps. Any comments/feedback/suggestions are welcome.