How To : SQL 2012 Filetable Setup and Usage

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:

  1. 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.

  2. 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”.

  3. Create a database in SQL (exclusively)for Filetables (preferable to using an existing database), and specify the WITH FILESTREAM option. Here’s an example:

    CREATE DATABASE FileTableDB
    ON  PRIMARY 

        NAME = N’FileTableDB', 
        FILENAME = N'C:FileTableFileTableDB.mdf' 
    ), 
    FILEGROUP FilestreamFG CONTAINS FILESTREAM     

        NAME = FileStreamGroup1, 
        FILENAME= 'C:FileTableData' 

    LOG ON 

        NAME = N'FileTableDB_Log', 
        FILENAME = N'C:FileTableFileTableDB_log.ldf' 

    WITH FILESTREAM 

        NON_TRANSACTED_ACCESS = FULL, 
        DIRECTORY_NAME = N'FileTables'
    )

  4. Alternatively, you can add a Filestream Filegroup to an existing database, and then create a Filestream directory for the database:

    ALTER DATABASE [FileTableDB] ADD FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM(NAME = FileStreamGroup1, FILENAME= 'C:FileTableData')
    GO

    ALTER DATABASE FileTableDB
        SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTables' );
    GO

  5. To verify the directory creation for the database, run this query:

    SELECT DB_NAME ( database_id ), directory_name
        FROM sys.database_filestream_options;
    GO

  6. 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):

    SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
        FROM sys.database_filestream_options;
    GO

  7. 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.
    Example:

    CREATE TABLE DocumentStore AS FileTable
        WITH (
              FileTable_Directory = 'DocumentTable',
              FileTable_Collate_Filename = database_default
             );
    GO

  8. 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):

    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

  9. 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:
    \Harsh2k8ENT2012FiletablesDocumentTable

  10. Next, copy files over to this share, and see the magic:
    select * from DocumentStore

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:
    https://msdn.microsoft.com/en-us/library/gg509086.aspx

    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:
https://msdn.microsoft.com/en-us/library/gg492089.aspx
https://msdn.microsoft.com/en-us/library/gg492087.aspx

Hope this helps. Any comments/feedback/suggestions are welcome.