FILESTREAM Configuration and Setup Changes in SQL Server 2008 February CTP

FILESTREAM Configuration and Setup Changes in SQL Server 2008 February CTP

  • Comments 7

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.

 

 

Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • 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")

    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:\\" & 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

       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:\\" & MachineName & "\root\Microsoft\SqlServer\ComputerManagement10:FilestreamSettings='" & InstanceName & "'")

       WScript.Echo "InstanceName = " & fsInstance.InstanceName

       WScript.Echo "AccessLevel  = " & cstr(fsInstance.AccessLevel)

       WScript.Echo "ShareName    = " & fsInstance.ShareName

       WScript.Echo "RsFxVersion  = " & fsInstance.RsFxVersion

  • 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??

  • 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??

  • Enabling the FILESTREAM datatype on SQL Server 2008

  • To Enable FILESTREAM using SQL Configuration Manager

    This works for me too.

    Thanks.

Page 1 of 1 (7 items)