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 (https://blog.sqlauthority.com)
17 Comments. Leave new
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.
Check out the below link to enable file stream at instance leve.
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
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.
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?
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?
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’.
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
Would Like to know where the Index is getting stored when Full-text indexing is enabled for Filetable data-type
Sir,
How to use filestream with SQL Server 2008
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.
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
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?
I want this table(FileTable ) by a foreign key relationship with another table
It was unclear to me whether the actual data inside the file is stored in the file system, or inside sql server? I’m assuming in the file system, and what is inside sql server is just the metadata about the filesystem file. Is that correct?
Hi I am getting the error when i execute the script
IF NOT EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N’SpendIntegration_FileStorage’
)
BEGIN
CREATE TABLE SpendIntegration_FileStorage AS FILETABLE WITH(FILETABLE_DIRECTORY = ‘SpendIntegrationFiles’, FILETABLE_COLLATE_FILENAME = database_default);
END
Error is
Msg 33405, Level 16, State 12, Line 8
An error occurred during the create table operation on a FileTable object. (HRESULT = ‘0x80070490’).
I have filestream enable on the server, and other dbs are working fine.
Could you please help me out ?
Thanks
Gajanan