Misconceptions around FILESTREAM storage

This short post is prompted by a question that came in through Twitter – I *knew* it was worth joining and spending time on it (http://twitter.com/PaulRandal).

The (paraphrased) question is "can FILESTREAM data be stored remotely?". This has been confusing people, and neither FILESTREAM BOL nor my FILESTREAM whitepaper (see here) explicitly answer the question.

The FILESTREAM data container for a database must be placed on an NTFS volume on locally-connected storage. Just like database data and log files, the directory cannot be on a UNC share. The confusion comes from the fact that FILESTREAM data *can* be *accessed* remotely through a UNC share – but as far as the host instance is concerned, the FILESTREAM storage must be local.

A second question that came up a while ago is whether FILESTREAM data containers can share the same directory or be nested. The answers are kind-of, and no, respectively. Let's see.

I'll create the first database with a FILESTREAM data container:

CREATE DATABASE FileStreamTestDB1 ON PRIMARY
    (NAME = FileStreamTestDB1_data, FILENAME = N'C:\SQLskills\FSTestDB1_data.mdf'),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
    (NAME = FileStreamTestDB1Documents, FILENAME = N'C:\SQLskills\FSDC\Documents')
LOG ON
    (NAME = FileStreamTestDB1_log, FILENAME = N'C:\SQLskills\FSTestDB1_log.ldf');
GO
 

And now let's try another database with the same parent directory:

CREATE DATABASE FileStreamTestDB2 ON PRIMARY
    (NAME = FileStreamTestDB2_data, FILENAME = N'C:\SQLskills\FSTestDB2_data.mdf'),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
    (NAME = FileStreamTestDB2Documents, FILENAME = N'C:\SQLskills\FSDC\Documents2')
LOG ON
    (NAME = FileStreamTestDB2_log, FILENAME = N'C:\SQLskills\FSTestDB2_log.ldf');
GO
 

This works fine. You can't have another database use the *same* directory as the first database (i.e. N'C:\SQLskills\FSDC\Documents'), but two FILESTREAM data containers can have the same parent directory.

And now let's try a nested one:

CREATE DATABASE FileStreamTestDB3 ON PRIMARY
    (NAME = FileStreamTestDB3_data, FILENAME = N'C:\SQLskills\FSTestDB3_data.mdf'),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
    (NAME = FileStreamTestDB3Documents, FILENAME = N'C:\SQLskills\FSDC\Documents\Documents3')
LOG ON
    (NAME = FileStreamTestDB3_log, FILENAME = N'C:\SQLskills\FSTestDB3_log.ldf');
GO

Msg 5136, Level 16, State 2, Line 1
The path specified by 'C:\SQLskills\FSDC\Documents\Documents3' cannot be used for a FILESTREAM container since it is contained in another FILESTREAM container.
Msg 1802, Level 16, State 2, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Doesn't work, as expected, as this is documented in BOL here.

Thanks

5 thoughts on “Misconceptions around FILESTREAM storage

  1. Hi Paul,
    Through your article, I found you said “The confusion comes from the fact that FILESTREAM data *can* be *accessed* remotely through a UNC share”. But I tried to use FileStream save/upload the file on remote server via UNC. I always got a error message “The network path can not be found”. I have checked the item “Allow remote clients to have streaming access to FILESTREAM data”, but still not work. Any advices from you. Many thanks!

  2. Hi,
    I am restoring database with following query
    RESTORE DATABASE [DB_TEST]
    FROM DISK = ‘X:BKUPDB_TEST_1.bak’,
    DISK = ‘X:BKUPDB_TEST_2.bak’,
    DISK = ‘X:BKUPDB_TEST_3.bak’,
    DISK = ‘X:BKUPDB_TEST_4.bak’,
    DISK = ‘X:BKUPDB_TEST_5.bak’,
    DISK = ‘X:BKUPDB_TEST_6.bak’
    WITH FILE = 1,
    move N’DB’ to N’W:MdfDB_TEST.mdf’,
    MOVE N’DB_log’ TO N’W:ldfDB_TEST_log_0.ldf’ ,
    MOVE N’DB_TEST_FSData’
    TO N’E:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATADB_TEST_2_newDB_TEST.filestreamdata’,
    NOUNLOAD, REPLACE, Recovery, STATS = 1
    GO
    it show following error
    Msg 3257, Level 16, State 1, Line 2
    There is insufficient free space on disk volume ‘E:’ to create the database. The database requires 1096398101470 additional free bytes, while only 598657335296 bytes are available.
    Msg 3119, Level 16, State 4, Line 2
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    Very simple there is no e: but it is asking for it.
    Plz suggest solution.

  3. basically you can
    1. step share folder with db files and blob folder to remote machine(where sql server is installed)
    2. create a symbolic link to this shared blob folder
    3. and then normal create dabatase … for attach
    (of course you should grant all needed permissions + delegation)

    but it doesn’t work for sql 2016 and above unfortunately
    (it works for sql server 2005-2014)

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.