Внутренние механизмы и скрытые элементы FILESTREAM

Мы продолжаем публикацию серии статей о SQL Server FILESTREAM.

В предыдущей статье мы рассказали о связи FILESTREAM с операционной системой. Далее мы продолжим разговор о скрытых (внутренних) механизмах работы FILESTREAM.

Создадим тестовую базу.

USE master;
GO
CREATE DATABASE FileStreamTest
ON PRIMARY (Name=FileStreamTest_data, FILENAME = 'C:\FileStream\FileStreamTest.mdf'),
FILEGROUP FSGroup CONTAINS FILESTREAM(Name=FSData, FILENAME = 'C:\FileStream\filestream1')
LOG ON (Name=FileStreamTest_log, FILENAME = 'C:\FileStream\FileStream.ldf');
GO

После создания базы в папке "C:\FileStream" появилась папка "filestream1", а в ней папка "$FSLOG" и файл "filestream.hdr".

Папка "$FSLOG" будет в дальнейшем содержать файлы, отслеживающие изменения производимые с файлами BLOB, подобно журналу транзакций SQL Server. Файл "filestream.hdr" содержит внутреннюю информацию для SQL Server. Он связывает BLOB-хранилище c реляционными таблицами базы данных. Этот файл не доступен для чтения, поскольку заблокирован SQL Server-ом.

Папка "filestream1" не может содержать данные второго FILESTREAM-хранилища,

Увидеть какая папка содержит какие данные можно с помощью запросов, приведенных ниже.

use FileStreamTest;
GO
select *
from sys.database_files df
join sys.data_spaces ds
on df.data_space_id = ds.data_space_id;
GO
SELECT *
FROM sys.filegroups fg
join sys.data_spaces ds
on fg.data_space_id = ds.data_space_id

Создаем таблицу для размещения filestream data

use FileStreamTest;
GO
CREATE TABLE dbo.FS_Records
(DocGUID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID(),
DocId int NOT NULL UNIQUE IDENTITY (1,1),
Document varbinary(max) FILESTREAM NULL);
GO

В таблице обязательно должен присутствовать столбец созданный подобным образом "UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID()". Это необходимо для идентификации каждой отдельной строки. Можно не использовать значение DEFAUL типа "NEWID()", но тогда вам придется присваивать новое значение каждый раз при вставке строки. Вместо этой функции можно использовать другую, генерирующую GUID.

Вставим пустую строку

use FileStreamTest;
GO
INSERT INTO dbo.FS_Records (Document) VALUES (NULL);
GO

Посмотрим в \\localhost\SQL2014_FileStream там пусто, это нормально, там всегда будет пусто, поскольку это точка перенаправления для клиентов работающих через Win32 API, но появились папки в "C:\FileStream\filestream1, при этом папки с GUID в качестве имени - пустые, поскольку мы вставили пустую строку (пустой BLOB), а все остальные данные и определения строки хранятся в mdf (FileStreamTest_data).
Теперь вставим строку содержащую данные.

