Настоящий пост является продолжением темы "Введение в FILESTREAM".

Конфигурирование FILESTREAM

Как правило, конфигурение filestream производится во время сетапа. Обрисованный здесь процесс предназначен для ситуации, если с момента установки ситуация изменилась.

Имеются два вентиля. Один на уровне Windows, другой на уровне SQL Server. Вентиль на уровне Windows более главный. Если filestream на нем запрещен, SQL Server ничего поделать не сможет:

create database TestFS1 on 
primary (name = TestFS_data, filename = 'c:\Temp\TestFS_data1.mdf'),
 
filegroup FG1 contains filestream 
 (name = TestFS_media, filename = 'c:\Temp\TestFS1_media')
 
log on (name = TestFS_log, filename = 'c:\Temp\TestFS1_log.ldf')
 
---
 
Msg 5591, Level 16, State 1, Line 1
 
FILESTREAM feature is disabled.

несмотря на то, что лично у него все будет разрешено:

exec sp_configure 'filestream'
 
---
 
name                     minimum  maximum  config_value run_value
 
------------------------ -------- -------- ------------ ---------
 
filestream access level  0        2        2            0

Скрипт 1

Конфигурирование на уровне Windows выполняется из SQL Server Configuration Manager

рис. 1

Дабл-кликаем на сервис SQL Server и переходим на закладку Filestream.

рис. 2

