Совет по переносу файлов из Blob?

Надо быть с этим очень аккуратным, т.к. в таком варианте любой пользователь может удалить файл физически, а в системе останется на него ссылка. Т.е система распределения прав доступа не Террасофт не будет распространяться на файловую систему. Теоретически скорость работы системы должна повыситься.
Есть другой вариант, но это при условии использования MS SQL 2088R2. Там есть тип поля FileStream (на форуме обсуждалось). Сам еще не делал, но уже давно пора.

Евгений почитал про FileStream и понял что это то что нужно. Возник вопрос в том возможно ли существующие файлы перевести в данный тип?

Да, конечно.
Самы простой способ - Вы переименовываете поле FileData в FileData_Old и создаете новое поле FileData но с уже нужным Вам типом varbinary(max) filestream, после чего обычным Update переносите данные из FileData_Old в FileData, после чего удаляете поле FileData_Old. Но тут есть подводлные камни,
напрмер, в таблице не должно быть Insted of.. триггеров (а у tbl_Files они есть). Наверно и всё.

"Евгений Либин" написал:подводлные камни,
напрмер, в таблице не должно быть Insted of.. триггеров (а у tbl_Files они есть). Наверно и всё.

Нет, вы их не сможете просто включить, эти триггера нужно переделать или на обычные.
Для Террасофт CRM ничего не поменяется.
А что значит выгрузить на локальную машину?

"Евгений Либин" написал:А что значит выгрузить на локальную машину?

BlobField.SaveToFile(FileName);

Да, конечно.
Еще раз повторюсь: для клиентского приложения ничено не меняется.

"Евгений Либин" написал:Нет, вы их не сможете просто включить, эти триггера нужно переделать или на обычные.

Прямого решения нет.
Один из вариантов - это переделать триггер на after delete и поудалять все констреинты на эту таблицу.
Другой вариант перенести логику триггера на клиента на событие OnBeforeDelete.

Подкину еще идею :wink:
Очень часто (а тем более при работе с почтовым клиентом) файлы дублируются, т.е. один и тот же файл может быть прикремплен к разным записям (разделам).
Можно еще оптимизировать кол-во хранимых записей, например удаление дубликатов и объедиенение ссылок.

Посмотрев триггеры таблицы tbl_Files. Их два: tr_tbl_Files_I и tr_tbl_Files_ID.
Первый раздает права на запись при вставки второй удаляет записи в таблицах файлы в объектах после чего удаляет саму запись. А что именно может некорректно работать при изменении способа хранения файлов?

tr_tbl_Files_ID

Точно:exclaim: Сама СУБД ограничивает данное действие.

"Евгений Либин" написал:Один из вариантов - это переделать триггер на after delete и поудалять все констреинты на эту таблицу.

Нет, сделайте по другому. В TSAdmin вы констреинты не удаляйте, а удалите их на сервере MS SQL. Тогда описательная часть в Террасофт CRM останется.

Да чет я после того как запостить понял это. Удалил вроде все ключи но система по прежнему ругается что не может удалить запись. Тестирую на демо версии 3.4 возможно есть еще в каких то таблицах связи кроме tbl_FileInXX ?

Что ругается, Террасофт или MS SQL?

Террасофт. Нашел недавний пост http://www.community.terrasoft.ua/blogs/7437 про принудительное удаление из связных таблиц и использовал запрос для поиска ключей.
Что то смущает меня немного удаления ограничений. Как то это может повлиять на функционал?

Я только что это всё проделалу себя и у меня всё работает.

"Евгений Либин" написал:Я только что это всё проделалу себя и у меня всё работает.

Отлично!

В таком варианте будет всё нормально работать. Это дань универсальности. В Oracle и FireBird есть триггер Before ... чего нет в MS SQL, а с точки зрения правильности постраения БД нужно обязательно указывать FK.
Ошибки могут появиться только кода вы будете добавлять новый раздел или просто связку с tbl_Files.
Вы правильно сказали что нужно будет просто убрать внешний ключ, но нужно не забыть модифицировать триггер на удаления связанных записей.

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

Этот сборщик мусора работает самопроизвольно. Он сам решает когда удалять информацию. Его явно вызывать ненадо, он работает по собственному алгоритму.

"Евгений Либин" написал:Вы правильно сказали что нужно будет просто убрать внешний ключ, но нужно не забыть модифицировать триггер на удаления связанных записей.

tr_tbl_Files_ID - получается удалить в данном триггере

delete from [dbo].[tbl_Files]
  where exists(
    select [ID] from [deleted] [d]
    where [d].[ID] = [dbo].[tbl_Files].[ID]
  )

и сделать его after delete правильно я понял?

Нет, это у Вас конструкция для vw_Files, а я гоаорю про триггер для tbl_Files, тот который Вы меняли.
Вот кусок этого триггера

  delete from [dbo].[tbl_FileInAccount]
  where exists(
    select [ID] from [deleted] [d]
    where [d].[ID] = [dbo].[tbl_FileInAccount].[FileID]
  )

т.е добавить такую конструкцию для нового раздела, например

  delete from [dbo].[tbl_FileInNewWS]
  where exists(
    select [ID] from [deleted] [d]
    where [d].[ID] = [dbo].[tbl_FileInAccount].[FileID]
  )

Все я понял спасибо. Менять его нужно будет при создании нового раздела. И еще может будет кому полезно сборщик мусора можно запустить вот так

CHECKPOINT
go
BACKUP LOG 
Имя базы данных 
TO DISK = 'C:\SQL\FSTB_log.bak';
go

Как я понял после бекапа лога происходит очистка.

Вот еще решение:
Раз в сутки по расписанию запускть:

