Где SQL Server должен хранить файлы, такие как PDF, изображения и отсканированные документы? Это решение незаметно влияет на размер резервных копий, время восстановления и объем памяти, остающейся для выполнения запросов.
Хранение файлов в SQL Server включает в себя несколько возможных подходов: хранение файлов в виде BLOB-объектов varbinary(max) внутри базы данных, использование FILESTREAM (и построенной на его основе FileTable) или хранение файлов вне базы данных с сохранением только пути. Выбор подходящего варианта определяется размером объектов и характером доступа к ним. Microsoft рекомендует использовать FILESTREAM, если размер объектов в среднем превышает 1 МБ, в то время как более мелкие объекты обычно эффективнее передаются в виде внутрибазовых BLOB-объектов.
В этом руководстве объясняется, как работает каждый из вариантов, каковы компромиссы между ними и почему именно нижележащий уровень хранения в конечном итоге определяет производительность каждого из них.
Как SQL Server хранит данные и где располагаются большие объекты
Каждая база данных SQL Server состоит как минимум из двух файлов операционной системы: файла данных и файла журнала транзакций. Основной файл данных, по соглашению .mdf, содержит информацию о запуск базы данных и указывает на другие файлы в базе данных. База данных также может иметь второстепенные файлы данных, по соглашению .ndf. Администраторы добавляют их для таких целей, как управление размером и ростом файлов, включение резервного копирования и восстановления на уровне файловых групп или снижение конфликтов выделения в tempdb.
Журнал транзакций, по соглашению .ldf, записывает изменения, необходимые для восстановления базы данных. Технически база данных может иметь более одного файла журнала, но SQL Server записывает данные в журнал последовательно, по одному файлу за раз. Дополнительные файлы журналов не повышают производительность и добавляются только в определенных ситуациях, например, когда на диске с существующим журналом заканчивается свободное место.
Файлы данных организованы в файловые группы, которые представляют собой логические контейнеры. Когда вы создаете таблицу или индекс, вы можете указать файловую группу, к которой она принадлежит, и SQL Server распределяет свои страницы по файлам в этой файловой группе. Файловая группа не определяет, где что-либо располагается на диске; физическое расположение каждого файла задается его собственным путем. Файлы журналов не принадлежат ни к какой файловой группе.
SQL Server поддерживает три типа файловых групп:
- Файловые группы строк содержат обычные файлы данных. Файловая группа PRIMARY — одна из них, и именно в ней по умолчанию находятся таблицы, индексы и внутрибазовые BLOB-объекты.
- Файловые группы FILESTREAM содержат каталоги файловой системы, которые Microsoft называет контейнерами данных, а не файлы данных. Эти каталоги служат интерфейсом между СУБД (Database Engine) и файловой системой и являются основой для FILESTREAM и FileTable, описанных ниже.
- Файловые группы данных, оптимизированных для памяти, содержат файлы контрольных точек для таблиц In-Memory OLTP. Они создаются на базе того же механизма FILESTREAM «под капотом», и в базе данных может быть только одна такая группа.
Почему большие объекты создают нагрузку на макет по умолчанию
Если вы не выберете иной вариант, большой объект хранится в файловой группе строк, как и все остальное. PDF-файл размером 40 МБ, сохраненный как значение varbinary(max), находится в тех же файлах данных, что и клиентские записи и индексы. Он считывается через те же страницы, кэшируется в том же буферном пуле и копируется в каждой полной резервной копии.
Хранение файлов в базе данных обеспечивает транзакционную целостность, единую безопасность и одну резервную копию, охватывающую все, но может увеличить размер базы данных, увеличить время резервного копирования и восстановления, а также занять память буферного пула, которая в противном случае могла бы обслуживать запросы.
FILESTREAM против FileTable против BLOB: варианты файлового хранения в SQL Server
SQL Server предлагает три способа хранения файлов под управлением базы данных. Это не три альтернативы в меню. FileTable построена поверх FILESTREAM, а не рядом с ней, что является частым источником путаницы.
Внутрибазовые BLOB-объекты — это самый простой вариант и тот, который вы получаете по умолчанию. Столбец varbinary(max) хранит байты внутри базы данных, является полностью транзакционным и не требует настройки для включения. Его предел составляет 2 ГБ на значение. Резервные копии SQL Server могут включать данные BLOB, FILESTREAM и FileTable; данные FILESTREAM и FILESTREAM обрабатываются через их файловые группы FILESTREAM. Ниже рекомендации Microsoft в 1 МБ это часто является практичным выбором; его простота может перевесить потенциальный прирост эффективности от других вариантов.
FILESTREAM интегрирует СУБД с файловой системой NTFS или ReFS, сохраняя данные varbinary(max) в виде файлов на диске, в то время как SQL Server продолжает управлять ими. Ограничение в 2 ГБ исчезает, так как размеры BLOB-объектов FILESTREAM ограничены только томом, на котором они находятся. Это также меняет картину использования памяти: FILESTREAM кэширует данные файлов в системном кэше NT, а не в буферном пуле SQL Server, поэтому память буфера остается доступной для обработки запросов. Для сервера, испытывающего нехватку памяти, это часто является наиболее существенным отличием из трех вариантов файлового хранения.
Перед принятием решения стоит учесть два ограничения. FILESTREAM применяется к SQL Server в ОС Windows, поэтому он недоступен при развертывании в Linux. Кроме того, шифрование не поддерживается для данных FILESTREAM, что может стать сюрпризом для команд, которые предполагают, что шифрование на уровне базы данных охватывает все, к чему прикасается база данных. Если эти данные конфиденциальны, спланируйте защиту на другом уровне и уточните текущее поведение для вашего выпуска.
FileTable добавляет структуру каталогов, похожую на файловую систему, поверх FILESTREAM. Каждая строка представляет собой файл или каталог, а содержимое предоставляется через общий ресурс Windows, поэтому существующие приложения Windows могут читать и записывать файлы без изменения кода. Эти операции API Windows являются нетранзакционными. FileTable — это отдельный тип таблицы с предопределенной схемой, а не дополнение к существующей таблице. Она построена на базе FILESTREAM и предоставляет файлы через общий ресурс Windows, в то время как доступ по T-SQL остается транзакционным. Если вам нужно, чтобы приложение продолжало просматривать файловый ресурс, пока SQL Server управляет файлами позади него, FileTable создана именно для этого.
Когда следует хранить файлы вне базы данных
Расширенной альтернативой хранению внутри базы данных является полное хранение файлов вне SQL Server. Каждый файл записывается в систему файлового хранения или хранилище объектов, а в столбце базы данных сохраняется только его путь или ключ объекта. Компания Microsoft отмечает, что это типичный шаблон для предприятий: неструктурированные файловые данные хранятся в файловой системе, в то время как связанные метаданные хранятся в реляционной базе данных.
Такой подход позволяет уменьшить размер базы данных, благодаря чему резервное копирование и восстановление базы данных масштабируются вместе с реляционными данными, а не с общим объемом файлов. Он также позволяет управлять файлами, распределять их по уровням и масштабировать независимо от базы данных. Для больших объемов неструктурированных данных это часто оказывается более экономичным и масштабируемым вариантом.
Поскольку SQL Server больше не управляет файлами, некоторые обязанности переходят от ядра СУБД к командам приложений и операций.
Проблемы, требующие решения
- Согласованность между файлами и записями. База данных не может гарантировать, что файл и его строка остаются синхронизированными. Неудачная выгрузка может привести к тому, что строка будет указывать на отсутствующий файл, а удаленная строка может оставить «осиротевший» файл.
- Транзакции. Транзакция базы данных не может включать запись файла. Если одна операция завершается успешно, а другая завершается сбоем, приложение должно обнаружить и разрешить это несоответствие.
- Координация резервного копирования и восстановления. Резервное копирование баз данных и файловых хранилищ выполняется раздельно. Восстановление обоих компонентов к одной и той же точке во времени требует планирования, особенно для восстановления на определенный момент времени.
- Безопасность. Разрешения SQL Server не распространяются на внешние файлы. Средства контроля доступа на уровне файловой системы или объектного хранилища должны настраиваться и поддерживаться отдельно.
- Высокая доступность и аварийное восстановление. Функции SQL Server, такие как группы доступности Always On, реплицируют базу данных, но не внешние файлы. Файловое хранилище нуждается в собственной стратегии репликации или аварийного переключения.
- Удаление и хранение. Удаление записи не приводит к удалению связанного с ней файла. Политики хранения и операции удаления должны применяться в обоих местах.
Распространенные методы снижения рисков
Команды обычно решают эти задачи с помощью нескольких стандартных практик:
- Записывайте файл перед вставкой строки, чтобы в случае сбоя оставался «орошенный» файл, а не поврежденная ссылка.
- Запускайте периодическую процедуру сверки, которая сравнивает сохраненные пути с фактически существующими файлами.
- Выполняйте удаление в два этапа с помощью процесса очистки, который удаляет файлы после удаления связанных с ними записей.
Эти практики хорошо зарекомендовали себя, но они требуют написания дополнительного кода для приложений и внедрения операционных процессов, которые необходимо создавать, тестировать и поддерживать. Эти постоянные усилия являются ключевой частью сравнения с FILESTREAM, наряду с производительностью и стоимостью.
Как базовое хранилище влияет на производительность
Эти варианты меняют то, как SQL Server обрабатывает операции ввода-вывода файлов, но они не устраняют потребность в производительном хранилище. FILESTREAM перенаправляет операции ввода-вывода с крупными объектами через файловую систему, а не через ядро СУБД, поэтому производительность зависит от того, сможет ли базовый томовый массив удовлетворить требования рабочей нагрузки к пропускной способности и задержке. Если это невозможно, уровень хранения останется узким местом.
Эти соображения также применимы к удаленному доступу к FILESTREAM. Удаленный доступ использует протокол SMB, и данные FILESTREAM не кэшируются на клиенте, поэтому операции записи отправляются на сервер. Microsoft рекомендует удаленным приложениям объединять небольшие операции записи в более крупные для повышения эффективности. Приложения, использующие удаленный доступ к FILESTREAM, должны в комплексе оценивать пропускную способность сети, производительность хранилища и шаблоны записи.
Независимо от выбранного варианта, базовое хранилище влияет на задержку и пропускную способность, доступные для рабочей нагрузки. Блочное хранилище на базе NVMe flash может обеспечивать иную производительность в условиях смешанных случайных и последовательных нагрузок по сравнению с универсальным томом, выделенным под файловый ресурс. Рабочие нагрузки с крупными объектами могут быстро выявить эти различия.
Как Everpure помогает
Everpure™ FlashArray™ предоставляет для файловых нагрузок и нагрузок с крупными объектами в SQL Server уровень хранения, который выдерживает смешанный ввод-вывод, создаваемый этими функциями. Архитектура NVMe обеспечивает стабильно низкую задержку независимо от того, выполняются ли транзакционные запросы к реляционным таблицам или потоковое чтение многогигабайтنых объектов из контейнера FILESTREAM, что имеет решающее значение, так как эти два паттерна обычно делят одну систему.
Для сред Microsoft SQL Server Everpure обеспечивает быстрое восстановление баз данных SQL Server объемом 10 ТБ за 10 минут или менее, а также доказанную доступность на уровне 99,9999% для FlashArray с нулевым плановым временем простоя. Время восстановления — это тот критический фактор, который усугубляется при использовании внутрибазы данных для хранения файлов, поэтому этот показатель стоит сопоставить с вашими собственными целями по восстановлению перед принятием решения о том, какой объем файловых данных хранить внутри базы данных.
Снимки (snapshots) Everpure делают создание копий базы данных быстрым и эффективным с точки зрения пространства, что меняет подход к управлению значительными объемами файлов в базе данных. Обновление тестовой среды перестает быть вопросом емкости. Evergreen//One™ связывает эту емкость с потреблением, а не с циклом закупок, обеспечивая снижение совокупной стоимости владения на величину до 35%, что отлично подходит для файловых томов, которые стабильно растут и редко уменьшаются.
Ознакомьтесь с решениями Everpure для хранения баз данных, чтобы узнать, как уровень хранения под SQL Server может расширить возможности вашей архитектуры файлового хранения.
Заключение
Правильный выбор варианта хранения файлов в SQL Server зависит главным образом от размера файлов, шаблона доступа и того, насколько тесно файлы должны быть согласованы с записями в базе данных.
- Большие двоичные объекты (BLOB) внутри базы данных (varbinary(max)) обычно хорошо подходят для файлов небольшого размера и умеренных объемов. Рекомендации Microsoft предполагают, что для объектов размером менее 1 МБ такое хранение часто обеспечивает лучшую производительность.
- FILESTREAM подходит для более крупных файлов, которые должны оставаться транзакционно согласованными со своими строками. Кроме того, он исключает попадание данных файлов в пул буферов SQL Server.
- FileTable разработан для приложений Windows, которым необходим доступ к файлам через стандартный файловый ресурс.
- Внешнее файловое или объектное хранилище часто является наиболее масштабируемым и экономичным выбором для больших объемов неструктурированных данных. При этом оно требует от приложения самостоятельного управления согласованностью, безопасностью и очисткой.
Какой бы вариант вы ни выбрали, операции ввода-вывода файлов по-прежнему зависят от базового хранилища. Каждый вариант лишь меняет место выполнения ввода-вывода крупных объектов, а не исключает его. Задержка и пропускная способность уровня хранения влияют на производительность выбранного подхода, поэтому его следует оценивать наряду с проектированием файлового хранилища.
EXCERPT:
Управление крупными объектами в SQL Server требует выбора правильной стратегии хранения. Узнайте, в чем разница между BLOB-объектами, FILESTREAM и FileTable, и какую роль в производительности играет лежащий в основе уровень хранения.
SUMMARY:
Варианты хранения файлов в SQL Server, включая BLOB, FILESTREAM, FileTable и внешнее хранилище, предлагают различные компромиссы в отношении транзакционной согласованности, доступа приложений, резервного копирования и восстановления, масштабируемости и производительности.
Часто задаваемые вопросы о хранении файлов в SQL Server
Стоит ли хранить файлы в базе данных SQL Server?
Как правило, нет. Для большинства приложений хранение файлов во внешнем хранилище с сохранением пути в базе данных является более простым, дешевым и масштабируемым решением. Храните файлы в базе данных, если файл и соответствующая ему строка должны оставаться транзакционно согласованными, и вы не хотите самостоятельно разрабатывать и поддерживать такую сверку. Microsoft рекомендует использовать FILESTREAM для объектов со средним размером более 1 МБ, когда важен быстрый доступ на чтение; для более мелких объектов лучше подходит хранилище varbinary(max) внутри базы данных.
Что такое FILESTREAM в SQL Server?
FILESTREAM — это функция SQL Server, которая хранит данные varbinary(max) в виде файлов в файловой системе NTFS или ReFS, в то время как ядро СУБД продолжает управлять ими. Transact-SQL позволяет вставлять, обновлять, запрашивать и выполнять резервное копирование этих данных, а интерфейсы Win32 поддерживают их потоковую передачу. Эта функция снимает ограничение в 2 ГБ на одно значение и исключает кэширование файлов из пула буферов SQL Server. Она применима к SQL Server на платформе Windows.
В чем разница между FILESTREAM и FileTable?
FileTable создана на базе FILESTREAM, а не является ее альтернативой. FILESTREAM предоставляет файловое хранилище для столбца, доступ к которому осуществляется через T-SQL или Win32 API. FileTable добавляет иерархию каталогов и представляет ее в виде общего ресурса Windows, благодаря чему обычные приложения Windows могут работать с файлами без каких-либо изменений. Каждая строка в FileTable представляет файл или каталог.
Как хранить изображения и документы в SQL Server?
Существует несколько вариантов. Столбец varbinary(max) хранит их внутри базы данных, что подходит для небольших файлов и малых объемов. FILESTREAM хранит их в файловой системе под управлением базы данных, что подходит для более крупных объектов. FileTable делает то же самое, но предоставляет доступный для просмотра общий ресурс для приложений Windows. Для больших объемов крупных файлов часто лучшей архитектурой является их хранение за пределами базы данных с указанием пути в столбце.
Можно ли шифровать данные FILESTREAM?
Шифрование не поддерживается для данных FILESTREAM, поэтому шифрование на уровне базы данных не распространяется на файлы, хранящиеся таким образом. Если содержимое файлов является конфиденциальным, запланируйте защиту на другом уровне — на уровне хранилища или приложения, а также проверьте поведение для конкретного выпуска SQL Server, который вы используете, прежде чем проектировать архитектуру на его основе.
