Configure Filestream in SQL Server 2008

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio
2. In Object Explorer, right click the server and select properties to view the Server Property window
3. Under Advanced, Click on the drop down next to Filestream Access Level and select Full access enabled
4. To do this the user needs to be a member of the sysadmin or serveradmin fixed server role to change the default settings for Filestream
5. Click Ok to save changes

How to create a Filestream Database
The FILESTREAM feature uses a special type of filegroup when you create the database. You need to specify the CONTAINS FILESTREAM clause for at least one filegroup in the create database statement.

Use Master
Go
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'FileStreamDB')
DROP DATABASE FileStreamDB
GO

USE Master
GO
CREATE DATABASE FileStreamDB ON PRIMARY
  ( NAME = FileStreamDB_Data,
    FILENAME = N'D:\FileStream\FileStreamDB_Data.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 15%),
 FILEGROUP MyDBData
  ( NAME = MyFileStream_Data,
    FILENAME = N'D:\FileStream\FileStreamDB_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB),
FILEGROUP FileStream CONTAINS FILESTREAM
  ( NAME = FileStream,
    FILENAME = N'D:\FileStream\FileStreamData')
LOG ON
  ( NAME = 'FileStreamDB_Log',
    FILENAME = N'D:\FileStream\FileStreamDB_Log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB);
 GO 

The image below shows the properties of FileStreamDB Database which was created by executing the above TSQL statement:



The below snippet displays the files which got created for FileStreamDB database in D:\FileStream folder. You will see a folder named FileStreamData which will store all the varbinary (max) values.



Within the D:\FileStream\FileStreamData folder you can see a sub folder named $FSLOG and a file named filestream.hdr, this file is a header file for the FILESTEAM container.