Ситуация Скрипт 1 соответствует отключенной галке Enable FILESTREAM for Transact-SQL access. Если ее выключить среди бела дня, то после рестарта SQL Serverного сервиса, который требует изменение этой галки, все базы данных с файлстримовскими группами перестанут читаться. В смысле станут не закоррупчеными, упаси боже, а просто доступа к ним не будет. Про вторую галку в BOL написано (https://msdn.microsoft.com/en-us/library/cc645923.aspx): If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Отсюда было бы логично ожидать, что если ее выключить, безобразия с правкой или удалением файла без ведома SQL Server не пройдут. Увы мне, Иван Василичу. Можете сами убедиться. Эти установочки живут у нас с вами в реестре по адресу где-нибудь HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\Filestream, DWORD-значение EnableLevel. Как нетрудно догадаться, значение 2 соответствует обеим включенным галкам, 1 – первой, 0 – никакой. Можно менять прямо там руками, VBScriptoм, PowerShellом, как угодно. Единственно, SQL Server подчитывает значение этого ключа только во время своего запуска.

В свою очередь на стороне SQL Server стоит еще один кран под названием 'filestream', который поворачивается в комнате под названием SQL Server Management Studio, клик правой кнопкой по серверу в Object Explorere, выбрать Properties -> Advanced. Там будет в самой первой строке.

рис. 3

Тем же краном можно управляться из скрипта

exec sp_configure 'filestream', 1
 
reconfigure with override

Скрипт 2

Значения 0, 1, 2 соответствуют тому же, что и в реджистри. Можно ставить какую угодно цифру, но по факту все равно будет не более той, что разрешена на уровне Windows. Скажем, если в реджистри прописано 1, а вы в sp_configure поставили 2, у нее будет config_value = 2, а фактическая run_value все равно 1. Так несправедливо устроена жизнь. Зачем понадобилось городить два крана – видимо, для того, чтобы DBA не мог сконфигурить файлстрим без админа Windows. Тот должен быть в курсе и сказать свое одобрямс. Они оба должны приложить свои отпечатки или радужки, тогда дверь откроется. В СТР5 SQLный админ был самодостаточен, а в феврале прошлого года поведение изменилось. Хорошо еще, что пока БД вроде можно создавать без служебной записки виндузовому админу.

Для просмотра текущего состояния второго вентиля можно использовать sp_configure или функцию serverproperty

select 
 serverproperty ('FilestreamShareName') 
,serverproperty ('FilestreamConfiguredLevel') 
,serverproperty ('FilestreamEffectiveLevel')

FILESTREAM и старые версии файлов.

При работе с файлстримом свербящей проблемой, которая беспокоит человечество практически так же остро, как Гондурас, является борьба с глюками. Под глюками будем понимать файлы, соответствующие старым версиям значений. Как вы помните, в таблице Media было три записи, блобовским полям которых соответствовало три файла в файловой системе. Сделаем:

update Media set stream = cast(N'ффф' as varbinary(max)) where id = 1

Во, их стало 4:

рис. 4

В первом хранится новое значение, во втором – предыдущее. Оно никуда не девается, а становится глюком и продолжает жить рядом. Сделайте ради хохмы еще один апдейт. Видите, их уже пять. Удалите нафиг эту запись: delete Media where id = 1. Стало меньше файлов? Правильно, по-прежнему пять. Да что удалять! Если оттранкейтить (минимально логгируемая операция) таблицу Media, старая папка, соответствующая таблице, никуда не денется. Она останется вместе со своей подпапкой, соответствующей колонке filestream, и файлами, содержащими значения полей этой колонки для каждой записи. Просто рядом с ней будет создана еще одна табличная папка. Новая вставка создаст новые файлы в новой папке, но старые никуда не денутся. Что творит, зараза! Теперь представьте, что вместо этих ффф и ххх там лежат киношки гиг по многу. Это уже не ффф. Дисковое пространство, конечно, нынче стоит копейки, но такими темпами его все равно не напасешься. Возникают три извечных вопроса русской интеллигенции: что делать, кто виноват и какой счет? В BOL они отражены недостаточно, в связи с чем возникают кривотолки. Один товарисч утверждал, что всему виной открытые транзакции, которые держат эту запись. Я позакрывал к бисовой матери все открытые транзакции, до каких мог дотянуться, и убедился, что никто из sys.dm_tran_active_transactions эту запись не держит. Глюки между тем не исчезли. Можно последовательно проверить, что ее не читает log reader, log backup, log shipping и т.д. (я знаю еще много умных слов) - транзакция может только продлить время существования глюка, но глюк не удаляется автоматически по завершении транзакции. Можно пристреливать их самому в зависимости от обстоятельств и настроения, но для этого надо знать, какой файл какому блобу соответствует. Это отдельный вопрос, который обсудим после. Кроме того, надо вести историю соответствия файла блобу, и чуть оно изменилась, старый файл сразу долой. В принципе, можно, но напряжно. Как-то через одно место. Это не есть истинное дао. Глюки подчищает процесс, аналогичный GC (Garbage Collector) в CLR, который происходит по чекпойнту. Файлы предыдущих версий (они же before-images), которые не держит никакая транзакция и которые больше нафиг никому не сдались, становятся кандидатами в покойники и помещаются в системную таблицу sys.filestream_tombstone_ля-ля-ля. Сделайте

select * from TestFS.sys.all_objects where name like '%tomb%'
 
---

Вот она, красавица, в первой строчке. Перейдите в выделенное административное соединение (DAC), потому что она INTERNAL_TABLE и просто так в нее хрен залезешь. Из DACа скажите

select * from TestFS.sys.filestream_tombstone_2073058421
 
---

Скрипт 3

У, красота какая. Ничего не понятно. Хотя почему? Очевидно, что rowset_guid – это имя файловой папки, соответствующей таблице, column_guid – папки, соответствующей файлстримовскому полю, а filestream_value_name – имя файла, который глюк. Рискну предположить навскидку, что oplsn_fseqno – это номер виртуального лога, oplsn_bOffset – смещение LSN транзакции, превратившей файл в глюк, относительно его начала, а oplsn_slotid – код операции. Например, 2 – update, 4 – delete. Чекпойнт пробегается по таблице и прибирает перечисленные в ней глюки. Наверное, всякий раз по высвобождению файла отвлекаться на его удаление выходит чересчур накладно, вот они и сделали на манер сборщика мусора. Скорее всего, решение было обусловлено теми же причинами, по которым на диск не скидываются по отдельности грязные записи после каждого изменения. По соображениям производительности проще накопить их до кучи и записать батчем, чем отвлекаться на каждую отдельную запись. Обратной стороной медали является повышенная трата дискового пространства. Инициируйте принудительный вызов чекпойнта:

checkpoint

Только выполняйте его, пожалуйста, не с DACовского соединения J. Сами при этом идете в C:\Temp\TestFS_media\65500280-cc4b-4fc9-8993-0cd7bf060589\7fbde366-b369-4939-873d-62d3547dcdfb и наблюдаете, как исчезают файлы 00000016-0000007b-0003, 00000016-00000095-0005 и 00000016-00000097-0005. Вернитесь в DACовское соединение и убедитесь (Скрипт 3), что таблица TestFS.sys.filestream_tombstone_2073058421 опустела. Отдельные товарищи утверждают, что перед этим нужно еще делать бэкап лога или переводить базу в recovery model = simple. В моем случае

select databasepropertyex('TestFS', 'recovery')
 
----
 
FULL

Делать backup log TestFS to disk = 'c:\Temp\TestFS_log.bak' не хотелось, а backup log with truncate_only они в Катмае убрали, гады. Поэтому никакого бэкапа я не делал, но вы видели, что тем не менее все глюки исправно появились в таблице кандидатов в покойники, т.е. SQL Server они были не нужны. Чекпойнт сработал, глюки ушли. Аналогично сборка глюков происходит при автоматическом наступлении чекпойнта. К сожалению, в профайлере отображается только ручной вызов чекпойнта, чтобы инициировать автоматический, обратимся к документации. Автоматическое наступление чекпойнта происходит в ситуациях (https://msdn.microsoft.com/ru-ru/library/ms188748.aspx):

События, которые вызывают появление контрольных точек

Перед резервным копированием базы данных компонент Database Engine автоматически выполняет контрольную точку, так что все изменения страниц базы данных содержатся в резервной копии. Кроме того, контрольные точки автоматически возникают, когда выполняются какие-либо из следующих условий.

• Активная часть журнала превышает объем, который сервер может восстановить за время, указанное в параметре конфигурации сервера recovery interval.
• Журнал заполнен на 70 процентов и база данных находится в режиме усечения журнала.

База данных находится в режиме усечения журнала, если для обоих из этих условий установлено TRUE. База данных использует простую модель восстановления и после выполнения последней инструкции BACKUP DATABASE, ссылающейся на базу данных, возникает одно из следующих событий:

• в базе данных выполняется операция с минимальной регистрацией, например выполняется операция массового копирования с минимальной регистрацией или инструкция WRITETEXT с минимальной регистрацией;
• выполняется инструкция ALTER DATABASE, добавляющая или удаляющая файл в базе данных;

Кроме того, остановка сервера вызывает контрольную точку в каждой базе данных сервера.

Наплодите предварительно глюков операциями update/delete над записями с файлстримовскими полями. Изобразите какую-нибудь активность, которая быстро наполняет журнал транзакций и инициирует автоматическое возникновение чекпойнта. Например,

if object_id('t', 'U') is not null drop table t
 
create table t(id int default 1)
 
set nocount on
 
while 1 = 1 begin
 
 insert t values (default)
 
 delete t
 
end

Только не используйте вместо t временные таблицы и табличные переменные J. Тем временем ступайте в папку C:\Temp\TestFS_media\65500280-cc4b-4fc9-8993-0cd7bf060589\7fbde366-b369-4939-873d-62d3547dcdfb и наблюдайте за глюками. Секунд через 10 журнал наполнится настолько, что вызовется чекпойнт и глюки исчезнут. Это и есть по всей видимости штатный режим очистки предыдущих версий, предполагавшийся разработчиками. Хотя... сами по себе операции над файлстримами, как известно, журналируются минимально, и если рядом не происходит никакой другой активности, чекпойнт, вероятно, все-таки придется вызывать принудительно.

Автор: Алексей Шуленин