SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS

You can’t just create FILESTREAM data – you have to specifically enable it at the instance level and then possibly restart the instance or host computer. The reason for this is that there’s a file-system filter driver (called RsFx) installed to aid access to the data through the NTFS Streaming APIs. The rules for this are:

• The computer needs to be restarted if this is the first time FILESTREAM has been enabled on any instance hosted on the machine
  
• The instance needs to be restarted if you disable FILESTREAM after its been enabled

In the previous article I mentioned that FILESTREAM can’t be enabled on a mirrored database – there’s another restriction I forgot: FILESTREAM isn’t supported for instances running on WOW64. I don’t remember the architectural limitation that prevents this but I do remember the heated discussions in the dev team back in early 2007.

Anyway, I digress. You can enable FILESTREAM through T-SQL using the sp_filestream_configure stored procedure. It takes two parameters @enable_level and @share_name. The level of support is configured through the first and has the following options:

• 0 – FILESTREAM is disabled for the instance
  
• 1 – FILESTREAM is enabled for T-SQL access only
  
• 2 – FILESTREAM is enabled for T-SQL AND local file-system access
  
• 3 – FILESTREAM is enabled for T-SQL, local file-system, AND remote file-system access

If level 3 is specified, then the a share name for remote access must also be specified. For example:

EXEC sp_filestream_configure @enable_level = 3, @share_name = ‘MyFilestreamSQLServerInstance’;
GO

Note that once the share name is specified, it can’t be changed without disabling and re-enabling FILESTREAM on the instance.

Now, if you don’t want to have to remember the options you can use SSMS to do the enabling (with no change to the restart requirements listed above). To do this you right-click on the server name in the Object Explorer and select Properties. When the Server Properties window comes up, select the Advanced tab and right at the top is the FILESTREAM section. Here’s what I mean:

Next up is creating a FILESTREAM filegroup and adding data.