Поделиться через

FILESTREAM (SQL Server)

  • Статья

Область применения: SQL Server — только Для Windows

FILESTREAM позволяет приложениям на основе SQL Server хранить неструктурированные данные, такие как документы и изображения, в файловой системе. Приложения могут использовать расширенные API потоковой передачи и производительность файловой системы и одновременно поддерживать согласованность транзакций между неструктурированными данными и соответствующими структурированными данными.

FILESTREAM интегрирует SQL Server ядро СУБД с файловыми системами NTFS или ReFS, сохраняя данные двоичного большого двоичного объекта (MAX) в виде файлов в файловой системе. Инструкции Transact-SQL могут вставлять, обновлять, запрашивать, искать данные FILESTREAM и создавать их резервные копии. Интерфейсы файловой системы Win32 предоставляют потоковый доступ к этим данным.

Для кэширования данных файлов в хранилище FILESTREAM используется системный кэш NT. Кэширование файлов в системном кэше помогает снизить влияние на производительность fileSTREAM ядро СУБД. Пул буферов SQL Server не используется; Таким образом, эта память доступна для обработки запросов.

FILESTREAM не включен автоматически при установке или обновлении SQL Server. Необходимо включить FILESTREAM с помощью диспетчер конфигурации SQL Server и SQL Server Management Studio. Для использования FILESTREAM нужно создать или изменить базу данных, которая будет содержать заданный тип файловой группы. После этого следует создать или изменить таблицу, чтобы она содержала столбец varbinary(max) с атрибутом FILESTREAM. После выполнения этих задач вы можете использовать Transact-SQL и Win32 для управления данными FILESTREAM.

Условия использования FILESTREAM

В SQL Server большие двоичные объекты могут быть стандартными данными varbinary(max ), которые хранят данные в таблицах или объектах FILESTREAM varbinary(max), которые хранят данные в файловой системе. Выбор в качестве хранилища базы данных или файловой системы определяется размером и назначением данных. Объекты FILESTREAM следует использовать в следующих случаях:

  • средний размер сохраняемых объектов превышает 1 МБ;
  • важен быстрый доступ для чтения;
  • Вы разрабатываете приложения, использующие средний уровень для логики приложения.

При работе с объектами меньшего размера сохранение больших двоичных объектов типа varbinary(max) в базе данных часто позволяет добиться лучшей производительности потоков.

Хранилище FILESTREAM

Хранилище FILESTREAM реализовано в виде столбца типа varbinary(max) , данные которого хранятся в файловой системе как большие двоичные объекты. Размеры объектов BLOB ограничены только размером тома файловой системы. Стандартное ограничение varbinary(max) размером 2 ГБ файлов не применяется к BLOB-объектам, хранящимся в файловой системе.

Чтобы указать необходимость сохранения данных столбца в файловой системе, укажите атрибут FILESTREAM для столбца varbinary(max) . Этот атрибут приводит к тому, что ядро СУБД хранить все данные для этого столбца в файловой системе, но не в файле базы данных.

Данные FILESTREAM должны сохраняться в файловых группах FILESTREAM. Файловая группа FILESTREAM представляет собой особую файловую группу, в которой вместо самих файлов содержатся системные каталоги файлов. Данные системные каталоги файлов называются контейнерами данных. Контейнеры данных — это интерфейс между хранилищем ядро СУБД и хранилищем файловой системы.

При использовании хранилища FILESTREAM обратите внимание на следующие аспекты:

  • Если таблица содержит столбец FILESTREAM, каждая строка должна иметь уникальный идентификатор строки без null.
  • в файловую группу FILESTREAM можно добавить несколько контейнеров данных;
  • Контейнеры данных FILESTREAM нельзя вложить.
  • При использовании отказоустойчивой кластеризации файловые группы FILESTREAM должны находиться на общих дисках.
  • файловые группы FILESTREAM могут размещаться на сжатых томах.

Интегрированное управление

