In my previous blog post I wrote about what FILESTREAM and FileTables are. In this post I explain how to set FILESTREAM and FileTables up.

To enable and change FILESTREAM settings

1. On the Startmenu, select All Programs, select the SQL Server folder, select Configuration Tools, and then click SQL Server Configuration Manager.
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Managersnap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Propertiesdialog box, click the FILESTREAM
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name
8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
9. Click Apply.
10. In SQL Server Management Studio, click New Queryto display the Query Editor.
11. In Query Editor, enter the following T-SQL code:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

12. Click Execute.
13. Restart the SQL Server service.

Next we need to add a File Group to the DB with FILESTREAM and then add a File into the File Group.

USE MASTER
GO

ALTER DATABASE ImageFilesDB
ADD FILEGROUP ImageFG1 CONTAINS FILESTREAM;
GO

ALTER DATABASE ImageFilesDB 
ADD FILE 
(
    NAME = ImageStorage,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ImageStorageFG1.ndf'
)
TO FILEGROUP ImageFG1;
GO

Once you have created the File Group and add a file you will need to set the FILESTREAM File Group as the default in the database properties. (Right-click the database, select Properties, select Filegroups in the left hand pane, check the default checkbox in the FILESTREAM section)

Next you need to check whether non-transaction access is enabled on the database.

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

If it is not enabled on the database we need to enable it

ALTER DATABASE ImageFilesDB
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'ImageTable')
Once non-transactional access is enabled on the database we can create a FileTable

CREATE TABLE ImageStore AS FILETABLE 
    WITH ( 
          FileTable_Directory = 'ImageTable',
          FileTable_Collate_Filename = database_default
         );
GO

Now that everything is set up the last thing to do is to add files to our FileTable. There are two methods of doing this, either through T-SQL code or by exploring the FileTable from SQL Management Studio.

T-SQL method:

DECLARE @img AS VARBINARY(MAX)
SELECT @img = CAST(BulkColumn AS VARBINARY(MAX)) 
FROM OPENROWSET(BULK 'C:\temp\testimage.jpg', SINGLE_BLOB ) AS x
 
INSERT INTO dbo.ImageStore(name, file_stream)
SELECT 'testimage.jpg', @img
 
SELECT * FROM dbo.ImageStore

Management Studio Method:

1. Expand the FileTables node under Tables in your database
2. Right-Click the FileTable you created
3. Select Explore FileTable Directory

A shared network location will open up where you can paste images/files into.