The FileTable feature builds on top of SQL Server FILESTREAM technology.Enabling the Prerequisites for FileTable

To enable the prerequisites for creating and using FileTables, enable the following items.

Enable FILESTREAM at the Instance Level

• Powershell

CLS
$instance="MSSQLSERVER"

$wmi = Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement11" -Class FileStreamSettings | where {$_.InstanceName -eq $instance} 

$wmi.EnableFilestream(3, $instance)

• Restart SQL Server Services

Enable using SQL Server Configuration Manage

r

Modify the Registry Key to enable FileStream

By default [EnableLevel] is set to 0, change it to 3 and restart the SQL Server Services.SQL Server Services will create the Share Name by itself.

• Connect to SQL Server instance from SQL Server Management Studio and Enable FILESTREAM for file I/O streaming access.

OR

sp_configure 'filestream access level',2
reconfigure with override

At the database level: Add a FILESTREAM Filegroup at the Database Level

ALTER DATABASE [Database Name] ADD FILEGROUP [FS] CONTAINS FILESTREAM

Enable Non-Transactional Access at the Database Level.Specify a Directory for FileTables at the Database Level

l

T-SQL

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

Checking Database File Stream Status

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

Administrative Considerations about FILESTREAM and FileTables

You configure FileTables separately from FILESTREAM. Therefore you can continue to use the FILESTREAM feature without enabling non-transactional access or creating FileTables.There is no non-transactional access to FILESTREAM data except through FileTables. Therefore, when you enable non-transactional access, the behavior of existing FILESTREAM columns and applications is not affected.
About FileTables and non-transactional access

• You can enable or disable non-transactional access at the database level.
• You can configure or fine-tune non-transactional access at the database level by turning it off, or by enabling read only or full read/write access.