Так как FILESTREAM реализуется как столбец varbinary(max) и интегрирован непосредственно в ядро СУБД, большинство средств управления и функций SQL Server работают без изменений для данных FILESTREAM. Так, например, с данными FILESTREAM можно использовать любые модели резервного копирования и восстановления, а резервное копирование данных FILESTREAM осуществляется при помощи структурированных данных в базе данных. Если вы не хотите создавать резервные копии данных FILESTREAM с реляционными данными, можно использовать частичное резервное копирование для исключения файловых групп FILESTREAM.

Встроенные функции безопасности

В SQL Server данные FILESTREAM защищены так же, как и другие данные: предоставляя разрешения на уровне таблицы или столбца. Пользователь, у которого имеется разрешение на столбец FILESTREAM в таблице, может открыть связанные файлы.

Примечание.

В данных FILESTREAM не поддерживается шифрование.

Только учетная запись, в которой выполняется учетная запись службы SQL Server, предоставляет разрешения для контейнера FILESTREAM. Рекомендуется, чтобы другие учетные записи не предоставлялись разрешения для контейнера данных.

Примечание.

Имена входа SQL не работают с контейнерами FILESTREAM. С контейнерами FILESTREAM работает только проверка подлинности NTFS или ReFS.

Доступ к данным BLOB с помощью Transact-SQL и доступа к потоковой передаче файловой системы

После хранения данных в столбце FILESTREAM вы можете получить доступ к файлам с помощью транзакций Transact-SQL или с помощью API Win32.

Доступ с помощью Transact-SQL

С помощью Transact-SQL можно вставлять, обновлять и удалять данные FILESTREAM:

  • Операция вставки позволяет выполнить предварительное заполнение поля FILESTREAM значением NULL, пустым значением или встроенными данными относительно небольшого размера. Однако при работе с большими объемами данных большей эффективности потока можно добиться при помощи файла, использующего интерфейсы Win32.
  • При обновлении поля FILESTREAM происходит изменение базовых данных BLOB в файловой системе. Если в поле FILESTREAM содержится значение NULL, данные BLOB, связанные с этим полем, удаляются. Вы не можете использовать блокированные обновления Transact-SQL, реализованные как UPDATE**.*Write(), для выполнения частичных обновлений данных.
  • При удалении строки или удалении или усечении таблицы, содержащей данные FILESTREAM, удаляются базовые данные BLOB в файловой системе.

Потоковый доступ к файловой системе

Поддержка потоковой передачи Win32 работает в контексте транзакции SQL Server. В рамках транзакции функции FILESTREAM позволяют получить логический UNC-путь к файлу в файловой системе. API-интерфейс OpenSqlFilestream позволяет получить дескриптор файла. Затем этот дескриптор может использоваться интерфейсами файловых потоков Win32, например ReadFile() и WriteFile(), для доступа и обновления файла посредством файловой системы.

Так как операции с файлами являются транзакциальными, вы не можете удалить или переименовать файлы FILESTREAM через файловую систему.

Предупреждение

Контейнер FILESTREAM — это папка, управляемая SQL Server. Не добавляйте или не удаляйте файлы в папке FILESTREAM вручную или с помощью других приложений. Если это сделать, это приведет к ошибкам резервного копирования и несоответствия. Дополнительные сведения см. в MSSQLSERVER_3056, MSSQLSERVER_7908 и MSSQLSERVER_7906.

Модель инструкций

Доступ к файловой системе FILESTREAM моделирует инструкцию Transact-SQL с помощью открытия и закрытия файла. Эта инструкция запускается при открытии дескриптора файла и завершается при закрытии этого дескриптора. Например, когда дескриптор записи закрыт, любой возможный триггер AFTER, зарегистрированный в таблице, запускается так, как если инструкция UPDATE завершена.

Пространство имен хранилища

