Configure Filestream in SQL Server 2008

SQL Server 2008 introduces a new feature called FILESTREAM which enables SQL Server based applications to store unstructured data such as documents, images, audios, videos etc. in the file system. FILESTREAM basically integrates the SQL Server Database Engine with New Technology File System (NTFS); it basically stores the data in varbinary (max) data type. Using this data type, the unstructured data is stored in the NTFS file system and the SQL Server Database Engine manages the link between the Filestream column and the actual file located in the NTFS. Using Transact SQL statements users can insert, update, delete and select the data stored in FILESTREAM enabled tables.

Configure and Enable Filestream during SQL Server 2008 Installation
SQL Server DBA’s can enable the FILESTREAM feature in SQL Server 2008 during the installation process. The option to enable FILESTREAM will be available in the Database Engine Configuration screen of the SQL Server 2008 setup. To enable FILESTREAM for Transact-SQL access, a DBA needs to select Enable FILESTREAM for Transact-SQL access option. Once the option is enabled all the other access controlling options will be available for configuration. Select Enable FILESTREAM for file I/O streaming access option to enable Win32 streaming access for the files. Then you need to provide the name of the windows share where the FILESTREAM data will be stored for access. Select the Allow remote clients to have streaming access to FILESTREAM data control, in case you plan to allow remote clients to access this FILESTREAM data on the server. In the below snippet we have enabled the FILESTREAM and also provide a windows share name for allowing file I/O streaming access.

During SQL Server 2008 installation, if you forget to enable the FILESTREAM feature, then the feature can be enabled after the installation by following the methods below.

a. If the value is 0 then, FILESTREAM support for the instance is Disabled
b. If the value is 1 then, FILESTREAM for Transact-SQL Access is Enabled
c. If the value is 2 then, FILESTREAM for Transact-SQL and Win32 streaming access is Enabled