Sql-server – In Search of FILESTREAM Insider Information

database-internalsfilestreamsql serversql-server-2008sql-server-2012

When the FILESTREAM feature is activated on Microsoft SQL Server 2012 then SQL Server will create a "hidden" share on the system. The share is defined as follows:

Sharename          FILESTREAM_SHARE
Path               \\?\GLOBALROOT\Device\RsFx0320\<localmachine>\FILESTREAM_SHARE
Remark             SQL Server FILESTREAM share
Maximum users      unlimited
Users Caching      Manual caching of documents 
Permissions        NT-AUTHORITY\Authenticated Users, FULL

The name is the name of the share you provide when initially configuring FILESTREAM in the SQL Server Configuration Manager. But what is it for?

So far

I read through all the available FILESTREAM documentation starting at:

FILESTREAM (SQL Server)
Enable and Configure FILESTREAM
Create a FILESTREAM-Enabled Database
Create a Table for Storing FILESTREAM Data
… and everything related
FILESTREAM Compatibility with Other SQL Server Features
FILESTREAM DDL, Functions, Stored Procedures, and Views
Access FILESTREAM Data with OpenSqlFilestream
the paper SQL Server 2008 – FILESTREAM Storage in SQL Server 2008
and the article FILESTREAM (OLTP)—a Technical Reference Guide for Designing Mission-Critical OLTP Solutions which references a lot of other material

…but there was no mention of the share and what it does or what it is for. You enter the name and SQL Server will create the share under-the-hood.

FILESTREAM-enabled database

When you create a FILESTREAM-enabled database, the database references a filegroup which references a directory (recommended on a separate drive) that has absolutely nothing to do with the share, which was initially created during FILESTREAM configuration.

Screenshot FILESTREAM-enabled database script

Yes, I do realise that all paths are on C:; it is just an example

The white paper by Paul Randall et al. goes on to explaing that…

FILESTREAM data is stored in the file system in a set of NTFS directories called data containers, which correspond to special filegroups in the database. Transactional access to the FILESTREAM data is controlled by SQL Server and a file system filter driver that is installed as part of enabling FILESTREAM at the Windows level. The use of a file system filter driver also allows remote access to the FILESTREAM data through a UNC path. SQL Server maintains a link of sorts from table rows to the FILESTREAM files associated with them. This means that deleting or renaming any FILESTREAM files directly through the file system will result in database corruption.

… further down the document (page 14) they carry on with …

There is a single FILESTREAM file system filter driver for each NTFS volume that has a FILESTREAM data container, and there is also one for each version of SQL Server that has a FILESTREAM data container on the volume. Each filter driver is responsible for managing all FILESTREAM data containers for that volume, for all instances that use a particular version of SQL Server.

For example, an NTFS volume that is hosting three FILESTREAM data containers, one for each of three SQL Server 2008 instances, will have only one SQL Server 2008 FILESTREAM file system filter driver.

Questions

It's nice to know that SQL Server has everything nice and tied up, but what does that share actually do? Is it the so called "file system filter driver"?
Seeing as any authenticated user can access the "share", what are the security implications?
Is the Device RsFx0320 a predecessor to the resilient file system format that was introduced with Windows Server 2012?

If you can supply answers to my questions, then it would be nice if you could provide a source reference.

Best Answer

When the FILESTREAM feature is activated on Microsoft SQL Server 2012 then SQL Server will create a "hidden" share on the system.

It does not do this by default, you have to CHOOSE to enable the share. This is done via SQL Server Configuration Manager. If you deselect the Enable FILESTREAM for file I/O access the share will be removed.

It's nice to know that SQL Server has everything nice and tied up, but what does that share actually do?

The share allows for clients (local and remote) to have a singular shared location to use the streaming windows api for access to filestream data. This works in conjunction with the SQL Server Instance level settings for filestream access of Full Access Enabled, any other access setting should not work with the streaming API.

... Is it the so called "file system filter driver"?

~~No, it is not. This is just a file share.~~

I was trying not to muddy the waters but you did ask for as much information as possible. In the above strikethrough text I did, in fact, say that this was not the filter driver. However that technically is a half truth. Yes, it is a shared folder but it actually shares through the filter driver. I really debated about this because it starts becoming a rabbit hole that you really can't go down without the source code (and to be honest it's of little value other than academic in my opinion).