В FILESTREAM ядро СУБД управляет пространством имен физической файловой системы BLOB. Новая встроенная функция, PathName, предоставляет логический UNC-путь объекта BLOB, соответствующего каждой ячейке FILESTREAM в таблице. В приложении этот логический путь используется для получения дескриптора Win32 и работы с данными BLOB посредством обычных интерфейсов файловой системы Win32. Эта функция возвращает значение NULL, если значением столбца FILESTREAM является NULL.

Доступ к файловой системе с транзакцией

Новая встроенная функция GET_FILESTREAM_TRANSACTION_CONTEXT()возвращает токен, представляющий актуальную транзакцию, с которой связан сеанс. Эта транзакция должна быть запущена, не прервана и не зафиксирована. Получение токена позволяет приложению связать потоковые операции файловой системы FILESTREAM с запущенной транзакцией. Эта функция возвращает значение NULL в случае отсутствия явно запущенной транзакции.

Прежде чем транзакция будет зафиксирована или прервана, все дескрипторы файлов должны быть закрыты. Если дескриптор остается открытым за пределами области транзакции, дополнительные операции чтения по дескриптору вызывают сбой; дополнительные записи в дескриптор успешно выполнен, но фактические данные не записываются на диск. Аналогичным образом, если база данных или экземпляр ядро СУБД завершает работу, все открытые дескрипторы недействительны.

Устойчивость транзакций

С помощью FILESTREAM при фиксации транзакции ядро СУБД обеспечивает устойчивость транзакций для данных BLOB-объектов FILESTREAM, измененных из доступа потоковой передачи файловой системы.

Семантика изоляции

Семантика изоляции регулируется уровнями изоляции транзакций ядро СУБД. Уровень изоляции с фиксацией на чтение поддерживается для доступа к Transact-SQL и файловой системе. Поддерживаются повторяемые операции чтения, сериализуемые и уровни изоляции моментальных снимков. Грязное чтение не поддерживается.

Открытые операции доступа к файловой системе не ожидают блокировки. Вместо этого открытые операции завершаются немедленно, если они не могут получить доступ к данным из-за изоляции транзакций. Вызовы API потоковой передачи завершаются ошибкой ERROR_SHARING_VIOLATION если открытая операция не может продолжаться из-за нарушения изоляции.

Чтобы разрешить выполнение частичных обновлений, приложение может издать команду FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT для выборки старого содержимого в файл, открытый с помощью ссылки на дескриптор. Это активирует копию старого содержимого на стороне сервера. Для повышения производительности приложений и предотвращения возникновения потенциальных времени ожидания при работе с очень большими файлами рекомендуется использовать асинхронный ввод-вывод.

Если формирование команды FSCTL произошло после записи в дескриптор, сохраняется последняя операция записи, а результаты предыдущих операций записи в дескриптор теряются.

API файловой системы и поддерживаемые уровни изоляции

Если API файловой системы не может открыть файл из-за нарушения изоляции, возвращается ERROR_SHARING_VIOLATION исключение. Это нарушение изоляции возникает в том случае, когда две транзакции пытаются получить доступ к одному и тому же файлу. Результат операции доступа зависит от режима открытия файла и версии SQL Server, на которой выполняется транзакция. В следующей таблице приведены возможные результаты для двух транзакций, которые обращаются к одному файлу.

