gifts2017

Организация файлового хранилища на базе MS SQL Server с доступом из 1С

Опубликовал Антон Андреев (tohandr) в раздел Администрирование - Сервисные утилиты

Пример реализации хранения файлов в базе MS SQL Server с использованием различных технологий. Подробно рассмотрен потоковый доступ к файлам с использованием технологии FileStream.

На текущий момент в конфигурациях 1С наиболее распространены два способа хранения прикрепленных файлов:

  1. Непосредственно в базе 1С
  2. В файловом хранилище.

О достоинствах и недостатках этих способов рассказано весьма подробно.

Существует и третий способ - хранение файлов во внешней базе, к примеру, MS SQL Server. До версии MS SQL Server 2008 данный способ не особо отличался от первого способа, т.е. хранения непосредственно в базе данных. Но начиная с MS SQL Server 2008 разработчикам была предложена новая технология хранения неструктурированных данных FileStream.

Если вкратце - технология FileStream предоставляет особый способ хранения и доступа к файлам:

  • На уровне СУБД файл помещается в определенное поле таблицы, но хранится при этом в особой файловой группе.
  • Каждой записи соответсвует физический файл на диске.
  • Доступ к файлам обеспечивает MS SQL Server, т.е. поддерживается транзакционное чтение/запись и ограничение доступа на основе прав доступа пользователя СУБД, а не Windows.
  • Файлы хранятся в специальной файловой группе, поэтому возможно расположение их на отдельном физическом диске.
  • Файловую группу FileStream возможно включать в бэкап базы.
  • Доступ к файлам возможен как через T-SQL-запросы, так и посредством потокового доступа (API файловой системы win32).
  • При потоковом доступе не используется память MS SQL Server, отсутствует высокая нагрузка на сервер, для кэширования файлов используется системный кэш Windows.

Приятным дополнением является полноценная поддержка FileStream бесплатным SQL Express, при этом отсутствуют какие-либо ограничения на размер файловой группы FileStream.

Существуют и ряд недостатков технологии FileStream:

  • На диске все файлы размещаются в одном каталоге, что при большом количестве файлов приводит к проблемам со скоростью доступа. Microsoft дает несколько рекомендаций при использовании томов хранилища FileStream.
  • С мелкими файлами (менее 1 Мб), особенно при потоковом доступе, обеспечивается более низкая скорость доступа, чем при хранении файлов напрямую в базе. Сравнение производительности различных технологий доступа к файлам 
  • Для реализации потокового доступа к файлам требуется написание специального клиентского приложения, в случае 1С - создание внешней компоненты. 

Как правило, прикрепляемые файлы в 1С имеют различный размер и утверждать, что большинство будут иметь размер больше 1 Мб или наоборот, было бы ошибочно. Поэтому было решено создать базу MS SQL, которая хранит файлы, как непосредственно в базе, так и в файловой группе FileStream.

База MS SQL состоит из трех таблиц:

1) innerStorage для хранения файлов непосредственно в базе, 

2) outerStorage для внешнего хранения файлов FileStream,

3) locationStorage - для определения в какой таблице хранится файл.

Скрипт для создания базы со всеми таблицами и хранимыми процедурами приложен в архиве.

В качестве идентификатора файла выступает ГУИД, в MS SQL он преобразуется в тип uniqueidentifier. Для передачи параметра между системами используется преобразование к строке.

В базе 1С указывается пограничный размер файла: если помещаемый файл меньше указанного размера, то файл помещается в таблицу innerStorage, больше - outerStorage. Одновременно делается  запись в таблицу locationStorage о расположении файла.

При получении файла в 1С, сначала делается запрос о расположении файла, а затем запрос к соответствующей таблице.

При обновлении файла также сначала делается запрос о расположении файла, вычисляется размер для определения нового типа расположения. Если тип расположения меняется, то старый файл удаляется из старого места и добавляется в новое.

Запись файлов непосредственно в базу данных довольно тривиальная задача. Описаний решений существует великое множество, к примеру, http://infostart.ru/public/283336/ Описанные методы кодирования/декодирования из двоичных данных в Base64 успешно работают. Для небольших файлов скорость работы вполне удовлетворительная.

Из особенностей моей реализации данной подсистемы можно отметить:

  1. Использование хранимых процедур и функций для добавления, обновления, удаления и получения файлов.
  2. Использование механизма внешних источников данных у 1С для вызова хранимых процедур.

В принципе, можно было все реализовать более традиционным способом через создание ADO соединения, но подход, когда база предоставляет все необходимое АПИ для работы является более правильным. Использование внешних источников 1С позволило сделать код 1С еще минимальней:

//добавление файла
ПараметрыСоединения = Новый ПараметрыСоединенияВнешнегоИсточникаДанных;
ПараметрыСоединения.СтрокаСоединения = "DRIVER={SQL Server};" + СтрокаПодключения;
ХранилищеВнешнихФайлов = ВнешниеИсточникиДанных.ХранилищеВнешнихФайлов;
ХранилищеВнешнихФайлов.УстановитьОбщиеПараметрыСоединения(ПараметрыСоединения);
ХранилищеВнешнихФайлов.УстановитьСоединение();

ХранилищеВнешнихФайлов.ДобавитьФайл(ИдФайла, Base64Строка(ДанныеФайла));

С организацией хранения больших файлов с использованием технологии FileStream все оказалось несколько сложней. Непосредственно создание базы и таблицы для хранения файлов не вызывает никаких вопросов. Все подробно описано. Непосредственно доступ к файлам возможен, как через Transact-SQL-запросы, так и использование API файловой системы.

В случае Transact-SQL-запросов решение было бы практически идентично первому способу - изменения только на внутреннем уровне хранения файлов. При этом на MS SQL Server легла бы высокая и не свойственная ему нагрузка по извлечению, кодированию/декодированию больших файлов. 

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

Только одна проблема - 1С не умеет работать напрямую с dotNet классами. Поэтому пришлось реализовать COM-объект для работы с классом SqlFileStream из 1С.  В архив приложены 32-х разрядная версия внешней компоненты SqlStreamAccess.dll и 64-х разрядная SqlStreamAccess64.dll. Регистрация Com-объекта производится путем вызова regasm.exe нужной разрядности под полными правами.

Реализованный COM-объект имеет одно свойство ConnString и два метода: InsertFile и  SelectFile, которые позволяют добавлять, обновлять и получать файлы.

Пример работы с  COM-объектом SqlStreamAccess:

// добавление нового файла
ТипДобавления = 1; //создание новой записи
ФайлПоток = Новый COMОбъект("SqlStreamAccess.SqlFile");
ФайлПоток.ConnString = СтрокаПодключения;
ФайлПоток.InsertFile(ИдФайла, ИмяФайла, ТипДобавления);
ФайлПоток = Неопределено;
	
// получение файла
ФайлПоток = Новый COMОбъект("SqlStreamAccess.SqlFile");
ФайлПоток.ConnString = СтрокаПодключения;
ФайлПоток.SelectFile(ИдФайла, ИмяФайла);
ФайлПоток = Неопределено;

В качестве идентификатора файла  также выступает ГУИД, приведенный к строке. В качестве имени файла указывается полный путь к файлу, который должен быть помещен/получен из базы. Естественно, пользователь Windows, под которым запущен сервис 1С, должен иметь полный доступ к этому файлу. Также данный пользователь должен иметь права для доступа к базе MS SQL посредством Windows-аутентификации.

Отдельного объяснения требует третий параметр метода "InsertFile". Он имеет целочисленый тип данных и может принимать три значения:

1 - проиcходит добавление файла

2 - происходит обновление файла без смены типа хранилища

3 - происходит обновление файла со сменой типа хранилища (удаляется из innerStorage, добавляет в OuterStorage).

Примеры работы с внешним хранилищем файлов можно посмотреть в приложенной в архиве конфигурации 1С. Встроенная обработка "РаботаСВнешнимХранилищемФайлов" предоставляет все высокоуровненые методы для получения, добавления, обновления и удаления файлов.

Скачать файлы

Наименование Файл Версия Размер Кол. Скачив.
FileStorage
.zip 17,00Kb
28.07.16
17
.zip 17,00Kb 17 Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Максим Кузнецов (Makushimo) 28.07.16 11:38
Не понятно, чего мы получаем от использования этой технологии.
Будет быстрее? Что будет быстрее?
Будет удобнее? как и в чем удобство?
наглядный примерчик бы.

ради чего все эти пляски?
Dragonim; +1 Ответить
2. Антон Андреев (tohandr) 28.07.16 14:13
Преимущество чего над чем?
- Хранение файлов в базе по сравнению с файлами на диске - повышается надежность.
- Хранение во внешней базе, а не в 1С - поможет существенно сэкономить в размерах базы 1С и снизить нагрузку на сервер 1С и СУБД. Также возможен доступ из других систем.

Основные преимущества технологии FileStream по сравнению с хранением в базе достигаются при хранении больших файлов (более 1 Мб). Файл не загружается в память SQL Server, фактически, считывание и запись файла производится порциями, указанного размера, используя АПИ файловой системы. Непосредственно в таблице хранится только ссылка на файл.
Скорость доступа также увеличивается, если файлы более 1 Мб - http://www.codeproject.com/Articles/32216/How-to-store-and-fetch-binary-data-into-a-file-str
Кроме того, файловую группу FileStream можно вынести на отдельный диск. Также можно включать файловую группу в бэкап или не включать.

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


3. panvartan panvartan (panvartan) 28.07.16 18:14
Вы это делали от любви к искусству или задача какая была, проиллюстрируйте решение если можно.
4. Антон Андреев (tohandr) 28.07.16 19:17
Задача была вполне конкретная - сделать файловое хранилище во внешней базе. Хранение файлов на диске или в базе 1С заказчика не устраивало.
Размер файлов от нескольких килобайт до десятков мегабайт.
5. Максим Кузнецов (Makushimo) 29.07.16 05:57
(2) tohandr,
то есть по простому если сказать, то
Когда мы имеем базу учебных материалов с короткими видео и тяжелыми презентациями (более 1мб), к которой одновременно ломятся более 100 пользователей, тогда
- в случае хранения этих файлов средствами 1С (на диске или в базе) пользователи будут ждать по часу каждый видос
- в случае хранения этих файлов вашим способом пользователи мгновенно будут открывать смотреть читать писать эти файлы одинаково быстро не зависимо от количества пользователей (хоть 100 хоть 1000).

верно?
6. Антон Андреев (tohandr) 29.07.16 10:45
(5) Makushimo,
Не совсем...
Если говорить только про скорость доступа, то наиболее быстрый способ для больших файлов - хранение файлов на диске. Минимум посредников, наиболее низкоуровневный из доступных из 1С способ доступа. Используется апи файловой системы
При потоковом доступе FileStream скорость доступа снижается, т.к. появляется посредник в виде MS SQL, увеличиваются накладные расходы, но непосредственно запись и чтение файлов на диск также осуществляются, фактически, теми же апи файловой системы, используется системный кэш Windows. При этом повышается надежность хранения - файл записывается в транзакции, никто не имеет непосредственного доступа к файлам.
При хранении больших файлов непосредственно в базе, скорость будет наиболее низкой - накладные расходы наиболее высокими. Файлы будут полностью помещаться в память MS SQL Server и 1С Предприятия, т.е. если одновременно 100 пользователей запросят файлы по 10 Мб, то в памяти окажется 1 Гб абсолютно ненужного для работы MS SQL Server и 1С Предприятия мусора, что в итоге может сказаться на быстродействии всей системы.
7. Егор Иванов (Infactum) 29.07.16 11:09
Явно не хватает сравнения плюсов/минусов с типовым функционалом БСП по хранению файлов в томах на диске.
Makushimo; +1 Ответить
8. Максим Кузнецов (Makushimo) 29.07.16 11:19
(6) tohandr,
Ясно,
значит все дело в защите файлов от внешнего доступа.
А сама ОС разве не регулирует доступ к папкам разными там политиками безопасности?
9. Антон Андреев (tohandr) 29.07.16 11:58
(8) Makushimo,
Защита от несанкционированного доступа - это не единственное преимущество.
Доступ к файлам правами доступа ОС можно настроить какой угодно. Но полный доступ в любом случае будет у пользователя сервера 1С и админов.
И тут уже вмешивается человеческий фактор: у админа рука дрогнет и случайно удалит какую-нибудь папку или горе-программист напишет УдалитьФайлы() без указания конкретного файла. Случаев может быть множество, они весьма редки, но если в один прекрасный день пользователь не может найти очень важного файла, т.к. он физически отсутствует на диске - отношение к этим случайностям резко меняется. Самое плохое, что при хранении файла на диске, в случае его "случайного" удаления - понять, как это произошло и кто виноват, чаще всего, невозможно.
При хранении файлов в базе риск "случайного" удаления резко снижается.

Есть и другие достоинства:
- транзакционный доступ к файлам,
- бэкап средствами MS SQL Server.

