Different ways to enable FILESTREAM feature of SQL Server 2008

By:   |   Updated: 2009-09-16   |   Comments (8)   |   Related: > FILESTREAM


Problem

A new feature in SQL Server 2008 is the FILESTREAM feature which allows you to store BLOBs in the NTFS file system instead of in the database.  In order to use this new feature it has to be enabled.  In this tip I go over four different options to enable the FILESTREAM feature.

Solution

In SQL Server 2008 one can store BLOBs (e.g. Images, video, word, excel, PDF, MP3, etc) in the NTFS file system rather than in a database file. This can be done by using the new FILESTREAM feature which was introduced in SQL Server 2008. However, in order to enable the FILESTREAM feature you need to be a member of the SYSADMIN or SERVERADMIN fixed server roles.

There are different ways in which one can enable FILESTREAM feature in SQL Server 2008. Let us go through each option one by one.


Enable FILESTREAM Feature Using SQL Server Configuration Manager

1. Click Start | All Programs | Microsoft SQL Server 2008 | Configuration Tools | SQL Server Configuration Manager.

This will open up SQL Server Configuration Manager as shown in the below snippet

image001 

2. In the left panel of SQL Server Configuration Manger, click on SQL Server Services, this will list all the SQL Server 2008 related services on the right side panel.

3. Click on SQL Server (MSSQLSERVER) service instance in case of a default installation. However, in case of a named instance you need to choose SQL Server (Instance Name) service and right click the same to see the properties for the service.

4. In SQL Server Properties dialog box, you need to click on FILESTREAM tab and Enable FILESTREAM for Transact-SQL access checkbox, this will enable the rest of the options.

5. If you are interested in enabling read and write FILESTREAM data from windows, then click Enable FILESTREAM for file I/O streaming access and specify the name of the windows file share in the Windows Share Name textbox.

6. If you require remote clients to access the FILESTREAM data that is stored in the share, then select Allow remote clients to have streaming access to FILESTREAM data option as shown in the below snippet.

image002

7. Finally click OK to save the changes.


Enable FILESTREAM Feature Using Transact SQL (TSQL)

The FILESTREAM feature of SQL Server 2008 can also be enabled using the Transact SQL (TSQL).

In the SQL Server 2008 Management Studio, open a new query window and type the below TSQL Query to enable FILESTREAM feature.

USE master
Go
EXEC sp_configure 'show advanced options'
GO
EXEC sp_configure filestream_access_level, 1
GO
RECONFIGURE WITH OVERRIDE
GO

There are 3 levels of FILESTREAM access which are supported in SQL Server 2008 and they are mentioned below for your reference.

  • When the value specified is 0 then, FILESTREAM support for the instance is Disabled
  • When the value specified is 1 then, FILESTREAM for Transact-SQL Access is Enabled
  • When the value specified is 2 then, FILESTREAM for Transact-SQL and Windows streaming access is Enabled

Enable FILESTREAM Feature at SQL Server Instance Level using SQL Server 2008 Management Studio (SSMS)

The FILESTREAM feature of SQL Server 2008 can also be enabled at the SQL Server Instance level using SQL Server Management Studio by following the below mentioned steps.

1. Connect to SQL Server 2008 Database Instance using SQL Server 2008 Management Studio

2. In the Object Explorer, right click the database instance and select Properties to view the Server Properties

3. On the left panel click on the Advanced tab, then click on the drop down list next to Filestream Access Level and select Full access enabled option as shown in the below snippet

image003

4. Finally click OK to save the changes.


Enable FILESTREAM Feature during the Installation of SQL Server 2008

The FILESTREAM feature can also be enabled during the installation of SQL Server 2008. A DBA can enable this feature by clicking on the FILESTREAM tab of the Database Engine Configuration screen and can choose all the available options as per his requirement as shown in the snippet below.

image004

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-09-16

Comments For This Article




Friday, September 13, 2019 - 8:10:32 AM - Sujeesh Back To Top (82405)

We have tried by following the step by step process. But I am getting the permission error when I open the folder in the network location.

Please see the error message and advice me.

You do not have permission to access the <folder name>


Thursday, May 3, 2018 - 11:49:24 AM - Jonathan Hodgkinson Back To Top (75857)

 I only appear to have access to the Sql Server Configuration Manager option to enable filestream and when checking the I/O streaming access and remote clients streaming access boxes I receive an error stating 'There was an unknown error applying te FILESTREAM settings. Check the parameters are valid.' Any idea what parameters I need to check and how to overcome this problem? I am running SQL 2008 R2 on a Win 7 machine.

All the best

Jonathan

 


Thursday, March 3, 2016 - 9:46:10 AM - Adrian Back To Top (40852)

Hi,

For my Windows Server 2008 R2 + SQLExpress2008

"Enable FILESTREAM Feature at SQL Server Instance Level using SQL Server 2008 Management Studio (SSMS)" says is enabled but in fact is NOT.

"Enable FILESTREAM Feature Using SQL Server Configuration Manager" says is enabled and works.

 

 

 


Sunday, November 22, 2015 - 4:02:02 AM - Jahabar Sathik Back To Top (39119)

Hi,

  Its realy a wonderfull documents for me and thanks for your wonderfull tips and blogs....

 

Thanks a lot..


Wednesday, July 1, 2015 - 8:30:49 AM - Gravier Jérôme Back To Top (38095)

Hello,

 

I tried to enable FILESTREAM using the query but FILESTREAM is still disabled if I check with SQL Server Management Studio.. Did I miss something ? In this page it said that enabling Filestream is made by using two of the ways you show : https://msdn.microsoft.com/fr-fr/library/cc645923(v=sql.120).aspx

 

Thanks a lot if you reply.

 


Friday, November 14, 2014 - 10:21:07 AM - Andrew Back To Top (35294)

Thanks! Tip was useful today.


Wednesday, January 11, 2012 - 11:39:47 AM - Ashish Kumar Mehta Back To Top (15614)

Dear Dreama,

You are most welcome, I am glad I could help you. Keep visiting MSSQLTips for more learning.

Cheers
Ashish Kumar Mehta
MSSQLTips - Author


Tuesday, January 10, 2012 - 1:05:31 AM - Dreama Back To Top (15581)
Great stuff, you hepled me out so much!














get free sql tips
agree to terms