locked
Creating FileStream tables: FILESTREAM group is not available in Filegroups RRS feed

  • Question

  • I am trying to get the FileStream feature working, but currently when I open database properties I see that on the Filegroups page the FILESTREAM group is unavailable.

    It look like this:

    I have used the following procedure to configure FILESTREAM on the server: Enable and Configure FILESTREAM.

    Attempts to create a FILESTREAM table also result in an error:

    USE TMDocs
    GO
    ALTER DATABASE TMDocs
    ADD FILEGROUP TMDocs
    CONTAINS FILESTREAM
    GO
    ALTER DATABASE TMDocs
    ADD FILE
    (
    NAME = N'TMDocs',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.TM\MSSQL\DATA\TMDocs.ndf'
    )
    TO FILEGROUP [TMDocs]
    GO

    And the error is as follows:

    Msg 5591, Level 16, State 3, Line 3
    FILESTREAM feature is disabled.
    Msg 1921, Level 16, State 4, Line 7
    Invalid filegroup 'TMDocs' specified.

    Checking FILESTREAM access level gets that proper settings:

    EXEC sp_configure filestream_access_level;
    GO

    name	minimum	maximum	config_value	run_value
    filestream access level	0	2	2	0

    I am using the following version of SQL:

    Microsoft SQL Server 2016 (RTM-GDR) (KB3210111) - 13.0.1728.2 (X64)   Dec 13 2016 04:40:28   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 15063: ) (Hypervisor) 

    What am I doing wrong?

    I appreciate your help.

    Thank you.


    Well this is the world we live in And these are the hands we're given...



    Well this is the world we live in And these are the hands we're given...

    Monday, April 24, 2017 4:26 PM

Answers

All replies

  • FILESTREAM must also be enabled on OS level. You can do this from the SQL Server Configuration Manager.

    Note that if you have Windows 10 Creators Edition there are some problems which requires that the service account for SQL Server has admin permissions in Windows. This does not apply to server OSs or earlier builds of Win10.

    Monday, April 24, 2017 9:30 PM
  • FILESTREAM must also be enabled on OS level. You can do this from the SQL Server Configuration Manager.

    Note that if you have Windows 10 Creators Edition there are some problems which requires that the service account for SQL Server has admin permissions in Windows. This does not apply to server OSs or earlier builds of Win10.

    Hi Erland,

    By mentioning Configuration Manager, do you mean this (I'm quoting):

    In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM. 
    Right-click the instance, and then click Properties. 
    In the SQL Server Properties dialog box, click the FILESTREAM tab. 
    Select the Enable FILESTREAM for Transact-SQL access check box. 
    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. 
    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.

    If so, I've already done this to no avail. All check boxes are selected on the Filestream tab of the DQL Server's service's properties dialog box

    Any clue?

    Tack för hjälpen på förhand.


    Well this is the world we live in And these are the hands we're given...

    Monday, April 24, 2017 9:55 PM
  • Did you restart SQL Server after enabling filestream?

    What about the OS? What OS do you have?

    Tuesday, April 25, 2017 7:43 AM
  • It's Windows 10 Creators Update: Windows 10 Enterprise 6.3 <X64> (Build 15063:

    Sure, I did restart the SQL Server service multiple times.

    Some articles say FILESTREAM functionality is not supported on 32-bit versions of SQL on 64-bit versions of Windows.

    I have SQL Server folders in %programfiles% and %programfiles(x86)%.

    How do I check this limitation is not the case here?

    Thank you for your interest and help.


    Well this is the world we live in And these are the hands we're given...

    Tuesday, April 25, 2017 8:36 AM
  • I've also checked the SQL Server version by using

    EXEC master.dbo.xp_msver

    and it shows that I am using the 64-bit version:

    Index	Name	Internal_Value	Character_Value
    1	ProductName	NULL	Microsoft SQL Server
    2	ProductVersion	851968	13.0.1728.2
    3	Language	1033	English (United States)
    4	Platform	NULL	NT x64
    5	Comments	NULL	SQL
    6	CompanyName	NULL	Microsoft Corporation
    7	FileDescription	NULL	SQL Server Windows NT - 64 Bit
    8	FileVersion	NULL	2015.0130.1728.02 ((SQL16_RTM_GDR).161213-0356)
    9	InternalName	NULL	SQLSERVR
    10	LegalCopyright	NULL	Microsoft Corp. All rights reserved.
    11	LegalTrademarks	NULL	Microsoft SQL Server is a registered trademark of Microsoft Corporation.
    12	OriginalFilename	NULL	SQLSERVR.EXE
    13	PrivateBuild	NULL	NULL
    14	SpecialBuild	113246210	NULL
    15	WindowsVersion	113246210	6.3 (15063)
    16	ProcessorCount	8	8
    17	ProcessorActiveMask	NULL	              ff
    18	ProcessorType	8664	NULL
    19	PhysicalMemory	8075	8075 (8467439616)
    20	Product ID	NULL	NULL

    This indicates that FILESTREAMS must have been supported by this server. I wonder why they are disabled then...


    Well this is the world we live in And these are the hands we're given...

    Tuesday, April 25, 2017 10:41 AM
  • It's Windows 10 Creators Update: Windows 10 Enterprise 6.3 <X64> (Build 15063:

    As I suspected. The Creators Update breaks FILESTREAM. See this blog post for more details and workaround:
    https://blogs.msdn.microsoft.com/sql_server_team/filestream-issues-with-sql-server-on-windows-10-creators-update/

    • Marked as answer by Exotic Hadron Tuesday, April 25, 2017 11:02 AM
    Tuesday, April 25, 2017 10:51 AM
  • Thanks! This explains why I have been experiencing startup issues with SQL server ever since I've updated to CU. "The last minute security patch".

    I am copying the known workarounds here:

    • Change the SQL Server service startup account to built-in account LocalSystem
    • Change the SQL Server service startup account to a domain user account with local admin privileges on the system
    • If you use virtual account [NT SERVICE\MSSQL$InstanceName] as service startup account, please make this account a member of the local administrators group
    • Uninstall Creators Update and fall back to the previous Windows build
    • Repair SQL Server installation.

    Choose one of the above that fits best to you.

    P.S. Reminds me of the Soviet Union times where party was striving to issue some breakthrough device by some anniversary or some big day like May Day rally just to demonstrate the enormous energy put by the USSR into being first.


    Well this is the world we live in And these are the hands we're given...

    Tuesday, April 25, 2017 11:18 AM