The whole point of the filter driver is to do a few things, but the one of those things is to give transactional access to the data stored in the filestream target via a variety of interfaces; SQL Server, Transact SQL, Windows APIs. It also does a handful of other items - however the access given through the share is done via the filter driver. In fact, if you attempt to access files in a filestream and are not an administrator or SQL Server you shouldn't be able to access them.

So, yes this both is and is not the filter driver. It's half a windows fileshare that is exposed through a filter driver. You can see this is you view the path property of the share.

get-wmiobject -class Win32_share | where {$_.Description -like 'SQL Server*'} | ft name, path -autosize

2.Seeing as any authenticated user can access the "share", what are the security implications?

You can change the permissions and requires the settings to be properly set. The security implications are that of any other file share.

3.Is the Device RsFx0320 a predecessor to the resilient file system format that was introduced with Windows Server 2012?

No, this is the name of a specific version of the filter driver. For example, here is a system with the 2016 one loaded RsFx0410. ReFS is a file system, this is a filter driver that sits between the filesystem and the miniport driver. It's actually quite disconcerting that this is a legacy filter driver as denoted by the .10 at the end of the altitude... hmm. You'll also notice it has quite a low altitude, which is generally not acceptable for 3rd party filter drivers.

If you can supply answers to my questions, then it would be nice if you could provide a source reference.

I have no sources for this but have backed up my information through screenshots and configuration options that change settings. Everything in this answer can be found by looking through the product itself and knowing how pieces of windows work (ex: filter drivers).

Related Solutions

Sql-server – Sql FILESTREAM garbage collection issue

It seems like insufficient info to tell something definitely. For example, are you deleting through Transact-SQL or through Win32API, etc.

The FILESTREAM data is not deleted immediately from file system because SQL Server transaction logging under full and bulk recovery models permit the crash recovery.

Have you deleted with CHECKPOINT

delete from tablename CHECKPOINT

or tried to execute CHECKPOINT statement or use simple recovery model?

Also, IMO,:

changing of FILESTREAM (re)configuration require restart of SQL Server
FILESTREAM data is restricted to LOCAL only drives
FILESTREAM operations depend on hardware, see Paul Randal's white paper
"deleting or renaming any FILESTREAM files directly through the file system will result in database corruption"

Check Security and Reliability sections of Paul Randal's white paper

Related discussions containing many subreferences:

Update:
Have you checked permissions of FILESTREAM (Windows) share/container vs. account under which SQL Server runs under. It should have local administrator permissions. It is recommended that no other account be granted permissions on the data container

Update2:
From Paul Randal's white paper:

"The file system access open operations do not wait for any locks. Instead, the open operations fail immediately if they cannot access the data because of transaction isolation. The streaming API calls fail with ERROR_SHARING_VIOLATION if the open operation cannot continue because of isolation violation"
"Antivirus software ... access to the BLOB data in the affected file will be prevented, and to SQL Server the file will appear to have been deleted."
"Note that for a table to have one or more FILESTREAM columns, it must also have a column of the uniqueidentifier data type that has the ROWGUIDCOL attribute. This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint"
"anything that can prevent transaction log truncation may also prevent a FILESTREAM file being physically deleted. Some examples are:"
FILESTREAM data containers cannot be nested

Run
DBCC CHECKDB

Update3:
I cannot coach you online. This is Q&A board. I already had been banned many times for infringing the rules on StackExchange having discussions instead of posting question or answer

Run

~~DBCC CheckDB (QPS8, repair_rebuild)~~

DBCC CheckDB (QPS8) WITH No_INFOMSGS, ALL_ERRORMSGS

and put output here.

To check/set NTFS permissions: right-click on folder in Windows --> Properties --> Security

To check/set MSSQLServer: in command line type

SQLServerManager10.msc

navigate to SQL Server Services and double-click corresponding instance of SQL Server

Update 3b:
Probably, you should drop and recreate index or you have further deeper issues and you should restore database from backup or perform database repair permitting loss of data. This is quite separate topic/question.
I do not want to take any responsibility to guide you on it.

Sql-server – Sql server 2008 filestream maximum number of rows

Actually you can put as many files as you want in a file group.

Basically file group feature included in sql server so that you can archive your organization data properly and can query the data efficiently. Its also depend on the hard disk space on server and configuration that which raid model you use for data storage.