Enable Filestream in SQL Server instance

Filestream allows to store the large size object in windows file system. Using Filestream you can store any size of object as large as your hard drive size. The file is not stored in the database which provides following advantages:

1. Database performance is not impacted like in case of big files stored in the database.
2. Express Edition of the SQL Server can be used also for scenarios with large files since the data stored in the Filestream are not counted to the database size limit.
3. Back-up and restore takes less time because the size of the database is smaller.

There are following limitations:

1. Back-up and restore procedures has to reflect Filestream. Back-up and restore are performed differently for databases with Filestream.
2. Filestream enabled database cannot be used for mirroring.
3. Filestream data is not available in database snapshots.

Steps to enable Filestream in the database

You will perform couple of steps in the SQL Server Configuration Manager and then in SQL Server Management Studio.

SQL Server Configuration Manager

1. Open the SQL Server Configuration Manager from Start menu.
2. Select SQL Server Services on left panel and then select your SQL server from right panel and right click on it and go to Properties menu.
3. In property window go to Filestream tab. 
4.  Enable option Enable FILESTREAM for Transact-SQL access and other two options given below and give proper Windows share name. Click on Apply and OK button.

5. Restart the SQL Server service. Your feature is now enabled on your SQL server instance and now you have to give access level to the Filestream.

SQL server management Studio

1. Open SQL server management Studio and execute below command.

EXEC sp_configure filestream_access_level, 2  
GO  
RECONFIGURE  
GO  

Here “filestream_access_level” can have following values.
0 - FILESTREAM feature disable
1 - FILESTREAM feature enable for T-SQL
2 - FILESTREAM feature enable for both T-SQL and Win32 streaming access

Now our SQL server is enabled with Filestream feature.

2. Right click on ObjectGears database and select Properties. 
3. Go to Filegroups tab, section Filestream and click on Add button and enter name e.g. “FileStreamGP” (you can give any name here) and enable Default option as shown in below image.

4. Now go to Files tab. Click on Add button and this will add a new row to the upper grid.
5. Now give any Logical Name, in File Type select  FileStream Data and define Path (give any valid path on your windows file system).

6. Click on Ok button.  

Now your database is complete with FILESTREAM feature.