Home > General Administration > Enabling FILESTREAM

Enabling FILESTREAM

When it comes to enabling FILESTREAM feature on a new instance of SQL Server 2008 (FILESTREAMs is new SS2K8 feature), you might come across this commands, which are supposed to do the job:

EXEC sp_configure filestream_access_level, 1

RECONFIGURE

or

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

but they don’t – FILESTREAM remains disabled. Some posts claim it happens in Express Edition only, – nope. Enterprise Edition as well.

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

 
 
There is “the GUI way” too, with SSMS:

 

But neither of this will work

 SELECT SERVERPROPERTY ('FilestreamConfiguredLevel'),SERVERPROPERTY ('FilestreamEffectiveLevel');

 will still return zeroes.

What needs to be done is this:

Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

then right click on SQL Server service -> Properties

Go to FILESTREAM tab and check “Enable FILESTREAM for Transact-SQL access” and “Enable FILESTREAM for I/O streaming access”

Then restart the service

This will do it. Now if you run the check again,

 
SELECT SERVERPROPERTY ('FilestreamConfiguredLevel'),SERVERPROPERTY ('FilestreamEffectiveLevel');

it shows that FILESTREAM has been enabled.

 
After this, FILESTREAM filegroup can be created and datafile assigned to it:
 
ALTER DATABASE AdventureWorks2008R2
ADD FILEGROUP FILESTREAM_FILEGRP
CONTAINS FILESTREAM
GO

–Add a file for storing database photos to FILEGROUP
ALTER DATABASE AdventureWorks2008R2
ADD FILE
(
    
NAME= ‘FILESTREAM_01’,
    
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\filestream_01.ndf’
)
TO FILEGROUP FILESTREAM_FILEGRP
GO

 
 
Thanks for the final hint to Stuart’s Tech Punch

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: