- Enable FILESTREAM for Transact-SQL access
- Enable FILESTREAM for file I/O access
- Allow remote clients access to FILESTREAM data
4. Execute the below mentioned Transact-SQL code in the SSMS.
EXEC sp_configure filestream_access_level, 2
5. Restart the SQL Server Instance.
Let’s create a demo database with a FILESTREAM filegroup, I’ll be setting the NON_TRANSACTED_ACCESS to FULL this gives me full non-transactional access to the share, this option can be changed later if you want to do that. Here is the code:
CREATE DATABASE MyFileTableTest
NAME = N'MyFileTableTest',
FILENAME = N'G:\Demo\MyFileTableTest.mdf'
FILEGROUP FilestreamFG CONTAINS FILESTREAM
NAME = MyFileStreamData,
NAME = N'MyFileTableTest_Log',
FILENAME = N'G:\Demo\MyFileTableTest_log.ldf'
NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'FileTable'
That’s the database done, now let’s create the magic table – that is simple done with the “AS FileTable” keyword. When creating a FileTable there is only two options that you can change, everything else is out of the box. Let’s have a look at how I created the table: