次の方法で共有


FILESTREAM Configuration and Setup Changes in SQL Server 2008 February CTP

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.

https://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.

Comments

  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!
  • Anonymous
    March 27, 2008
    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") End If If WScript.Arguments.Named.Exists("Level") Then    TargetLevel = WScript.Arguments.Named("Level") End If If WScript.Arguments.Named.Exists("Share") Then    ShareName = WScript.Arguments.Named("Share") End If 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:&quot; & MachineName & "rootMicrosoftSqlServerComputerManagement10: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    End If    return Err.Number End If    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 Else    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)"    End If    End If    End If    End If    End If    End If    End If    End If    End If End If WScript.Echo vbNewLine & vbNewLine & "New Filestream configuration:"& vbNewLine set fsInstance = GetObject("WINMGMTS:&quot; & MachineName & "rootMicrosoftSqlServerComputerManagement10:FilestreamSettings='" & InstanceName & "'")    WScript.Echo "InstanceName = " & fsInstance.InstanceName    WScript.Echo "AccessLevel  = " & cstr(fsInstance.AccessLevel)    WScript.Echo "ShareName    = " & fsInstance.ShareName    WScript.Echo "RsFxVersion  = " & fsInstance.RsFxVersion

  • Anonymous
    May 20, 2008
    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??

  • Anonymous
    May 20, 2008
    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 missing something here??

  • Anonymous
    March 25, 2009
    Enabling the FILESTREAM datatype on SQL Server 2008

  • Anonymous
    December 06, 2010
    To Enable FILESTREAM using SQL Configuration Manager This works for me too. Thanks.