SQL Server myth 30th talk about the 18th day of FileStream storage, garbage collection, and other _mssql

Misunderstanding #18: The following several misunderstandings about FileStream

All errors

A) FileStream data can be stored remotely

No, because the FileStream data container (which refers to the NTFS folder in which the FileStream file is stored) must conform to the local storage policy as a data file or log file-that is, the data container must be placed on the running SQL The server's Windows Server is local storage (translator: That is, the storage that can be seen in the ' computer ', the DAC of course is, in fact, the SAN is not directly connected to the server is also considered) access to FILESTREAM data as long as the client is connected to the SQL Server and gets the transaction context of the response, it can be accessed through a UNC path.

b) FileStream data containers can be nested

No, two different FileStream containers for the same database may be in the same directory, but they cannot be nested. FileStream containers for different databases cannot be in the same directory. One of my posts has a piece of code to illustrate this point: misconceptions around FILESTREAM storage.

c) can be partially updated for FileStream updates

Any FILESTREAM update will result in the creation of a completely new FileStream file, which will be recorded in the log. This is why FileStream cannot be used for database mirroring. So much data, if used for mirroring, would have been unthinkable, only that future versions of SQL Server could modify this mechanism to allow partial updates.

D FileStream will be garbage collected as soon as it is not needed

Error. FileStream data is garbage collected when it is no longer needed and to the next checkpoint. This is not so direct that many people are mistaken about the FileStream recycling mechanism.

f) FileStream stored directories and filenames are randomly obtained

In fact, FileStream's filename actually represents the creation of its operation corresponding to the LSN number. The table and column GUID directory names can be obtained in the system tables.

I have a more detailed explanation of the following two posts:

• FILESTREAM directory structure explains how to learn its name from a FILESTREAM row

• FILESTREAM directory Structure-where Does the GUIDs come from? Can words too literally know the content of this article:-)