Different ways to enable FILESTREAM feature of SQL Server 2008


By:   |   Updated: 2009-09-16   |   Comments (8)   |   Related: More > 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

 

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.

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

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.

Next Steps:



Last Updated: 2009-09-16


get scripts

next tip button



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.

View all my tips
Related Resources





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 03, 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 03, 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 01, 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!


download


Recommended Reading

Stored procedure to import files into a SQL Server FILESTREAM enabled table

How to Remove Filestream from a SQL Server Database

FileTable Feature in SQL Server 2012

How to Backup and Restore a SQL Server FILESTREAM Enabled Database

How to Detach and Attach a SQL Server FILESTREAM Enabled Database





get free sql tips
agree to terms


Learn more about SQL Server tools