Did you forget to enable FILESTREAM during setup?

What is FILESTREAM?

Much data is unstructured, such as text documents, images, and videos. This unstructured data is often stored outside the database, separate from its structured data. This separation can cause data management complexities. Or, if the data is associated with structured storage, the file streaming capabilities and performance can be limited.

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

SQL Server 2008’s FILESTREAM support can be very handy, but often overlooked during installation.  It is disabled by default in the installer, but can be enabled at install time, and also post-installation.

Obviously, the easier option is to enable during installation, but if you missed it, the steps to enable are relatively straightforward.

Keep in mind that there are two areas you need to consider: SQL Server Configuration and SQL Server Management Studio – both are responsible for enabling Filestream, but for different reasons.

To enable and change FILESTREAM settings (from MSDN)

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the list of services, right-click SQL Server Services, and then click Open.

3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.

4. Right-click the instance, and then click Properties.

5. In the SQL Server Properties dialog box, click the FILESTREAM tab.

6. Select the Enable FILESTREAM for Transact-SQL access check box.

7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

9. Click Apply.

10. In SQL Server Management Studio, click New Query to display the Query Editor.

11. In Query Editor, enter the following Transact-SQL code:

    Copy

    EXEC sp_configure filestream_access_level, 2
    RECONFIGURE

12. Click Execute.

Notes

The reason for the two locations (Configuration Manager and SQL Management Studio) is that FILESTREAM needs to be enabled at the network configuration level, and at the database engine level.  If you miss one or the other, Filestream will not be accessible or enabled!

Also note that you cannot enable FILESTREAM on a 32-bit version of SQL Server running on a 64-bit operating system.

Reference

http://msdn.microsoft.com/en-us/library/bb933993.aspx

http://msdn.microsoft.com/en-us/library/cc645923.aspx