One of the great features of SQL Server 2008 is Filestream data support. I wrote a blog entry on this option which allows us to store large data objects outside of the database on the file system while benefiting from database transaction processing and synchronized disaster recovery. Configuration of this option has changed since the Nov 2007 CTP which "threw me for a loop" when I started testing the feature again this week with SQL Server 2008 RC0. Filestream data support is disabled by default. So how exactly do you configure it?

As it turns out, Microsoft has decided to separate the configuration into two distinct layers - one for the Windows Administrator and one for the SQL Administrator. This makes sense since the feature spans both sides of the system. Many times both administrators are the same person but many times they are not so it makes sense to keep the configuration separate and secure. The first layer is the Windows configuration layer and must be performed by a member of the BUILTIN\Administrators group either at SQL Server install time (there is a Filestream tab on one of the setup windows - easy to miss!) or after the install using SQL Server Configuration Manager (there is a Filestream tab on the service properties window).  But that is not enough to enable the Filestream data option. Layer two is performed by the SQL Administrator (member of sysadmin role) by using either sp_configure or SSMS. Both layers have to be configured in order for a Database to support Filestream data successfully.

Both layers also have the concept of a "filestream_access_level" which can restrict access to filestream data via Transact-SQL alone or allow access through both Transact-SQL and Win32 streaming (through a named shared folder). When using sp_configure the filestream_access_level can be set to three values:

0 Disables FILESTREAM support.

1 Enables FILESTREAM for Transact-SQL access.

2 Enables FILESTREAM for Transact-SQL and Win32 streaming access

Of course, the two layers have to be consistent otherwise the most restrictive setting will prevail. That's what threw me, as I was setting the SQL Administrator layer without setting the corresponding Windows Administrator layer. The options look similar so it's easy to think that the layers are the same thing. But they are not. Now you know. Consult the new Books Online. That's what saved me. Invented by Sybase; maintained diligently by Microsoft. It has saved me more than once in my career - it's the best help system out there. Use it: all the experts do.

Later

Brian