Working with SQL Server File Tables – part 1

Did you ever think that your SQL server can work as a file server? The odds are that you didn’t – neither did I until I tried a FileTable feature first introduced in SQL Server 2012. The FileTable feature is the next step in the FILESTREAM technology that allows you to store files and folders in the special tables but access them using traditional Windows SMB protocol – users can create/read/modify/delete files from a generic file share without even being aware of the underlying SQL commands. In other words, FileTables let us use SQL tables for non-transactional access.

In my opinion, one of the greatest benefits of FileTables feature is the storing of file attributes (such as Last-WriteTime, Last_AccessTime, etc.) that allows administrators to audit file access operations using the corresponding columns in the FileTables. In this article I’d like to show you how SQL Server 2012-2016 can be used for the file storage and what auditing mechanisms may be leveraged for files and folders residing in the SQL file shares.  Some prerequisites must be met before File Tables can be created in a database and we’ll go over all the steps required to prepare for File Tables, then create several tables and see how we can work with them.

Let’s start from enabling the FILESTREAM for the SQL 2012 instance and creating a new FILESTREAM database:

https://msdn.microsoft.com/en-us/library/gg509097%28v=sql.120%29.aspx

I Enable FILESTREAM at the Windows level

  1. Open SQL Configuration manager:
    02
  2. Make sure the following checkboxes are selected:
    03

II Enable FILESTREAM at the SQL Instance level:

  1. In SQL Server Management Studio, click New Query to display the Query Editor.
  2. In Query Editor, enter the following Transact-SQL code:
    EXEC sp_configure filestream_access_level, 2
    RECONFIGURE04

III Provide a FILESTREAM Filegroup at the Database Level

Before we can create FileTables in a database, the database must have a FILESTREAM filegroup. For more information about this prerequisite, please see Create a FILESTREAM-Enabled Database.
Since I’m going to create a new database I will first create a folder to contain FileTables:
01
and then define a Filestream filegroup in this code:

CREATE DATABASE FILESERVER
ON
PRIMARY (NAME = FS,
FILENAME = ‘c:\FILESERVER\FSdat.mdf’),
FILEGROUP FileStreamFS CONTAINS FILESTREAM(NAME = FSgr,
   FILENAME = ‘c:\FILESERVER\fs1’)
LOG ON (NAME = FSlog,   FILENAME = ‘c:\ FILESERVER \FSlog.ldf’)
GO

Attention! As MS says: “For a FILESTREAM filegroup, FILENAME refers to a path. The path up to the last folder must exist, and the last folder must not exist.” In the preceeding code, c:\FILESERVER must exist, but fs1 must NOT at the time you execute the CREATE DATABASE statement.

05

10

The Filegroups database property page now displays the FileStreamFS filestream for the FILESERVER database:
07

IV Enable Non-Transactional Access at the Database Level

Once we’ve got the database created the last step in preparing it for FileTables is to enbable non-transactional access at the database level.

FileTables let Windows applications obtain a Windows file handle to FILESTREAM data without requiring a transaction. To allow this non-transactional access to files stored in SQL Server, you have to specify the desired level of non-transactional access at the database level for each database that will contain FileTables. If we execute the following code –

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

…we’ll see that no databases have non-transactional access enabled:
08

To enable non-transactional access to files at the database level (for the FILESERVER database in this case) one more option must be specified – a directory for FileTables: it is the directory I’ve created in the begining of the article – C:\FILESERVER, but the directory name should be typed without the drive letter and the symbol “\”:

ALTER DATABASE FILESERVER
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’FILESERVER’ )
GO

09

In the FileTable folder hierarchy, this database-level directory becomes the child of the share name specified for FILESTREAM at the instance level, and the parent of the FileTab les created in the database” – it means FILESERVER will be the subfolder of MSSQLSERVER share as I havn’t changed it in step I-2. For more information, please see Work with Directories and Paths in FileTables

Executing this code again will show the non-transactional access has been enabled successfully:

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

09-1

The following code displays the directory name for the FILESERVER databse:

Select DB_NAME ( database_id), direcory_name
  FROM sys.database_filestream_options;
GO

11

Here’s the “SQL” file share that users can now use for storing their files and folders:10-11 10-12

If I had renamed the default MSSQLSERVER share into something like SHARE1 in step I-2 there would have been no indication of any SQL Server involved – from the user’s perspective it would be just an ordinary file share.

All prerequisites for creating FileTables are fulfilled now and we can proceed to creating FileTables.

Working with SQL Server File Tables – part 2

 

%d bloggers like this: