Как хранить файлы в базе данных

Организация хранения файлов в базе данных Microsoft SQL Server. Универсальный способ

Современные базы данных могут хранить самые различные виды информации. В том числе, целые файлы.

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

Современные системы управления, базами данных, включая Microsoft SQL Server (MS SQL), и средства программирования прекрасно справляются с данной задачей.

Существуют два способа хранения файлов в базах данных MS SQL.

Хранение файла в поле с двоичными данными (тип данных VARBINARY(MAX));
Использование файловых таблиц.

Второй способ стал доступен вместе с появлением файловых таблиц в MS SQL 2012 и в более ранних версиях его использование не возможно. Первый способ поддерживают все без исключения версии MS SQL (включая 2014) и он является, по сути, универсальным. Именно он и будет рассмотрен в данной статье.

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

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

Вот примерный вариант такой таблицы:

id – уникальный идентификатор. Первичный ключ;
fileName – строковое поле (например, nvarchar(255)) с именем файла;
binaryData – поле с двоичными данными (VARBINARY(MAX)) в котором собственно и хранится файл.

Рассмотрим работу с такой таблицей из программы клиента на примере Delphi и C#.

Вы храните в базе данных документы и картинки?

В SQL Server 2008 появились две новые возможности для хранения неструктурированных данных — FILESTREAM и удаленное хранилище BLOB_объектов (Remote BLOB Storage).

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

Помимо этого, в SQL Server 2008 полностью поддерживается хранение бинарных объектов в стандартных BLOB_колонках с типом данных varbinary. Рассмотрим каждую из перечисленных возможностей более подробно.

Варианты хранения BLOB в SQL Server 2008
Для хранения неструктурированных данных в SQL Server используются два типа данных — binary и введенный в SQL Server 2005 тип данных varbinary(max). Тип данных binary позволяет хранить в колонке или переменной данного типа до 8000 байт, а varbinary(max) — до 2147483647 байт. Модификатор max позволяет управлять физическим расположением данных на страницах таблицы — для этого используется табличная опция large value types out of row. Если эта опция включена, на странице данных для конкретной записи хранится 16_байтовый указатель на присоединенные страницы, где хранятся все данные. Когда эта опция отключена, значения объемом до 8000 байт хранятся непосредственно на страницах данных, а более объемные значения — на отдельных, присоединенных страницах.
Для включения данной опции используется следующая команда:

Новые возможности в SQL Server 2008 — FILESTREAM и Remote BLOB позволяют более эффективно решать вопросы хранения неструктурированных данных, но в тех случаях, когда размер объектов не очень большой — порядка 150_250 Кбайт, можно по прежнему использовать тип данных varbinary.
Рассмотрим теперь более подробно работу с этими новинками.

Производительность при использовании данных будет соответствовать потоковым возможностям файловой системы;
Размер хранимых объектов будет ограничен объемом свободного пространства на дисковом хранилище.

Прежде чем использовать FILESTREAM, необходимо включить эту функциональность для данного экземпляра SQL Server. Для этого используется хранимая процедура sp_filestream_configure:

Также можно включить поддержку FILESTREAM в SQL Server Management Studio — для этого используется вкладка Advanced диалоговой панели Server Properties.

После того как поддержка FILESTREAM включена одним из описанных выше способов, мы можем создать базу данных, использующую новые возможности SQL Server. Так как FILESTREAM использует специальный тип файловой группы, необходимо указать атрибут CONTAINS FILEGROUP для как минимум одной файловой группы для базы данных. Ниже показан пример создания базы данных с именем FileStreamDB. Эта база данных содержит три файловые группы — PRIMARY, RowGroup1 и FileStreamGroup1. Группы PRIMARY и RowGroup1- это обычные файловые группы и они не могут содержать данные FILESTREAM — эта возможность доступна только в файловой группе FileStreamGroup1.

[необходим совет]хранение файлов в БД

Небольшая предистория: работаю в фирме, которая оказывает услуги, пишу для нас программку, чтоб упорядочить работу с заказами. С каждым заказом у нас связанно несколько файлов (в основном макеты). Все данные по заказам я постепенно перевел в БД, теперь подошла очередь фалов.

Собственно вопрос: файлы я планирую хранить в БД на серере, но действительно ли это удобно? Насколько сильно это нагрузить сервер БД? Насколько больше дискового пространства будет использоватся? Какие еще возможны подводные камни?

Прошу людей, знакомых с подобными вопросами, дать мне ценных советов.

Читать:

Av console что это за программа в автозагрузке

