Setting Up FILETABLE and FILESTREAM access on MS SQL Server

Enabling FILESTREAM on the Server Instance:

1. Open SQL Server Configuration Manager:

• SQL Server 2017 C:\Windows\SysWOW64\SQLServerManager14.msc
• SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc
• SQL Server 2014 (12.x) C:\Windows\SysWOW64\SQLServerManager12.msc
• SQL Server 2012 (11.x) C:\Windows\SysWOW64\SQLServerManager11.msc

2. Click SQL Server services in the left pane. Right click the instance in the right pane and choose "Properties"

3. Click on the FILSTREAM tab and

• Check Enable FILSTREAM for Transact-SQL access
• Check Enable FILSTREAM for file I/O access.
• Check Allow remote clients access to FILESTREAM data.
• Optional: Change the Windows Share Name. Note: Files will be accessible via UNC at \\[ServerName][WindowsShareName]

Click OK to save changes.

Enable Non-Transactional File Access on the Server Instance.

According to the official documents, you should be able to perform this step by running the following code:

EXEC sp_configure filestream_access_level, 2  
RECONFIGURE  

On two different SQL instances (2012 and 2014) this did not work.

If it does now work you can enable using Sql Server Management Studio:

1. In SQL Server Management Studio right click the instance and choose properties.
2. In the Server Properties window, click advanced and locate the FILESTREAM Settings.
3. Change FILESTREAM Acess Level from disabled to FULL.

Create A FILESTREAM Enabled Database

Create a database with a FILESTREAM filegroup.

As an example:

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'c:\data\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'c:\data\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'c:\data\archlog1.ldf')
GO

Alternately, you can use the Stored Procedure here to simplify the process. https://gist.github.com/alexhiggins732/acd66090322358aa2e510c02f642a0c9

Usage:

exec SP_CreateFileStreamDatabase 'FileRepo', 'c:\dms\', 1

Output:

CREATE DATABASE FileRepo
    ON   
	PRIMARY ( 
		NAME = [FileRepoPrimary],    
		FILENAME = 'c:\dms\FileRepodata1.mdf'),   
		FILEGROUP [FileRepoPrimaryGroup1]    
	CONTAINS      
		FILESTREAM     
		(      
		NAME = [FileRepoFileStreamPrimaryGroup],      
		FILENAME = 'c:\dms\FileRepoFileStreamFileStream1'     
		)    
	LOG ON       
	(      
		NAME = [FileRepoFileStreamlog],      
		FILENAME = 'c:\dms\FileRepoFileStreamlog1.ldf'     
	)     

The procedure accepts three parameters:

 - @DatabaseName nvarchar(100) -- Specifies the name of the database to create. Also use to consistently format file names and file groups.
 - @RootDirectory nvarchar(250), -- The root directory for the file group. This directory must exist and will be the location the share is created. 
 - @GenerateOnly Bit - The procedure generates a SQL statement similar to the example above. When set to true the SQL will be executed. You can set this to false 	to preview the generated SQL and optionally modify and manually execute it if you wish.

Enable Database Level Non-Transactional File Access:

1. In SQL Server Management Studio right click on the database just created and choose properties.
2. Click options and in the FILESTREAM section change FILESTREAM Access to "FULL" and supply a directory name.

Create A File Table

You can now create FileTables in the database. Each will show up as directory in \\[ServerName][WindowsShareName][DirectoryName] where you will be able to work with the files as you would from a normal file share.

CREATE TABLE Documents AS FileTable  
WITH (   
	FileTable_Directory = 'Documents',  
	FileTable_Collate_Filename = database_default  
	);  

Verify you can browse to \\[ServerName][WindowsShareName][DirectoryName][FileTable_Director] and work with files directly in windows explorer.

Use the library

You can now use this library to work with and modify Files as if they were regular files in the file system.