SQL SERVER – Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment

Filestream is a very interesting feature, and an enhancement of FileTable with Filestream is equally exciting. Today in this post, we will learn how to set up the FileTable Environment in SQL Server.

The major advantage of FileTable is it has Windows API compatibility for file data stored within an SQL Server database. In simpler words, FileTables remove a barrier so that SQL Server can be used for the storage and management of unstructured data that are currently residing as files on file servers. Another advantage is that the Windows Application Compatibility for their existing Windows applications enables to see these data as files in the file system. This way, you can use SQL Server to access the data using T-SQL enhancements, and Windows can access the file using its applications. So for the first step, you will need to enable the Filestream feature at the database level in order to use the FileTable.

-- Enable Filestream
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO
-- Create Database
CREATE DATABASE FileTableDB
ON PRIMARY
(Name = FileTableDB,
FILENAME = 'D:\FileTable\FTDB.mdf'),
FILEGROUP FTFG CONTAINS FILESTREAM
(NAME = FileTableFS,
FILENAME='D:\FileTable\FS')
LOG ON
(Name = FileTableDBLog,
FILENAME = 'D:\FileTable\FTDBLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'FileTableDB');
GO

Now, you can run the following code and figure out if FileStream options are enabled at the database level.

-- Check the Filestream Options
SELECT DB_NAME(database_id),
non_transacted_access,
non_transacted_access_desc
FROM sys.database_filestream_options;
GO

You can see the resultset of the above query which returns resultset as the following image shows. As you can see , the file level access is set to 2 (filestream enabled).

Now let us create the filetable in the newly created database.

-- Create FileTable Table
USE FileTableDB
GO
CREATE TABLE FileTableTb AS FileTable
WITH
(FileTable_Directory = 'FileTableTb_Dir');
GO

Now you can select data using a regular select table.

SELECT *
FROM FileTableTb
GO

It will return all the important columns which are related to the file. It will provide details like filesize, archived, file types etc.

You can also see the FileTable in SQL Server Management Studio. Go to Databases >> Newly Created Database (FileTableDB) >> Expand Tables

Here, you will see a new folder which says “FileTables”. When expanded, it gives the name of the newly created FileTableTb.

You can right click on the newly created table and click on “Explore FileTable Directory”. This will open up the folder where the FileTable data will be stored.

When you click on the option, it will open up the following folder in my local machine where the FileTable data will be stored:

\\127.0.0.1\mssqlserver\FileTableDB\FileTableTb_Dir

In tomorrow’s blog post as Part 2, we will go over two methods of inserting the data into this FileTable.

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

18 thoughts on “SQL SERVER – Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment

  1. HI Pinal,

    i had enabled filestream option
    o/u:Configuration option ‘filestream access level’ changed from 2 to 2. Run the RECONFIGURE statement to install.

    but i m trying to execute 2 step creating db

    i m getting below error:
    Msg 5591, Level 16, State 1, Line 1
    FILESTREAM feature is disabled. can you please suggest me.

  2. Hi Sir,

    Error:
    FILESTREAM feature is disabled.

    i find it how to enable this one in MSDN
    pelase follow below

    In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.

    Right-click the instance, and then click Properties.

    In the SQL Server Properties dialog box, click the FILESTREAM tab.

    Select the Enable FILESTREAM for Transact-SQL access check box.

    If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

    If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

    Any thing wrong please correct me.
    Thanks

  3. 1.In Newly created table i had clicked on Explore file table Directory in system it showin message “THIS FOLDER IS EMPTY”

    2. after database created

    File Table DB 2 Full

    What is mean by 2 and FULL
    and other databases are showing 0 why ? is there any option 1 also.
    please expline bit more.

  4. Sorry sir, I not get very clear from the description and where could I use it for any useful purpose. Could you please provide and useful scenario with any example and also a smooth step wise desction how to setup this feature enabled in sql server?

  5. Pingback: SQL SERVER – Working with FileTables in SQL Server 2012 – Part 2 – Methods to Insert Data Into Table « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Working with FileTables in SQL Server 2012 – Part 3 – Retrieving Various FileTable Properties « SQL Server Journey with SQL Authority

  7. Is the inverse possible? I.e. store the files on the NTFS and expose it through SQL Server (like remote blob storage), or MUST the files actually be stored in SQL?

  8. when i write query as —>

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

    and

    select * from sys.filetables

    it returns error

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.database_filestream_options’.

    Msg 208, Level 16, State 1, Line 2
    Invalid object name ‘sys.filetables’.

  9. Hi,
    This is awesome information abt new feat in SQL SERVER 2012.But just curious to know if any real time application can you let me know…..

    thanks
    kamesh

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

  12. Excellent place! Thank Pinal!!!!!

    My name is Juan. I love SQL, but my knowledge is about Microsoft Access. Is possible create a link odbc Access with SQL File Table technology? I need to add and read documents (PDF, Word, Excel, Videos, Photos, etc).

    Please, give me some advice.

  13. Sir, Me having the setup of windows Server 2012 and SQL 2012. using file streaming.

    I want to do the Sql mirroring on BACKUP Server. I want to know the proceedure.Please help me in this regard.
    warm regards./deepak

  14. when i used filetable to create a folder and the folder have over 390GB data with heavy access. The folder will not show up sometimes and I need to restart the SQL Server. Have you seen this issue?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s