DBCC SHRINKFILE('DataBaseName_log', 1)
CHECKPOINT

Более подробно можно почитат тут http://dalexs.homedns.org/blog/archive/2009/02/08/mssql-2008-filestream-dark-side.aspx

У меня почему то данная инструкция ничего не удалила. Возможно нужно настроит что либо в БД.

Здравствуйте Максим,
Предлагаю другой вариант

USE FSDB;
GO
EXEC sp_filestream_force_garbage_collection @dbname = N'FSDB';

Пример с http://msdn.microsoft.com/en-us/library/gg492195(v=sql.110).aspx
По поводу того что DBCC SHRINKFILE не отрабатывает корректно для filestream упоминается также в
http://stackoverflow.com/questions/2269610/sql-server-2008-unable-to-re…

"Яворский Алексей" написал:Здравствуйте Максим,
Предлагаю другой вариант

USE FSDB;
GO
EXEC sp_filestream_force_garbage_collection @dbname

Спасибо, Алексей.

Это то что нужно.

Вот пример из жизни.

Есть организация N, которая использует MS SQL 2008 R2 ExpressEdition.
Работа идет активно, работает порядка 30 человек. Так же они установили себе встроенный почтовый клиент. В один день, оказалось что размер базы достиг 10 Гб, что выходит за рамки использования бесплатной версии. Пришлось оперативно внедрять технологию FileStream. В результате, база уменьшилась до 1 Гб, а все файлы (и аттачи почты) таблицы tbl_Files были вынесены на диск (9 Гб).

Вот такая история. :smile:

Спасибо за помощь. В ближайшее время думаю внедрить данную технологию. Единственное нужно купить дополнительные диски так как я понял на сетевые шары нельзя разместить FileStream.

Hello, this is the script, I use to move data in the tbl_Files to filestreams :)

1. Make database backup
 
2. Create filestream files
ALTER DATABASE DATABASE_NAME ADD filegroup FileStreamGroup1 CONTAINS filestream 
ALTER DATABASE DATABASE_NAME ADD FILE (NAME = FileStreamFile1, FILENAME = 'f:\database\tcr_xxx_332_lt\FileStreamData') TO FILEGROUP FileStreamGroup1;
 
3. Make column copy
ALTER TABLE tbl_Files ADD FileDataBackup IMAGE;
UPDATE tbl_Files set FileDataBackup = FileData;
 
4. For all tbl_FileIn..  do this
 
DROP TRIGGER tr_tbl_FileInAccount_ID
ALTER TABLE dbo.tbl_FileInAccount DROP CONSTRAINT FFileInAccountFileID
ALTER TABLE dbo.tbl_FileInAccount ADD CONSTRAINT FFileInAccountFileID FOREIGN KEY ( FileID ) REFERENCES dbo.tbl_Files ( ID ) ON UPDATE NO ACTION ON DELETE CASCADE 
 
DROP TRIGGER tr_tbl_FileInContact_ID
ALTER TABLE dbo.tbl_FileInContact DROP CONSTRAINT FFileInContactFileID
ALTER TABLE dbo.tbl_FileInContact ADD CONSTRAINT FFileInContactFileID FOREIGN KEY ( FileID ) REFERENCES dbo.tbl_Files ( ID ) ON UPDATE NO ACTION ON DELETE CASCADE 
 
...
 
5. tbl_FileRight and tbl_Files instead of trigger
 
ALTER TABLE dbo.tbl_FilesRight DROP CONSTRAINT FFilesRightRecordID
ALTER TABLE dbo.tbl_FilesRight ADD CONSTRAINT FFilesRightRecordID FOREIGN KEY ( RecordID ) REFERENCES dbo.tbl_Files ( ID ) ON UPDATE NO ACTION ON DELETE CASCADE 
DROP TRIGGER tr_tbl_Files_ID
 
6. Change FileData column type to VARBINARY(MAX):
 
ALTER TABLE tbl_Files DROP COLUMN FileData;
ALTER TABLE tbl_Files ADD FileData VARBINARY(MAX) filestream;
 
 
7. Move data to updated column
UPDATE tbl_Files SET FileData = FileDataBackup
ALTER TABLE tbl_Files DROP COLUMN FileDataBackup
 
8. Drop vw_Files triggers
DROP TRIGGER tr_vw_Files_ID
DROP TRIGGER tr_vw_Files_IU
 
9. Recreate files view
ALTER VIEW [dbo].[vw_Files]
AS 
  SELECT [P].* 
  FROM [dbo].[tbl_Files] AS [P] with(nolock)
  WHERE EXISTS (
    SELECT * FROM [dbo].[tbl_FilesRight] AS [R] with(nolock)
    WHERE (([R].[RecordID] = [P].[ID]) 
    AND EXISTS(
      SELECT * FROM [dbo].[tbl_UserAdminUnit] AS [U] with(nolock)
      WHERE ([R].[AdminUnitID] = [U].[AdminUnitID]) 
      AND ([U].[UserName] = SYSTEM_USER))))
GO
 
10. Make additional triggers or modifications in TS client side to make correct access rights to the files

Hello Aurimas Lacitis,
Thank you for sharing experience

You are welcome. :smile:
Only after making these changes there might be a problem to add new workspace, that contains Files detail using TS Master window or PVC, because the service tbl_Files can not be saved correctly, so some workarounds are needed.
Terrasoft might do some changes in the standard configuration (including binaries), so that moving blobs to FileStreams would be easier to configure and maintain. :smile:

Да было бы здорово. Может внести это как идею?

Yes, it could be as idea for TS 3.4 and maybe for BPMOnline as well.