INSERT INTO dbo.FS_Records (Document) VALUES (CAST('Yes, it''''s work fine' as varbinary(max)));
GO

Посмотрим в "\\localhost\SQL2014_FileStream" там (как и ранее) пусто, при этом одна из папок с GUID в качестве имени содержит файл. В моем случае это файл с именем 0000002a-00000150-0006. Откроем его с помощью Notepad.exe (в реальной жизни прямой доступ (через файловую систему) с FILESTREAM-файлами настоятельно не рекомендуется) и внутри вы увидите "Yes, it''s work fine", все же остальные данные этой строки храняться в mdf-файле (FileStreamTest_data).

Что представляет собой имя файла?
Для ответа на это вопрос давайте выполним запрос к недокументированной функции и укажем ей в качестве условия имя файла 0000002a-00000150-0006 слегка модифицировав его в формат 0000002a:00000150:0006.

SELECT [Current LSN], Operation, [Transaction ID]
FROM sys.fn_dblog(null,null)
WHERE [Current LSN] = '0000002a:00000150:0006'

Как мы видим этому имени соответствует операция (LOP_FS_DOWNLEVEL_OP) в журнале транзакций, т.е. в журнал записана операция создания файла и его имя соответствует LSN этой операции.

Выполним запрос к созданной таблице.

SELECT DocGUID, cast (Document as char(100) ) as [Document text], Document.PathName(0) as [Path name]
FROM dbo.FS_Records

В запросе есть столбец "Path name" который получен встроенной функцией PathName(0). Этот путь необходим для доступа к FILESTREAM-файлу через специальный Win32 API, для доступа через Transact-SQL он не нужен. 

Обратите внимание на то, что часть пути (помечено красным) соответствует DocGUID. Как было показано в предыдущей статье, при выполнении команды NET SHARE мы получим строку "SQL2014_FileStream\\?\GLOBALROOT\Device\RsFx0300\<localmachine>\SQL2014_FileStream", где присутствует имя общего ресурса "SQL2014_FileStream", а также имя минифильтра RsFx0300. Таким образом, когда клиенты Win32 API обращаются по пути полученному функцией PathName() их запрос через минифильтр перенаправляется на SQL Server и далее в файловую систему.

Как это работает?

• Запрос приходит на SQL Server.
• По значению DocGUID извлекается нужная физическая страница.
• Из страницы извлекается реальное имя файла (соответствующее LSN транзакции его создавшей) 

Для получения номера страницы и строки мы воспользуемся недокументированной функцией описанной в https://www.sqlskills.com/blogs/paul/sql-server-2008-new-undocumented-physical-row-locator-function.

use [FileStreamTest];
GO
SELECT DocGUID, cast (Document as char(100) ) as [Document text], sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID]
FROM dbo.FS_Records
WHERE DocGUID = 'B16BC7B7-D337-4D52-A2CC-C4695C629362';

Как видно из результатов запроса искомая строка лежит в файле №1, странице 300 и номер строки равен 1.

Выполним запрос на получение данных хранящихся в странице, используя недокументированную функцию DBCC PAGE (); Найдем нужную нам строку (slot) по DocGUID (обозначено красным).

DBCC TRACEON (3604);
DBCC PAGE ([FileStreamTest], 1,300,3);

Получим

Slot 1 Column 1 Offset 0x4 Length 16 Length (physical) 16
DocGUID = b16bc7b7-d337-4d52-a2cc-c4695c629362

Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4
DocId = 2

Document = [Filestream column] Slot 1 Column 3 Offset 0x1f Length 48
ColType = 3 Version = 1 FileId = 65537
UpdateSeq = 1 CreateLSN = 0000002a:00000150:0006 (42:336:6)
TxFMiniVer = 0

Как видим в поле CreateLSN находится значение 0000002a:00000150:0006. Если в этой строке заменить двоеточие на дефисы, то получим 0000002a-00000150-0006, что и будет соответствовать имени файла размещенного в папке "C:\Filestream\filestream1\04e6a56a-9457-4dd3-8b1f-ac0f49a49b0a\38e656d7-2cfb-43c8-ae98-f489b57f6d7d".

Откуда пришло значение "C:\Filestream\filestream1\" мы знаем, поскольку мы сами его указали при создании базы, но откуда берутся еще два имени папок ("04e6a56a-9457-4dd3-8b1f-ac0f49a49b0a\38e656d7-2cfb-43c8-ae98-f489b57f6d7d")? Каким образом клиент может получить информацию по этим папкам, чтобы загрузить файл из этих папок?

Для ответа на этот вопрос нам необходимо обратиться к внутренним, системным таблицам SQL Server, доступ к которым возможен только с использованием Dedicated Administrative Connection (DAC).

Выполним запрос к базе данных, подключившись к ней через DAC.

use FileStreamTest;
go
SELECT o.name AS [Table], cp.name AS [Column], r.rsguid AS [Rowset GUID], rs.colguid AS [Column GUID]
FROM sys.sysrowsets r
CROSS APPLY sys.sysrscols rs
JOIN sys.partitions p ON rs.rsid = p.partition_id
JOIN sys.objects o ON o.object_id = p.object_id
JOIN sys.syscolpars cp ON cp.colid = rs.rscolid
WHERE rs.colguid IS NOT NULL AND o.object_id = cp.id AND r.rsguid IS NOT NULL AND r.rowsetid = rs.rsid AND o.name = 'FS_Records' and cp.name = 'Document';

Теперь, если произвести произвести побайтовую перестановку "Rowset GUID" и "Column GUID" (См. https://ru.wikipedia.org/wiki/%D0%9F%D0%BE%D1%80%D1%8F%D0%B4%D0%BE%D0%BA_%D0%B1%D0%B0%D0%B9%D1%82%D0%BE%D0%B2), то мы получим имена папок файловой системы.

Итог.

В данной статье мы разобрались с внутренними механизмами управления FILESTREAM, включая недокументированные и скрытые от глаз пользователей элементы системы. Мы надеемся, что данный материал позволит вам полнее понять работу с FILESTREAM.
В следующих статьях мы рассмотрим доступ к данным FILESTREAM с использованием языка Transact-SQL и C#.

Александр Каленик, Senior Premier Field Engineer (PFE), MSFT (Russia)