Using FileTables in SQL Server 2012

Using FileTables in SQL Server 2012

FileTables, a new feature introduced in SQL Server 2012, is a unique table that reflects metadata of files in a specified folder.

Enabling File stream at instances level.

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. 

The second method of enabling FileStream is by using the stored procedure "sp_configure". This stored procedure requires two parameters: "filestream_access_level" and the security level.

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

Go

If you attempt running the sp_configure stored procedure prior to configuring it at the instance level, you'll receive the following error:

Configuration option 'filestream access level' changed from 0 to 2. Run the RECONFIGURE statement to install.

Msg 5591, Level 16, State 1, Line 2

FILESTREAM feature is disabled.

Otherwise, when successful you'll receive the following message:

Configuration option 'filestream access level' changed from 0 to 2. Run the RECONFIGURE statement to install.

Provide a FileStream Filegroup
The next step is to provide a FileStream FileGroup. This can be incorporated in the CREATE DATABASE command

CREATE DATABASE FTableDB

ON PRIMARY

(Name = FTableDB,

FILENAME = 'D:\FTable\FTABLEDB.mdf'),

FILEGROUP FTFG CONTAINS FILESTREAM

(NAME = FTableFS,

FILENAME='D:\FTable\FS')

LOG ON

(Name = FTableDBLog,

FILENAME = 'D:\FTable\FTABLEDBLog.ldf')

WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,

DIRECTORY_NAME = N'FTableDB');

GO

After executing the above statement, you'll see the new additions to the "FTables" folder

A FileTable folder in Object Explorer that was created by default.

Enable Non-Transactional Access at the Database Level
Because FileTable allows modifications to be performed via Windows applications without requiring a transaction, I have to enable non-transactional access. To do so, I execute the SQL statement below. Note a directory name is specified -- this is the directory that will store files used with the FileTable.

ALTER DATABASE FTABLEDB

SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FTABLE')

Create a FileTable
The final step for configuring FileTables is to create the FileTable itself. This is accomplished by executing a bit of SQL:

Use FTABLEDB

Go

CREATE TABLE FTABLE AS FileTable

GO

Note that there was no structure specified for the table. The FileTable was created using the default schema containing 17 fields.

If you right-click the newly created FileTable ("FTABLE") and select "Explore FileTable Directory", a new window will open, directing you to the corresponding folder.

Now you can save files into databases those files will be automatically possible to see.