Немного подробностей о БД: PostgreSQL, файлы планировал хранить как бинарные данные (E’\\xxx’).

В общем как-то вот так все сумбурно написалось.

Всем заранее спасибо за советы.

При хранении в БД проблемы могут быть с отдачей. К примеру как реализовать докачку? Если не лень писать Rang-и ручками, то нормально. Однако когда суммарный размер файлов будет слишком большой БД будет тормозить. Самое первое что можно сделать, это перенести файлы в отдельную БД, что бы не загружать основную. Файлы в конце концов и так не очень быстро отдаются. А вообще лучше всего это хранить файлы в файловой системе и отдавать nginx, а в БД хранить ссылки на файлы.

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

Хранить в файловой системе файлы действительно намного удобнее, но для этого необходимо писать фронт-энд для сервера, который будет эти фалы принимать, раскладывать по директориям и оформлять их в БД.

Отдельная БД это интересный вариант, но хотелось бы узнать, почему база начнет тормозить из-за фалов.

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

Сейчас нет — завтра да. Сейчас макеты веб-страниц, а завтра макет плаката в tiff. Сейчас только внутренние пользователи, а завтра директору понравится и скажет не посылать по электронной почте макеты, а отправлять ссылки на сайт, с логином и паролем. Требования меняются в этом мире.

>Хранить в файловой системе файлы действительно намного удобнее, но для этого необходимо писать фронт-энд для сервера, который будет эти фалы принимать, раскладывать по директориям и оформлять их в БД.

Делаю сейчас один проект, столкнулся с похожей задачей. Много файлов до 1-2мб, и полное нежелание писать что-то на сервере.

Решил сделать ftp, а всю логику вогнать в прогу-клиент, благо логики на 20 строк. Если у тебя всё делает прога, то это вполне нормальный выбор, в БД только пути хранить.

>Сейчас нет — завтра да. Сейчас макеты веб-страниц, а завтра макет плаката в tiff. Сейчас только внутренние пользователи, а завтра директору понравится и скажет не посылать по электронной почте макеты, а отправлять ссылки на сайт, с логином и паролем. Требования меняются в этом мире.

Тогда нужно будет хитрый плагинчик для ngnix написать, чтоб он файлы из БД дергал, а не с hdd. Думаю, что это вопрос отдаленного будущего.

>Решил сделать ftp, а всю логику вогнать в прогу-клиент, благо логики на 20 строк. Если у тебя всё делает прога, то это вполне нормальный выбор, в БД только пути хранить.

Интересный вариант, надо будет обдумать.
А как вопрос безопасности ftp? Просто детально этот протокол не смотерл, но где-то видел не очень хорошие отзывы.

>А как вопрос безопасности ftp? Просто детально этот протокол не смотерл, но где-то видел не очень хорошие отзывы.

Не думаю что там есть какие-то жесткие проблемы. А, если сравнивать с http, он позволяет получать разных юзеров с разными правами без дополнительной возни с http-авторизацией. И без всяких post-запросов.

Ествественно надо не анонимный доступ:)

Лучше уж не ftp, а WebDav

>Лучше уж не ftp, а WebDav

Точно, про него совсем забыл, впрочем, никогда с ним не работал серьёзно. А чем он лучше ftp?

plperl

Есть два способа:
1) хранить файлы в БД
+ транзакции и все с ними связанное
+ один интерфейс для файлов и метаинформации
+ удобно настраивать права доступа
+ один бакап
— 99% объема БД занимают файлы, бакап/ресторе длится трое суток, очень обидно что 99% этого времени уходит на статичную информацию 🙂
— скорость обмена файлами значительно ниже, нагрузка на сеть (не забываем, что для BYTEA трафик в 3 раза больше) и на сервер выше чем доступ по ftp/smb. Если у вас тысяча пользователей одновременно не пишут файлы, то обычно это не актуально.

2) хранить файлы в ФС, а в БД только метаинформация
плюсы и минусы первого способа меняются знаками

В PostgreSQL есть очень удобная фича — plperlu. Он позволяет совместить некоторые плюсы обоих способов. В БД пишется интерфейс для BYTEA, и пользователи работают только с SQL. А файлы реально хранятся в ФС.

В ИС моей конторы к документам привязываются файлы. В БД хранится метаинформация. Сами файлы сжимаются, в них добавляется дополнительная инфа, и они хранятся на ftp. Пользователи прямого доступа к ftp не имеют — работа только через ИС. Для сторонних программ файлы доступны через файловый сервер, это реализовано на FUSE.