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:
  4. 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’
    )

  5. Alternatively, you can add a Filestream Filegroup to an existing database, and then create a Filestream directory for the database:
  6. 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

  7. To verify the directory creation for the database, run this query:
  8. SELECT DB_NAME ( database_id ), directory_name
        FROM sys.database_filestream_options;
    GO

  9. 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):
  10. SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
        FROM sys.database_filestream_options;
    GO

  11. 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:
  12. CREATE TABLE DocumentStore AS FileTable
        WITH (
              FileTable_Directory = ‘DocumentTable’,
              FileTable_Collate_Filename = database_default
             );
    GO

  13. 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):
  14. 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

  15. 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
  16. 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:
    http://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:
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.

Comments (19)

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

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

  3. xudong125 says:

    @skan2dan maybe you need check you firewall.make sure the TCP port 139,445 is allow in.

  4. skan2dan says:

    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.

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

  6. skan2dan says:

    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.

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

  8. Stuart says:

    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

  9. HarshDeep_Singh says:

    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

  10. Dennis says:

    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.

    1. ms says:

      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.

  11. Josh says:

    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?

  12. HarshDeep_Singh says:

    Hi Dennis,

    Thank you so much for sharing that piece of information. I will include it in the blog. Thanks again.

  13. HarshDeep_Singh says:

    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.

  14. Robert says:

    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

  15. HarshDeep_Singh says:

    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

  16. Gonzalo says:

    Not very useful.   You basically describe the steps for someone who is familiar with the concepts, but you do not explain what the different components are.  What is a File Group?  Why is it needed?  What is the filestream at the server level?  Etc.   Just repeating a bunch of steps without a clear understanding of  what you are doing is quite useless.

  17. HarshDeep_Singh says:

    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

  18. Chetak Patil says:

    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