Если описать кратко - существенно повышается надежность хранения.
10. Максим Кузнецов (Makushimo) 29.07.16 12:21
(9) tohandr,
ну вот теперь все более менее понятно.
Спасибо.
11. marat_n q (marat_n) 29.07.16 12:54
Есть небольшой нюанс в плане чистоты лицензий, если SQL сервер покупается вместе с 1С, то там, насколько я помню, использование сервера ограничено исключительно базами 1С.
12. Алексей Соловьев (Silenser) 29.07.16 16:14
Делал и выкладывал тут в свое время аналогичную разработку, когда принималось решение о типе хранения данных, наталкивался на опыт людей о том, что при большом количестве файлов в каталоге файловой системы, файловая система начинает подтормаживать. Людьми делалось предположение, что это вызвано тем, что при большом количестве файлов системе нужно большее время на поиск адреса размещения файла на физическом диске, т.к. увеличивается размер таблицы FAT. Сам не проверял. Но в итоге сделал таблицу с varbinary(max).
Так же не совсем понятно из описания технологии Filestream, насколько система будет устойчива при одновременном доступе к одному и тому же файлу. Не будет ли каких-либо подводных камней.
13. Алексей Соловьев (Silenser) 29.07.16 16:18
(11) marat_n, Автор упомянул про вариант с бесплатным SQL express.
14. Антон Андреев (tohandr) 29.07.16 22:00
(12) Silenser,
Хранение файлов в одной папке - это недостаток FileStream. Где-то на msdn встречал цифру в 300 000 файлов, после которой начинаются подтормаживания. Собственно Microsoft и дает несколько рекомендаций при использовании томов хранилища FileStream, чтоб минимизировать эти подтормаживания https://msdn.microsoft.com/ru-ru/library/cc645923.aspx

Отключите короткие имена файлов на компьютерах с FILESTREAM. Короткие имена файлов создаются гораздо дольше. Отключить короткие имена файлов можно с помощью программы Windows fsutil.
Регулярно выполняйте дефрагментацию на компьютерах с FILESTREAM.
Используйте файловую систему NTFS с кластерами по 64 килобайт. На сжатых томах должна быть файловая система NTFS с кластерами по 4 килобайта.
Отключите индексирование на томах FILESTREAM и установите параметр disablelastaccess. Для этого воспользуйтесь программой fsutil Windows.
Отключите антивирусное сканирование на томах FILESTREAM, если оно не является необходимым. Если антивирусное сканирование необходимо, не настраивайте политики автоматического удаления зараженных файлов.


Так же не совсем понятно из описания технологии Filestream, насколько система будет устойчива при одновременном доступе к одному и тому же файлу. Не будет ли каких-либо подводных камней.

К сожалению, полнофункциональное нагрузочное тестирование провести пока не удалось.
В описании сказано, что возможны две одновременных транзакции на чтение https://msdn.microsoft.com/ru-ru/library/gg471497(v=sql.110).aspx#dual
15. Антон Андреев (tohandr) 29.07.16 22:06
(11) marat_n,
1) Между 1С и MS хитрое соглашение, но на внешние базы MS SQL оно, на мой взгляд, не распространяется. В точки зрения MS с базой взаимодействует один пользователь.
2) Возможно создать нужные таблицы непосредственно в базе 1С. Могу ошибаться, но вроде непосредственно работа с таблицами 1С считается нарушением лиц. соглашения 1С, создание отдельных таблиц и работа с ними - нет. На работе это никак не сказывается.
3) Как уже говорили, возможен бесплатный SQL Express.
16. Dim Dragonim (Dragonim) 03.08.16 08:16
(9) tohandr,

Не вижу ни какого плюса кроме транзакционности, всё остальное минусы.
Ускоренный доступ к фалам хранимым в MS SQL обеспечивается за счет оперативной памяти, т.е. вы размениваете скорость на память, а сам файл начинает занимать больше места, т.к. появляются накладные расходы MS SQL.
О бекапах с использованием MS SQL как плюсе говорить не стоит, т.к. существует множество программ которые отлично бэкапят файловые системы различными способами включая инкриментальные и дифиринциальные методы с историей на годы.
По поводу "знать кто удалил" вопрос сложный. Если удалил через 1С то есть журнал транзакций, если удалите другим способом, не через 1С, то знание кто и когда это сделал у вас не появятся, если вы не позаботились отдельно.

В общем решение может подходить если вы точно знаете почему делаете именно так, а не иначе, и какие плюшки получите в итоге. Тесты тут обязательны, как доказательство жизнеспособности.
17. aspirator 23 (aspirator23) 06.08.16 14:33
(6) Полезная статья, давно прицеливался к Filestream. Есть проект, в котором хотелось бы проверить этот способ. Хотя в комментарии и сказано, но можно провести тестовое испытание записи/чтения непосредственно в файл на диске и используя Filestream?
Хранить нужно чаще большие файлы (0,1-2ГБ), поэтому хотелось представлять насколько быстро они записываются/читаются этими двумя способами.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа