Начиная с 2008 версии в SQL сервере появилась возможность хранить данные (фото, музыку, видео и прочие документы)
Данная опция называется FILESTREAM и задается она следующим образом:
Обязательно в таблице должен быть задан столбец с указателями UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE !!
В итоге в файловой системе в специальной папке появятся файлы с названиями типа GUID которые будут связаны с этой таблицей
Использовать эту опцию будет целесообразно если средний размер данных в столбце VARBINARY(MAX) больше 1-2 Мб.
Подробнее можно прочитать в книге The Art of SQL Server FILESTREAM (Jacob Sebastian & Sven Aelterman).
Здесь можно найти скрипты для настроек, примеры кода для работы с FILESTREAM для ASP.NET, C#, Silverlight, SSIS, SSRS и
много полезных советов.
Однако опция FILESTREAM имеет ряд недостатков это и односторонняя целостность (если удалить файл со стороны файловой системы,
В 2012 версии эта опция была дополнена надстройкой FILETABLE которая позволяет обращаться к данным по сетевому пути и работать
Для того чтобы воспользоваться опцией FILETABLE необходимо сделать всего лишь 5 простых шагов!
1.Включить на уровне службы SQL Server опцию FILESTREAM
Enable FILESTREAM for file I/O streaming access – позволяет получить доступ к файлам со стороны файловой системы
Allow remote clients to have streaming access to FILESTREAM data – позволяет получить доступ к файлам удаленно,
2.Разрешить использование FILESTREAM на уровне экземпляра SQL сервера
Disabled – отключает FILESTREAM для экземпляра
Transact-SQL access enabled – разрешает доступ только со стороны T-SQL
Full access enabled – включает FILESTREAM для T-SQL и файловой системы
3.Создать файловую группу и файл для хранения данных FILESTREAM
Группа создается в отдельном разделе FILESTREAM
Включить в группу FILESTREAM файл. По сути это даже не файл, а всего лишь указатель на папку где будут физически храниться данные FILESTREAM в системе,
в поле Path задайте путь для хранения данных FILESTREAM, например, C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
4.Включить опцию FILESTREAM на уровне базы данных
Здесь необходимо задать две опции Filestream Directory Name = это подпапка которая будет создана в общей папке FILESTREAM для нашей БД
Выставить опцию Non-Transacted Access для доступа к файлам вне транзакций
Read-Only – разрешает не транзакционный доступ только для чтения файлов, создавать удалять файлы нельзя
Full – разрешает полный доступ для клиентских приложений со стороны файловой системы используя Windows API
5.Создать таблицу типа FILETABLE
Таблица FILETABLE имеет уже жестко определенную структуру поэтому необходимо указать лишь ключевое слово AS FILETABLE
И две опции FILETABLE_DIRECTORY —корневой каталог для всех файлов и каталогов, хранящихся в FileTable.
Если при создании не указано имя каталога, то в качестве него используется имя самой таблицы.
FILETABLE_COLLATE_FILENAME указывает имя параметров сортировки, применяемых к столбцу Name в таблице FileTable.
Если значение не указано или задано как database_default, столбец унаследует параметры сортировки текущей базы данных.
Теперь можно перейти к тестированию нашего механизма!
Для это можно зайти на сервер по сетевому пути или выбрав в SSMS опцию Explore FileTable Directory
Видно что на сервере появилась общая папка mssqlserver, которая была
указана в настройке FILESTREAM и подпапки MEDIA и FILES. Скопируем в папку FILES необходимые файлы…
Выполним SQL запрос к нашей файловой таблице, видим чтофайлы появились в таблице MEDIA_DATA
в столбце File_stream находится наше содержимое файлов, поэтому не стоит делать запрос ко всей таблице вместе с этим полем)
Настроить FILESTREAM также можно через SQL
--включение FILESTREAM для экземпляра SQL SERVER
--0 – FILESTREAM is disabled; 1 – FILESTREAM is enabled for T-SQL access;2 – FILESTREAM is enabled for T-SQL and Win32 streaming access.
EXEC sp_configure filestream_access_level, 2;
GO
RECONFIGURE
GO
--Проверить настройки FILESTREAM для экземпляра
-- 0 – FILESTREAM is disabled
-- 1 – FILESTREAM is enabled for T-SQL access
-- 2 – FILESTREAM is enabled for T-SQL and Win32 streaming access (local only)
-- 3 – FILESTREAM is enabled for T-SQL and Win32 streaming access (local and remote)
SELECT
SERVERPROPERTY('FilestreamEffectiveLevel') AS EffectiveValue,
SERVERPROPERTY('FilestreamConfiguredLevel') AS WindowsConfiguredValue,
SERVERPROPERTY('FilestreamShareName') AS FilestreamShare
-----------------------------------------------
---создание файловой группы FILESTREAM
ALTER DATABASE MEDIA
ADD FILEGROUP FILESTREAM_DATA
CONTAINS FILESTREAM;
GO
--добавление файла в группу FILESTREAM
ALTER DATABASE MEDIA
ADD FILE ( NAME = MEDIA_FILE,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA') TO FILEGROUP FILESTREAM_DATA;
------------------------------------------------
--включение не транзакционного доступа для таблиц FILETABLE в текущей БД
ALTER DATABASE MEDIA SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'MEDIA')
--проверить настройки доступа к данным FILESTREAM вне транзакций
SELECT non_transacted_access, non_transacted_access_desc, directory_name
FROM sys.database_filestream_options
WHERE database_id = DB_ID('MEDIA')
--создание таблицы типа FILETABLE
--http://eazybi.blogspot.ru/
CREATE TABLE IMAGES AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = 'Files',
FILETABLE_COLLATE_FILENAME = database_default
)
GO
|
+ несколько полезных запросов
--физический путь к контейнерам FILESTREAM
SELECT db_name() AS DatabaseName, physical_name AS FilestreamFolder
FROM sys.database_files WHERE type_desc = 'FILESTREAM'
--таблицы с опцией FILESTREAM
SELECT DISTINCT OBJECT_NAME(OBJECT_ID) AS TableName FROM sys.columns c WHERE c.is_filestream = 1
--связь между таблицами и физическими папками с названиями GUID
SELECT t.name AS TableName, d.name AS FileGroup, p.filestream_guid AS FolderName
FROM sys.tables t INNER JOIN sys.system_internals_partitions p ON p.object_id = t.object_id
INNER JOIN sys.data_spaces d ON d.data_space_id = t.filestream_data_space_id
WHERE p.filestream_filegroup_id > 0
--связь между таблицами и физическими папками для секционированных таблиц
SELECT t.Name AS TableName, sip.partition_number AS PartitionNumber,
sip.filestream_guid AS FilestreamFolder
FROM sys.system_internals_partitions sip
INNER JOIN sys.partitions p ON p.partition_id = sip.partition_id
AND sip.filestream_guid IS NOT NULL
INNER JOIN sys.tables t ON t.object_id = p.object_id
--получить абсолютный или относительный путь к файлу, таблице, сконвертировать
--путь во внутренний path_locator
select FileTableRootPath('Images') as [FileTableRootPath],
i.file_stream.GetFileNamespacePath(0) as [FileRelativePath],
i.file_stream.GetFileNamespacePath(1) as [FileAbsoutePath],
GetPathLocator(i.file_stream.GetFileNamespacePath(1)) [FilePathLocator],
[path_locator]
,path_locator.GetLevel() AS 'level'
,CAST(path_locator AS VARCHAR(4000)), name
,is_directory
from images i
--создание под папки
INSERT INTO Images ([name], is_directory) VALUES ('TEST_FOLDER', 1)
--------------------------------------------------------------------------------
--вставка данных в таблицу FILETABLE в указанную папку
GO
DECLARE @parent_path_locator HIERARCHYID
SELECT @parent_path_locator = path_locator FROM Images WHERE name = 'TEST_FOLDER'
AND path_locator.GetLevel() = 1 AND is_directory = 1
-- Create a new path_locator value that places the new file -- as a child of the parent directory
DECLARE @path_locator VARCHAR(675)
SET @path_locator = @parent_path_locator.ToString() +
CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING( CONVERT(BINARY(16), NEWID()), 1, 6))) + '.' +
CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING( CONVERT(BINARY(16), NEWID()), 7, 6))) + '.' +
CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING( CONVERT(BINARY(16), NEWID()), 13, 4))) + '/'
--вставка нового файла в указанную папку
INSERT INTO Images ([name], path_locator,file_stream)
SELECT 'MyMonitor2.jpg', @path_locator,
CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET( BULK 'C:\Shared\Images\Monitor.jpg', SINGLE_BLOB ) as x
--сборка мусора от FILESTREAM вручную
--http://eazybi.blogspot.ru/
EXEC sp_filestream_force_garbage_collection @dbname = N'MEDIA';
|
