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.
Configure and Enable Filestream using SQL Server Configuration Manager
- Click the Start Menu | Programs | Microsoft SQL Server 2008 | Configuration Tools | SQL Server Configuration Manager
- In the left panel, click on SQL Server Services which will list all the SQL Server 2008 related services in the right panel
- Click on the SQL Server (MSSQLSERVER) Service Instance on which you want to enable FILESTREAM; and right click to see the properties of the service
- In the SQL Server Properties dialog box, click the FILESTREAM tab
- Select Enable FILESTREAM for Transact-SQL access checkbox, which will enable rest of the options
- If you want to provide read and write FILESTREAM data access from windows then select the option to Enable FILESTREAM for I/O streaming access and enter the name of the windows share
- The last option, Allow remote clients to have streaming access to FILESTREAM data will allow remote clients to access FILESTREAM data on the server.
- Click Apply to save the changes
Configure and Enable Filestream using TSQL Query 1. In the SQL Server Management Studio, click the new query window and type the below TSQL Query
Use Master
Go
EXEC sp_configure ‘filestream access level’, 2
Go
RECONFIGURE
Go
SQL Server 2008 supports 3 levels of FILESTREAM access
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