Working with FileTables in SQL Server 2012 – Part 1- Configuration

Enabled filestream from the SQL server Services and restart the SQL Server services for the changes to take effect.
FT_1

— Enable Filestream, by default its set to 0 (disabled)

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

FT_2

After configuration change take effect the “FILESTREAM Access Level” will change to “Full access enabled”

FT_3

Add FileStream FileGroup and file to the Database

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [FST_FG] CONTAINS FILESTREAM 

Add file to the FileStream filegroup

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] ADD FILE ( NAME = N'FTS_Doc_1', FILENAME = N'C:\dsf\FTS_Doc_1' ) TO FILEGROUP [FST_FG]
GO

Configure Database for FileStream

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'documents_dbprox' ) WITH NO_WAIT
GO

Script to check databses enabled for Filestream option

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

Create FileTable Table

GO
CREATE TABLE FileTableTb AS FileTable WITH
(FileTable_Directory = 'documents_dbprox');
GO

FT_4

FT_5

Right click on the file table and click on “Explore FileTable Directory”. This will open up the folder where the FileTable data will be stored.

FT_6

FT_7

Copy files to the share \\Sinpc114\mssqlserver\documents_dbprox\documents_dbprox

FT_8

Leave a Reply

Your email address will not be published. Required fields are marked *