My name is Harsh Deep Singh, and I am a SQL Server Premier Field Engineer with Microsoft. I've been working on SQL Server for quite some time, and the intention behind starting this blog is to try and share my knowledge with the SQL Server community. Any feedback or suggestions on the blog are both welcome and solicited. Please feel free to drop me a line on firstname.lastname@example.org anytime.
Disclaimer: The information in this weblog is provided “AS IS” with no warranties, and confers no rights. This weblog does not represent the thoughts, intentions, plans or strategies of my employer. It is solely my opinion. Feel free to challenge me, disagree with me, or tell me I’m completely nuts in the comments section of each blog entry, but I reserve the right to delete any comment for any reason whatsoever (abusive, profane, rude, or anonymous comments) – so keep it polite, please.
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.