Проводка 1 Проводка 2 Результат SQL Server 2008 (10.0.x) Результат sql Server 2008 R2 (10.50.x) и более поздних версий
Открыта для чтения. Открыта для чтения. Обе завершаются успешно. Обе завершаются успешно.
Открыта для чтения. Открыта для записи. Обе завершаются успешно. Операции записи в транзакции 2 не влияют на операции чтения, выполняемые в транзакции 1. Обе завершаются успешно. Операции записи в транзакции 2 не влияют на операции чтения, выполняемые в транзакции 1.
Открыта для записи. Открыта для чтения. При открытии для транзакции 2 возникает ошибка с исключением ERROR_SHARING_VIOLATION. Обе завершаются успешно.
Открыта для записи. Открыта для записи. При открытии для транзакции 2 возникает ошибка с исключением ERROR_SHARING_VIOLATION. При открытии для транзакции 2 возникает ошибка с исключением ERROR_SHARING_VIOLATION.
Открыта для чтения. Открыта для SELECT. Обе завершаются успешно. Обе завершаются успешно.
Открыта для чтения. Открыта для UPDATE или DELETE. Обе завершаются успешно. Операции записи в транзакции 2 не влияют на операции чтения, выполняемые в транзакции 1. Обе завершаются успешно. Операции записи в транзакции 2 не влияют на операции чтения, выполняемые в транзакции 1.
Открыта для записи. Открыта для SELECT. Транзакция 2 блокируется, пока транзакция 1 не будет зафиксирована или завершена или пока не истечет время ожидания блокировки транзакции. Обе завершаются успешно.
Открыта для записи. Открыта для UPDATE или DELETE. Транзакция 2 блокируется, пока транзакция 1 не будет зафиксирована или завершена или пока не истечет время ожидания блокировки транзакции. Транзакция 2 блокируется, пока транзакция 1 не будет зафиксирована или завершена или пока не истечет время ожидания блокировки транзакции.
Открыта для SELECT. Открыта для чтения. Обе завершаются успешно. Обе завершаются успешно.
Открыта для SELECT. Открыта для записи. Обе завершаются успешно. Операции записи в транзакции 2 не влияют на транзакцию 1. Обе завершаются успешно. Операции записи в транзакции 2 не влияют на транзакцию 1.
Открыта для UPDATE или DELETE. Открыта для чтения. При открытии для транзакции 2 возникает ошибка с исключением ERROR_SHARING_VIOLATION. Обе завершаются успешно.
Открыта для UPDATE или DELETE. Открыта для записи. При открытии для транзакции 2 возникает ошибка с исключением ERROR_SHARING_VIOLATION. При открытии для транзакции 2 возникает ошибка с исключением ERROR_SHARING_VIOLATION.
Открыта для SELECT с повторяющимся чтением. Открыта для чтения. Обе завершаются успешно. Обе завершаются успешно.
Открыта для SELECT с повторяющимся чтением. Открыта для записи. При открытии для транзакции 2 возникает ошибка с исключением ERROR_SHARING_VIOLATION. При открытии для транзакции 2 возникает ошибка с исключением ERROR_SHARING_VIOLATION.

Запись с удаленных клиентов

Удаленный доступ файловой системы к данным FILESTREAM осуществляется по протоколу SMB. Если клиент является удаленным, операции записи не кэшируются на стороне клиента. Операции записи всегда отправляются на сервер. Кэширование данных возможно на серверной стороне. Рекомендуется, чтобы приложения, работающие на удаленных клиентах, консолидируют небольшие операции записи в операции большего размера. Цель состоит в том, чтобы выполнять меньше операций записи.

Создание сопоставленных представлений памяти (сопоставленных операций ввода-вывода памяти) с помощью дескриптора FILESTREAM не поддерживается. Если сопоставление памяти используется для данных FILESTREAM, ядро СУБД не может гарантировать согласованность и устойчивость данных или целостность базы данных.

Рекомендации по повышению производительности FILESTREAM

