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

0. Антон Андреев (tohandr) 76 22.07.16 21:26 Сейчас в теме
Пример реализации хранения файлов в базе MS SQL Server с использованием различных технологий.
Подробно рассмотрен потоковый доступ к файлам с использованием технологии FileStream.


Перейти к публикации

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

ради чего все эти пляски?
Dragonim; +1 Ответить
23. Peter Filyk (pfilyk) 29.11.17 10:54 Сейчас в теме
(1) например у вас есть несколько баз, между которыми происходит обмен некоторыми документами, к документам необходимо прикрепить файлы (скан документа, чертеж и т.д.), чтобы в каждой базе не дублировать прикрепленные файлы, можно передавать только идентификатор файла в внешней базе. и в приемнике по запросу загружать из внешней базы.
в данной реализации есть один момент который автор упустил, com объект коннектится к mssql серверу используя аутентификацию операционной системы, под логином и паролем сервиса 1с, и это хорошо работает если сервер 1с и mssql сервер находятся на одной машине. если на разных то залогиниться используя аутентификацию ОС не получится. разве что сервера будут находится в домене, но этот вариант протестировать нет возможности.
2. Антон Андреев (tohandr) 76 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 можно вынести на отдельный диск. Также можно включать файловую группу в бэкап или не включать.

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


5. Максим Кузнецов (Makushimo) 152 29.07.16 05:57 Сейчас в теме
(2)
то есть по простому если сказать, то
Когда мы имеем базу учебных материалов с короткими видео и тяжелыми презентациями (более 1мб), к которой одновременно ломятся более 100 пользователей, тогда
- в случае хранения этих файлов средствами 1С (на диске или в базе) пользователи будут ждать по часу каждый видос
- в случае хранения этих файлов вашим способом пользователи мгновенно будут открывать смотреть читать писать эти файлы одинаково быстро не зависимо от количества пользователей (хоть 100 хоть 1000).

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

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

Если описать кратко - существенно повышается надежность хранения.
10. Максим Кузнецов (Makushimo) 152 29.07.16 12:21 Сейчас в теме
(9)
ну вот теперь все более менее понятно.
Спасибо.
16. Dim Dragonim (Dragonim) 85 03.08.16 08:16 Сейчас в теме
(9)

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

В общем решение может подходить если вы точно знаете почему делаете именно так, а не иначе, и какие плюшки получите в итоге. Тесты тут обязательны, как доказательство жизнеспособности.
17. aspirator 23 (aspirator23) 348 06.08.16 14:33 Сейчас в теме
(6) Полезная статья, давно прицеливался к Filestream. Есть проект, в котором хотелось бы проверить этот способ. Хотя в комментарии и сказано, но можно провести тестовое испытание записи/чтения непосредственно в файл на диске и используя Filestream?
Хранить нужно чаще большие файлы (0,1-2ГБ), поэтому хотелось представлять насколько быстро они записываются/читаются этими двумя способами.
3. в п (panvartan) 28.07.16 18:14 Сейчас в теме
Вы это делали от любви к искусству или задача какая была, проиллюстрируйте решение если можно.
4. Антон Андреев (tohandr) 76 28.07.16 19:17 Сейчас в теме
Задача была вполне конкретная - сделать файловое хранилище во внешней базе. Хранение файлов на диске или в базе 1С заказчика не устраивало.
Размер файлов от нескольких килобайт до десятков мегабайт.
7. Егор Иванов (Infactum) 221 29.07.16 11:09 Сейчас в теме
Явно не хватает сравнения плюсов/минусов с типовым функционалом БСП по хранению файлов в томах на диске.
Makushimo; +1 Ответить
11. marat_n q (marat_n) 108 29.07.16 12:54 Сейчас в теме
Есть небольшой нюанс в плане чистоты лицензий, если SQL сервер покупается вместе с 1С, то там, насколько я помню, использование сервера ограничено исключительно базами 1С.
13. Алексей Соловьев (Silenser) 470 29.07.16 16:18 Сейчас в теме
(11) marat_n, Автор упомянул про вариант с бесплатным SQL express.
15. Антон Андреев (tohandr) 76 29.07.16 22:06 Сейчас в теме
(11) marat_n,
1) Между 1С и MS хитрое соглашение, но на внешние базы MS SQL оно, на мой взгляд, не распространяется. В точки зрения MS с базой взаимодействует один пользователь.
2) Возможно создать нужные таблицы непосредственно в базе 1С. Могу ошибаться, но вроде непосредственно работа с таблицами 1С считается нарушением лиц. соглашения 1С, создание отдельных таблиц и работа с ними - нет. На работе это никак не сказывается.
3) Как уже говорили, возможен бесплатный SQL Express.
12. Алексей Соловьев (Silenser) 470 29.07.16 16:14 Сейчас в теме
Делал и выкладывал тут в свое время аналогичную разработку, когда принималось решение о типе хранения данных, наталкивался на опыт людей о том, что при большом количестве файлов в каталоге файловой системы, файловая система начинает подтормаживать. Людьми делалось предположение, что это вызвано тем, что при большом количестве файлов системе нужно большее время на поиск адреса размещения файла на физическом диске, т.к. увеличивается размер таблицы FAT. Сам не проверял. Но в итоге сделал таблицу с varbinary(max).
Так же не совсем понятно из описания технологии Filestream, насколько система будет устойчива при одновременном доступе к одному и тому же файлу. Не будет ли каких-либо подводных камней.
14. Антон Андреев (tohandr) 76 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
18. Алексей Ру (rubezh) 08.06.17 11:21 Сейчас в теме
Библиотеку зарегистрировал, но при выполнении Поместить файл ошибка:

{Обработка.РаботаСВнешнимХранилищемФайлов.МодульОбъекта(116)}: Ошибка при вызове конструктора (COMОбъект)
ФайлПоток = Новый COMОбъект("SqlStreamAccess.SqlFile");
по причине:
-2147024894(0x80070002): Не удается найти указанный файл.

что не так?
19. Алексей Ру (rubezh) 08.06.17 12:16 Сейчас в теме
(18) решено регистрацией с параметром /codebase
20. Алексей Ру (rubezh) 08.06.17 17:58 Сейчас в теме
При удалении из базы файла, который хранился не внутри, а в каталоге FileStream - из таблиц базы соответствующие строки удаляются, но файлы в каталоге так и остаются! Это так и задумывалось? И как сделать чтобы они всё таки удалялись
21. Наталия Кузнецова (Natgrey) 09.06.17 14:46 Сейчас в теме
(20) файлы удалятся после усечения журнала транзакций
22. Эдуард Тышковский (etyshkovskiy) 28.11.17 11:51 Сейчас в теме
Здравствуйте. А можно получить код com объекта?
Оставьте свое сообщение