FILESTREAM is disabled by default in SQL2008. Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine. Enabling/configuring FILESTREAM is a bit different from configuring other SQL features because it involves making machine wide changes and not only SQL instance changes. Also, FILESTREAM configuration has been changed for the February CTP to properly separate the Windows Built-in administrator and SQL administrator roles. In the November CTP, enabling/disabling FILESTREAM required Built-in Admin privilege. Enabling FILESTREAM is now made up of 2 admin layers:
1- The Windows configuration layer handles Windows related setup changes required for FILESTREAM. This requires built in administrator privilege. FILESTREAM settings in the SQL Configuration Manager (SQL-CM) represent this admin layer.
2- The SQL configuration layer handles the setup changes required within the boundaries of the SQL instance. The SQL Admin can now use sp_configure/RECONFIGURE to handle this FILESTREAM configuration.
These 2 layers are by design conceptually independent. If the SQL Admin decides to enable FILESTREAM for a particular SQL Server 2008 instance on the system, sp_configure/reconfigure will succeed regardless of the Windows Admin settings. However, FILESTREAM related operations (like creating a database with FILESTREAM filegroups or accessing data in FILESTREAM columns) will fail if the Windows admin configuration was not set to allow this access. To allow these operations to succeed, proper adjusting of the FILESTREAM settings in SQL Configuration Manager is required. Now, FILESTREAM operations will start succeeding. Similarly, if for example the SQL-CM FILESTREAM settings have enabled FILESTREAM, FILESTREAM related operations will fail if the FILESTREAM access level in sp_configure/reconfigure is set to disabled. To allow these operations to succeed, the user needs to call sp_configure/reconfigure and set the access level to a non disabled value. The FILETREAM operations will now succeed.
FILESTREAM has also been integrated for failover scenarios. However, setting up FILESTREAM instance failover remains not integrated in the failover UI. To allow easy failover instance setup for FILESTREAM scenarios in the future, enabling FILESTREAM has been integrated in SQL Setup UI (standalone non-failover instances only). Currently, calling sp_configure is required after SQL setup is complete. This will not be required in the upcoming Refresh CTP.
Here are step by step details on
- how the SQL Admin should configure FILESTREAM (sp_configure),
- how the Windows built in Admin should configure FILESMTREAM (during setup for standalone instances or in SQL Config Manager), and
- how to configure FILESTREAM for failover clusters.
To Enable FILESTREAM During SQL Server 2008 Installation
To enable FILESTREAM for Transact-SQL access, select “enable FILESTREAM for Transact-SQL access”. This control must be checked before the other control options will be available.
To enable Win32 streaming access, select “enable FILESTREAM for file I/O streaming access”. Use the “Windows share name” control to enter the name of the Windows share in which the FILESTREAM data will be stored. Select the “Allow remote clients to have streaming access to FILESTREAM data” control in case you plan to allow remote clients to access this FILESTREAM data on this server.
To Enable FILESTREAM using SQL Configuration Manager
To enable/configure FILESTREAM settings from the UI:
1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
9. Click Apply.
To do the same using a vb script, see the following blog entry for the script published on SQL engine community to do that.
http://blogs.msdn.com/sqlserverstorageengine/archive/2008/06/09/enabling-filestream-post-sql2008-setup-a-known-issue-in-sql-config-manager.aspx
Also, this blog entry describes a known issue in SQL Config Manager UI when configuring FILESTREAM.
FILESTREAM Configuration Option in Management Studio
Using T-SQL Script:
Use the FILESTREAM configuration option to change the FILESTREAM access level for this instance of SQL Server. For example:
· In SQL Server Management Studio, click New Query to display the Query Editor.
· In Query Editor, enter the following Transact-SQL code:
EXEC sp_configure ‘filestream_access_level’, ‘[level_value]’
RECONFIGURE
· Click Execute.
Here, [level_value] can be:
0 Disables FILESTREAM support for this instance.
1 Enables FILESTREAM for Transact-SQL access.
2 Enables FILESTREAM for Transact-SQL and Win32 streaming access.
Using the UI:
Right click on the SQL instance and select properties, Advanced and change the FILESTREAM access level.
Before this option has any effect, the Windows administration settings for FILESTREAM must be enabled. You enable these settings when you install SQL Server or by using SQL Server Configuration Manager.
To Set Up FILESTREAM on a Failover Cluster
To set up FILESTREAM on a failover cluster, set up the primary node for the failover cluster. After the setup finishes, enable FILESTREAM on the primary node by using SQL Server Configuration Manager. This enables the settings that require Windows Admin privileges. If remote access is required, select Allow remote clients to have streaming access to FILESTREAM data. This will create a file-share cluster resource. Now, set up a passive node.
After the setup finishes, enable FILESTREAM on the passive node by using SQL Server Configuration Manager. The name that you specify for Windows Share Name must be the same across all nodes in the cluster.
1. Add more passive nodes if needed.
2. After all the nodes are added, complete the process by executing the sp_configure stored procedure on each instance of SQL Server.
3. To add and enable additional nodes to the cluster at any time, you can repeat steps above.
PingBack from http://msft.starsports.info/msft/filestream-configuration-and-setup-changes-in-sql-server-2008/
So this begs the questions:
1. If I don't want to do this during setup and a prefer to write to T-SQL scripts, do I really need use the SP_CONFIGURE option (e.g.. will sp_filestream_configure continue to be supported)?
1a. Clarifying q1, which of this lis actually needed to fully configure a filestream share via T-SQL only?
a. EXEC sp_configure 'filestream_access_level', '0'
b. RECONFIGURE
c1. declare @sn sysname = N'MusicShare';
c2. exec sp_filestream_configure @enable_level = 3, @share_name = @sn;
2. If I don't use the configureation tools and sp_filestream_configure is removed, how do I enable the share name?
Thanks!
sp_filestream_configure will be removed from the product. To specify a share name without having to go thru the SQL Configuration Manager UI, you can use the WMI scripting CM support. Here is a sample VBS script on how to do that (note that this requires Windows admin privilege, just like SQL CM, FILESTREAM functionality still requires the the SQL admin to call sp_configure/reconfigure):
'
' USAGE:
' cscript filestream_enable.vbs [/Machine:<MachineName>] [/Instance:<InstanceName>] [/Level:<0|1|2|3>] [/Share:<ShareName>]
On Error Resume Next
Err.Clear()
InstanceName = "MSSQLSERVER"
MachineName = "."
TargetLevel = 3
ShareName = ""
If WScript.Arguments.Named.Exists("Machine") Then
MachineName = WScript.Arguments.Named("Machine")
End If
If WScript.Arguments.Named.Exists("Instance") Then
InstanceName = WScript.Arguments.Named("Instance")
If WScript.Arguments.Named.Exists("Level") Then
TargetLevel = WScript.Arguments.Named("Level")
If WScript.Arguments.Named.Exists("Share") Then
ShareName = WScript.Arguments.Named("Share")
WScript.Echo "Machine: " & MachineName
WScript.Echo "Instance: " & InstanceName
WScript.Echo "Level: " & TargetLevel
WScript.Echo "ShareName: " & ShareName
WScript.Echo vbNewLine & "Current Filestream configuration:"& vbNewLine
set fsInstance = GetObject("WINMGMTS:\\" & MachineName & "\root\Microsoft\SqlServer\ComputerManagement10:FilestreamSettings='" & InstanceName & "'")
If Err.Number <> 0 Then
If Err.Number = -2147217396 Then
WScript.Echo "WBEM_E_NOT_SUPPORTED == IDS_FILESTREAM_NOT_SUPPORTED_ON_INSTANCE (0x8004100C : -2147217396)"
Else
WScript.Echo "Unknown error calling GetObject for FilestreamSettings object "
WScript.Echo "Error: " & Err.Number & " Description: " & Err.Description
return Err.Number
WScript.Echo "InstanceName = " & fsInstance.InstanceName
WScript.Echo "AccessLevel = " & cstr(fsInstance.AccessLevel)
WScript.Echo "ShareName = " & fsInstance.ShareName
WScript.Echo "RsFxVersion = " & fsInstance.RsFxVersion
WScript.Echo vbNewLine & "Calling method EnableFilestream with new level" & vbNewLine
Set method = fsInstance.Methods_("EnableFilestream")
Set inParam = method.inParameters.SpawnInstance_()
inParam.AccessLevel = TargetLevel
inParam.ShareName = ShareName
Set outParam = fsInstance.ExecMethod_("EnableFilestream", inParam)
If outParam.returnValue = 0 Then
WScript.Echo "Method executed successfully" & vbNewLine
WScript.Echo "Method failed: hr = " & cstr(outParam.returnValue)
' WBEM_E_NOT_SUPPORTED == IDS_FILESTREAM_NOT_SUPPORTED_ON_INSTANCE (0x8004100C : -2147217396) (Thrown at GetObject time)
' WBEM_E_INVALID_OPERATION == IDS_FILESTREAM_CANNOT_CHANGE_SHARE (0x80041016 : -2147217386)
' HRESULT_FROM_WIN32 (ERROR_ALREADY_EXISTS) == IDS_FILESTREAM_DUP_SHARE_NAME (0x800700B7 : -2147024713)
' HRESULT_FROM_WIN32 (ERROR_ACCESS_DENIED) == IDS_FILESTREAM_ACCESS_DENIED (0x80070005 : -2147024891)
' HRESULT_FROM_WIN32 (ERROR_INVALID_SHARENAME) == IDS_FILESTREAM_INVALID_SHARENAME (0x800704BF : -2147023681)
' HRESULT_FROM_WIN32 (ERROR_FILENAME_EXCED_RANGE) == IDS_FILESTREAM_SHARENAME_TOO_LONG (0x800700CE : -2147024690)
' HRESULT_FROM_WIN32 (ERROR_RESOURCE_NOT_FOUND) == IDS_FILESTREAM_PRIMARY_NODE_NOT_ENABLED (0x8007138F : -2147019889)
' HRESULT_FROM_WIN32 (ERROR_CLUSTER_INVALID_REQUEST) == IDS_FILESTREAM_SHARENAME_NODE_MISMATCH (0x800713B8 : -2147019848)
' HRESULT_FROM_WIN32 (ERROR_SUCCESS_RESTART_REQUIRED) == IDS_FILESTREAM_DISABLED_RESTART (0x80070bc3 : -2147021885)
' WBEM_E_INVALID_PARAMETER == IDS_FILESTREAM_GENERAL_ERROR (0x80041008 : -2147217400)
If outParam.returnValue = -2147217386 Then
WScript.Echo "WBEM_E_INVALID_OPERATION == IDS_FILESTREAM_CANNOT_CHANGE_SHARE (0x80041016 : -2147217386)"
Else If outParam.returnValue = -2147024713 Then
WScript.Echo "HRESULT_FROM_WIN32 (ERROR_ALREADY_EXISTS) == IDS_FILESTREAM_DUP_SHARE_NAME (0x800700B7 : -2147024713)"
Else If outParam.returnValue = -2147024891 Then
WScript.Echo "HRESULT_FROM_WIN32 (ERROR_ACCESS_DENIED) == IDS_FILESTREAM_ACCESS_DENIED (0x80070005 : -2147024891)"
Else If outParam.returnValue = -2147023681 Then
WScript.Echo "HRESULT_FROM_WIN32 (ERROR_INVALID_SHARENAME) == IDS_FILESTREAM_INVALID_SHARENAME (0x800704BF : -2147023681)"
Else If outParam.returnValue = -2147024690 Then
WScript.Echo "HRESULT_FROM_WIN32 (ERROR_FILENAME_EXCED_RANGE) == IDS_FILESTREAM_SHARENAME_TOO_LONG (0x800700CE : -2147024690)"
Else If outParam.returnValue = -2147019889 Then
WScript.Echo " HRESULT_FROM_WIN32 (ERROR_RESOURCE_NOT_FOUND) == IDS_FILESTREAM_PRIMARY_NODE_NOT_ENABLED (0x8007138F : -2147019889)"
Else If outParam.returnValue = -2147019848 Then
WScript.Echo "HRESULT_FROM_WIN32 (ERROR_CLUSTER_INVALID_REQUEST) == IDS_FILESTREAM_SHARENAME_NODE_MISMATCH (0x800713B8 : -2147019848)"
Else If outParam.returnValue = -2147021885 Then
WScript.Echo "HRESULT_FROM_WIN32 (ERROR_SUCCESS_RESTART_REQUIRED) == IDS_FILESTREAM_DISABLED_RESTART (0x80070bc3 : -2147021885)"
Else If outParam.returnValue = -2147217400 Then
WScript.Echo "WBEM_E_INVALID_PARAMETER == IDS_FILESTREAM_GENERAL_ERROR (0x80041008 : -2147217400)"
WScript.Echo vbNewLine & vbNewLine & "New Filestream configuration:"& vbNewLine
I tried enabling Filestream during installation but post installation it appears diabled. Even on retrying after installation (using Sql Configuration Manager) does not work.
If I try to do it from sqlcmd I get the following error :
The current user does not have permissions to configure the FILESTREAM feature. To configure the FILESTREAM feature, Windows Administrator and sysadmin rights are required.
But I (this user) am a Windows Administrator.
Am I miising something here??
Am I missing something here??
Enabling the FILESTREAM datatype on SQL Server 2008
This works for me too.
Thanks.