How To : SQL 2012 Filetable Setup and Usage

  • Article

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.

Comments

  • Anonymous
    March 06, 2013
    Hi, While clicking on "Explore File Table Directory" we receive the following error: The File location cannot be opened. Either access is not enabled or you do not have permissions for the same. On you blog post you mentioned that "The only exception is using a SQL Server login that has sysadmin privileges (in which case it will impersonate the SQL Server Service account). " Please suggest how can we achieve this ?

  • Anonymous
    March 07, 2013
    Hi skan2dan, Thanks for showing interest in the blog. To use Filetables with a SQL server login (as opposed to windows one), you need to give it the sysadmin role in SQL Server. To do this, you have to right on the login in the object explorer in SQL Server Management Studio, navigate to the roles tab, and check the sysadmin role. Hope this helps. Regards, Harsh

  • Anonymous
    March 13, 2013
    @skan2dan maybe you need check you firewall.make sure the TCP port 139,445 is allow in.

  • Anonymous
    April 16, 2013
    Harshdeep Singh, Thanks for the reply. As you advised, I assigned SysAdmin privilege to the SQL Server Log in. Still the same error. Then, I have done the impersonation by following steps.

  • Created a Local Windows Log in  account namely "OSLOGIN"  on the Windows Server with admin privileges.
  • Changed & assigned this Windows Log in as the service account of SQL Server & Agent Services.
  • Impersonated the SQL Login namely "SQLLOGIN" with SQL Server's Service Account by the following command. use [master] GO GRANT IMPERSONATE ON LOGIN::[OSSERVEROSLOGIN] TO [SQLLOGIN] GO But, still the issue is persisting and the error is : " The File location cannot be opened. Either access is not enabled or you do not have permissions for the same". Kindly review and suggest to overcome this issue. Thanks in advance.

  • Anonymous
    April 17, 2013
    Hi Skan2dan, Thanks for getting back on this. Can you please try using a windows login to access the Filetable share first, to confirm that the share has been created successfully and is accessible? Regards, Harsh

  • Anonymous
    April 17, 2013
    Harshdeep_Singh, If I add the Windows OS Log in with SysAdmin privileges to the SQL Server instance then I am able to open the File table directory in either way. i.e. 1. by selecting the "Explore File Table Directory" option from SSMS or 2. accessing the Filetable share first, here it is \MYSERVERNAMEmssqlserverDocumentStoreMyDocumentStore. So, it is confirmed that the Filetable share has been successfully created. My problem here is we need to use a SQL Server authentication with least privileges to access the Filetable share as we need to provide the credentials to the end users.

  • Anonymous
    April 21, 2013
    Hi skan2dan, Thanks for getting back. In that case, I need you to confirm on 2 things:

  1. Please ensure that the SQL login has sysadmin privileges (direct, not through a credential).
  2. Please make sure that you test this with both Local System and domain accounts as SQL Server and SQL Agent service accounts. Regards, Harsh

  • Anonymous
    December 04, 2013
    I found that you have to enable Filestream Access Level on the instance i.e. in SSMS right click on the Instance > Properties > Advanced > FILESTREAM > Full Access enabled I am using windows authentication

  • Anonymous
    December 04, 2013
    Hi Stuart, Thanks for the input. The steps I've outlined worked for me on SQL 2012 RTM, but I found that the additional step you mentioned is needed on my SQL 2012 SP1 installation. I've edited the blog to include this step as well. Thanks again. Regards, Harsh

  • Anonymous
    July 14, 2014
    to access the files via share (with normal non-admin permissions) you need also to grant "Control" permission on the filetable to the database role where your users are in, of course they need also SELECT (UPDATE/INSERT/DELETE) permission. Without "control" permission you will get the "The File location cannot be opened. Either access is not enabled or you do not have permissions for the same." Error... Unfortuately i didn`t found this documented anywhere...So maybe this information is useful for someone.

    • Anonymous
      March 29, 2016
      grant control to [user] does work (im using virtual dir access that points to fstream dir) but still get the error msg when clicking on "Explore File Table Directory". This might be an issue with ssms/windows trying to access fstream dir, so don't rely on it.

  • Anonymous
    August 14, 2014
    I attempted to run the provided code (4.) to add a FileStream FileGroup to my existing database, but I keep getting a syntax error at 'FILESTREAM(NAME = FileStreamGroup1' telling me incorrect syntax near 'NAME'. Am I missing something here?

  • Anonymous
    August 18, 2014
    Hi Dennis, Thank you so much for sharing that piece of information. I will include it in the blog. Thanks again.

  • Anonymous
    August 18, 2014
    Hi Josh...thanks for showing interest in the blog. Can you please confirm if you copied the code from here and modified it? If yes, I would request you to write out the statements yourself, as sometimes the font can cause issues when copied over directly into SSMS. Hope this helps.

  • Anonymous
    November 10, 2014
    HarshDeep_Singh please see the following - I am getting push back because of TechNet - technet.microsoft.com/.../bb933993(v=sql.105).aspx   ----  STATING BELOW Only the account under which the SQL Server service account runs is granted NTFS permissions to the FILESTREAM container. We recommend that no other account be granted permissions on the data container. However, this is contradicting what you are stating... - "At the same time, it also allows you to access those files and documents directly, through windows explorer or Windows Filesystem API calls." can you please get the TECHNET documentation updated correctly to ensure that other accounts should be able to access the FileSystem API, as I am trying to get the IIS AppPool account access to read/write files to the filestream directory.  I was going to stream from SQL Server, however there is an issue with an PDF api that we have that causes the creating to take 2 minutes vs. milliseconds from reading using I/O. Please also, please get a Microsoft Employee to reply to my TechNet forum. --- social.technet.microsoft.com/.../permission-to-filestream-directory-on-msdn-question

  • Anonymous
    November 17, 2014
    Hi Robert, I believe there might be some confusion with regards to the difference between the terms Filetable and Filestream. This blog post discusses only Filetables, a new feature introduced in SQL 2012. While Filetables do use Filestream technology, the fundamental concepts are different. Filetables will allow you to access the files on the file system, even with an account that's different from the account under which SQL Server is running. However, this is not the case with using regular Filestream containers, as explained in the TechNet article you mentioned above. I hope this clarifies your doubts. Please let me know if I can assist further. Regards, Harsh

  • Anonymous
    January 09, 2015
    The comment has been removed

  • Anonymous
    January 14, 2015
    Hi Gonzalo, I am sorry that you did not find the blog useful. Allow me to clarify that this blog is targeted for professionals who work with SQL server on a regular basis, and are familiar with the concept mentioned in the blog. For the sake of keeping the content within scope, it was not possible to go about defining every concept/term used in the post. If any of the terms are unclear, you're always welcome to search for it on bing/google, and use the TechNet/msdn articles provided by Microsoft to gain an understanding of the concept. Regards, Harsh

  • Anonymous
    March 31, 2015
    Hi: Windows Domian Users cannot access the files through UNC inspite of granting  SELECT,DELETE,UPDATE,INSERT permissions on the file table. Should we restart the SQL Service after running this query? Thanks