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:
- Enable Filestream for the instance in question from SQL Server Configuration Manager (Right click on the SQL Server Service-> Properties->Filestream-> Enable Filestream for Transact-SQL access). Also make sure you provide a Windows Share name. Restart SQL after making this change.
- Enable FileStream access from the SSMS GUI. Right click on the instance, go to properties->advanced, click on the drop down next to “FileStream Access Level” and select “Full access enabled”.
- Create a database in SQL (exclusively)for Filetables (preferable to using an existing database), and specify the WITH FILESTREAM option. Here’s an example:
- Alternatively, you can add a Filestream Filegroup to an existing database, and then create a Filestream directory for the database:
- To verify the directory creation for the database, run this query:
- Next, you can run this query to check if the enabling Non Transacted Access on the database was successful (the database should have the value ‘FULL’ in the non_transacted_access_desc column):
- The next step is to create a Filetable. It is optional to specify the Filetable Directory name. If you don’t specify one, the directory will be created with the same name as the Filetable.
- Next, you can verify the previous step using this query (don’t be daunted by the number of rows you see for a single object):
- Now comes the most exciting part. Open the following path in windows explorer:
\<servername><Instance FileStream Windows share name (from config mgr)><DB Filetable directory><Table Directory Name>
In our case, it will be:
- Next, copy files over to this share, and see the magic:
select * from DocumentStore
CREATE DATABASE FileTableDB
NAME = N’FileTableDB’,
FILENAME = N’C:FileTableFileTableDB.mdf’
FILEGROUP FilestreamFG CONTAINS FILESTREAM
NAME = FileStreamGroup1,
NAME = N’FileTableDB_Log’,
FILENAME = N’C:FileTableFileTableDB_log.ldf’
NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N’FileTables’
ALTER DATABASE [FileTableDB] ADD FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM(NAME = FileStreamGroup1, FILENAME= ‘C:FileTableData’)
ALTER DATABASE FileTableDB
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’FileTables’ );
SELECT DB_NAME ( database_id ), directory_name
SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
CREATE TABLE DocumentStore AS FileTable
FileTable_Directory = ‘DocumentTable’,
FileTable_Collate_Filename = database_default
SELECT OBJECT_NAME(parent_object_id) AS ‘FileTable’, OBJECT_NAME(object_id) AS ‘System-defined Object’
ORDER BY FileTable, ‘System-defined Object';
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:
- The Fielstream/Filetable features together give you the ability to manage windows files from SQL Server. Since we’re talking about files on the file system, accessing them requires a Windows user. Thus, these features will not work with SQL Server authentication. The only exception is using a SQL Server login that has sysadmin privileges (in which case it will impersonate the SQL Server Service account).
- Filetables give you the ability to get the logical/UNC path to files and directories. File manipulation operations (such as copy, cut, delete, etc.) can be performed from SQL Server. More details on that are available in the following TechNet article:
These operations can also be performed by your application, using file system API’s such as CreateFile or CreateDirectory. In this case, the onus is on the application to obtain a handle to the file using file system API’s. Filetables will only serve the purpose of providing the path to the application.
Some useful references for Filetables:
Hope this helps. Any comments/feedback/suggestions are welcome.