FileStream doesn't seem to get enabled

Question

On one of our Dev DBs I am trying to enable FileStream.

I ran the script below and it is says it went from 0 - Disabled to 2 - Enabled:

Use FileStreamTest
GO
EXEC sp_configure 'filestream access level', 2
GO
RECONFIGURE
GO

But When I run this I get the error FILESTREAM is disabled?:

ALTER DATABASE FileStreamTest 
ADD FILEGROUP FS_FG_20110101
CONTAINS FILESTREAM
GO

Running this Shows a value of 2 (Enabled):

SELECT * FROM sys.configurations
WHERE name = 'filestream access level';

Error Message:

Msg 5591, Level 16, State 3, Line 1
FILESTREAM feature is disabled.

Any ideas what I am doing wrong?

EDIT: Do I have to go into the SQL Server Configuration manager and change the properties on the MSSQLSERVER instance (service) as well?

Answer 1

On SQL Server 2008 R2 it is necessary to restart the SQL server service after the reconfiguration even when you execute RECONFIGURE or RECONFIGURE WITH OVERRIDE.

Proof:

SELECT * FROM sys.configurations WHERE name = 'filestream access level';

returns the value for configuration setting 1580 "filestream access level" as 2 but value_in_use 1.

After a restart of the service, the problem goes away and there is one obstacle less on the winding road of FILESTREAM configuration.

Answer 2

Yes, you do need to enable file stream through SQL Server Configuration Manager. Here is some more information.