Функция SQL Server FILESTREAM позволяет хранить двоичные данные двоичного объекта varbinary(max) в виде файлов в файловой системе. При наличии большого количества строк в контейнерах FILESTREAM, которые являются базовым хранилищем для столбцов FILESTREAM и FileTable, можно создать том файловой системы, содержащий большое количество файлов. Чтобы обеспечить оптимальную производительность при обработке интегрированных данных из базы данных и файловой системы, важно обеспечить оптимальную настройку файловой системы. Ниже приведены некоторые доступные параметры настройки файловой системы.

  • Проверка высоты драйвера фильтра FILESTREAM SQL Server (например, rsfx0100.sys). Оцените все драйверы фильтров, загруженные для стека хранилища, связанного с томом, в котором компонент FILESTREAM хранит файлы, и убедитесь, что драйвер RsFx находится в нижней части стека. Для перечисления драйверов фильтров для определенного тома можно использовать программу управления FLTMC.EXE. Ниже приведен пример выходных данных из служебной программы FLTMC: C:\Windows\System32>fltMC.exe фильтры

    Имя фильтра Число экземпляров Высота над уровнем моря Кадр
    Sftredir 1 406000 0
    MpFilter 9 328000 0
    luafv 1 135000 0
    FileInfo 9 45 000 0
    RsFx0103 1 41001.03 0

  • Убедитесь, что для этих файлов свойство "Время последнего доступа" отключено на сервере. Этот атрибут файловой системы сохраняется в реестре: имя ключа: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    Имя: NtfsDisableLastAccessUpdate
    Тип: REG_DWORD
    Значение: 1

  • Убедитесь, что на сервере отключена нотация 8.3. Этот атрибут файловой системы сохраняется в реестре: имя ключа: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    Имя: NtfsDisable8dot3NameCreation
    Тип: REG_DWORD
    Значение: 1

  • Убедитесь, что контейнеры каталогов FILESTREAM не имеют шифрования файловой системы или сжатия файловой системы, так как они могут привести к уровню накладных расходов при доступе к этим файлам.

  • В командной строке с повышенными привилегиями запустите экземпляры FLTMC и убедитесь, что к тому, на котором вы пытаетесь выполнить восстановление, не подключены драйверы фильтров.

  • Убедитесь, что контейнеры каталогов FILESTREAM не имеют более 300 000 файлов. Сведения из представления каталога sys.database_files можно использовать, чтобы выяснить, какие каталоги в файловой системе содержат файлы FILESTREAM-related. Такую ситуацию можно предотвратить с помощью нескольких контейнеров. (Дополнительные сведения см. в следующем элементе маркированного списка.)

  • При наличии только одной файловой группы FILESTREAM все файлы данных создаются в одной и той же папке. Создание файла очень большого количества файлов может повлиять на большие индексы NTFS, которые также могут стать фрагментированными.

    • Использование нескольких файловых групп обычно помогает решить эту проблему (приложение использует секционирование или содержит несколько таблиц, каждая из которых находится в собственной файловой группе).

    • В SQL Server 2012 (11.x) и более поздних версиях можно использовать несколько контейнеров или файлов в файловой группе FILESTREAM, а схема распределения циклического перебора будет применяться. Таким образом, количество файлов NTFS в каждом каталоге меньше.

  • Резервное копирование и восстановление можно ускорить с помощью нескольких контейнеров FILESTREAM, если для хранения контейнеров используются несколько томов.

    SQL Server 2012 (11.x) поддерживает несколько контейнеров для каждой файловой группы и упрощает работу. Для управления большим числом файлов не требуются сложные схемы секционирования.

  • Если в экземпляре SQL существует очень большое количество контейнеров FILESTREAM, запуск баз данных с большим количеством контейнеров FILESTREAM может занять много времени, чтобы зарегистрировать их в драйвере фильтра FILESTREAM. Распространение их в нескольких разных томах помогает улучшить время запуска базы данных.

  • Файл MFT файловой системы NTFS может стать фрагментированным, что может вызвать проблемы с производительностью. Зарезервированный размер MFT зависит от размера тома, поэтому вы можете и не столкнуться с этой проблемой.

    • Можно проверить фрагментацию MFT с помощью defrag /A /V C: (измените C: на фактическое имя тома).

    • Можно зарезервировать дополнительное пространство MFT с помощью команды fsutil behavior set mftzone 2.

    • Файлы данных FILESTREAM должны быть исключены из проверки антивирусным ПО.

      Примечание.

      Windows Server 2016 автоматически включает Защитник Windows. Убедитесь, что Защитник Windows настроен для исключения файлов FILESTREAM. Невыполнение этого действия может привести к снижению производительности операций резервного копирования и восстановления.

      Дополнительные сведения см. в разделе Настройка и проверка исключений для проверок антивирусной программой "Защитник Windows".

Связанный контент