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
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.