SQL 2012 : Enabling Filestream

Filestream launched in SQL 2012 and introduces the concept of a FileTable.
This is a directory structure (folder) viewable from SQL.

Here's a quick walkthrough to see Filestream in action.

1) Enable Filestream at the INSTANCE Level by -

Launching SQL Server configuration manager
Navigate to the properies of the instance service and enable via the Filestream tab
Chose the appropriate options -

• Enable FILESTREAM for Transact-SQL access
• Enable FILESTREAM for file I/O streaming access (Also provide a share name)
• Allow remote clients to have streaming access to FILESTREAM data.

Launch a query window in Management Studio and run

EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO

2) Add a Filestream filegroup at the database level 

ALTER DATABASE SandPit
ADD FILEGROUP fsTestFileGroup
CONTAINS FILESTREAM;
GO

3) Add a file to the filegroup 

ALTER DATABASE SandPit
ADD FILE
(
NAME='fsTestFile',
FILENAME='C:\fsTestFile'
)
TO FILEGROUP fsTestFileGroup;
GO

4) Enable Filestream on the database

ALTER DATABASE SandPit
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME ='fsTestFile')

5) Create a table 

CREATE TABLE fsTestTable AS FILETABLE
WITH
(
FILETABLE_DIRECTORY ='fsTestFile',
FILETABLE_COLLATE_FILENAME = database_default
);
GO

6) Test !!! 

SELECT * FROM fsTestTable;