Організація зберігання файлів у базі даних Microsoft SQL Server

Починаючи з версії 2012, у SQL Server став доступний новий формат зберігання файлових даних - файлові таблиці.

Файлові таблиці (FileTables) – особливий вид таблиць, який дозволяє окрім зберігання файлів практично необмеженого розміру (з цим легко справлявся і FileStream, що з'явився у версії 2008), також отримувати доступ до них із зовсім сторонніх додатків, які навіть не підключені до даної БД, за допомогою стандартних засобів файлової системи Windows.

Також підтримується зберігання папок.

Підготовка до роботи

Робота файлових таблиць полягає в технології FileStream. Тому перед тим, як створити файлову таблицю, необхідно включити підтримку FileStream для даного екземпляра SQL Server.

Включення проводиться у два етапи.

Увімкнення підтримки FileStream на рівні сервера у вікні його властивостей;
Увімкнення підтримки FileStream для служби SQL Server даного екземпляра та її подальше перезавантаження. Це найпростіше зробити за допомогою Sql Server Configuration Manager.

Після того як підтримка FileStream буде увімкнена, необхідно додати в БД файлову групу FileStream і створити в ній хоча б один файл.

Тепер можна розпочати безпосередньо роботу з файловими таблицями.

Створення файлових таблиць

Щоб створити файлову таблицю, достатньо скористатися найпростішою командою Transact-SQL.

Ключовим параметром у ній є параметр FILETABLE_DIRECTORY, який визначає ім'я умовної «папки» у файловій системі, через яку буде здійснюватися доступ до файлів, що зберігаються в таблиці. Чому цяпапка названа умовною буде пояснено далі.

У наведеній вище інструкції відсутні стовпці та їх типи даних. Справа в тому, що структура файлових таблиць суворо фіксована і створюється разом із таблицею автоматично. Ця структура вичерпно описана документації [1].

Робота з файловими таблицями засобами файлової системи

Ця умовна мережна папка і дозволяє працювати з вмістом файлових таблиць як із звичайними файлами. Підтримуються файли будь-яких форматів. Можна також додавати цілі папки.

При додаванні, зміні або видаленні файлів із цієї папки змінюються відповідні дані у самій файловій таблиці. Нижче наведено приклад подібного додавання файлу до файлової таблиці.

Що ж являє собою дана умовна папка насправді?

А насправді в папці, в якій зберігаються файли БД, створюється підпапка з ім'ям файлу даних FileStream. У ній, у свою чергу, створюються ще дві вкладені один в одного підпапки з іменами у форматі GUID.

Нарешті, у папці на нижньому рівні цієї структури є два файли.

Вони і збережений доданий в БД файл.

Робота з файловими таблицями засобамиTransact-SQL та мов програмування загального призначення

Список файлів та папок

Однією з найпоширеніших завдань є отримання списку файлів та папок, що зберігаються в БД.

Вирішити її можна за допомогою простого SQL запиту:

Якщо значення поля is_directory дорівнює 1, це папка (0 – звичайний файл).

Поля path_locator та parent_path_locator – відповідно первинний та зовнішній ключі для зберігання ієрархічних зв'язків.

Наприклад, поле parent_path_locator у файлу WeatherForecast.apk вказує напапку а якій він розташований (див. скріншот).

Це дозволяє, зокрема, легко візуалізувати структуру даних, що зберігаються.

Вивантаження файлів за допомогою клієнтської програми

Вміст файлів у файлових таблицях фізично зберігається у форматі varbinary(max) у полі file_stream.

Тому може бути легко отримано клієнтської програми з допомогою методу, який було розглянуто раніше зберігання файлів універсальним способом [2].

Завантаження, зміна та видалення файлів за допомогою клієнтської програми

Завантаження файлів у файлову таблицю також немає принципових відмінностей від універсального способу завантаження у полі формату varbinary(max) [2].

Існують деякі обмеження пов'язані з підтримкою зберігання папок та загальною структурою таблиці [3]. Зокрема:

Всі стовпці атрибутів файлу мають обмеження NO NULL. Якщо значення не задані явним чином, надаються відповідні значення за промовчанням;
Якщо інструкція INSERT встановлює name, path_locator, parent_path_locator або атрибути файлів, застосовуються системні обмеження;
Програма може отримати path_locator для файлу або каталогу за вказівкою шляху файлової системи для функції GetPathLocator (Transact-SQL);
Дозволяється оновлювати будь-які дані, що визначаються користувачем;
Оновлення даних FILESTREAM у стовпці file_stream не впливає на інші стовпці, включаючи позначки часу;
При видаленні рядка видаляється відповідний файл або каталог із файлової системи (детальніше пояснення див. вище);
Неможливо видалити рядок, якщо він відноситься до каталогу, який містить інші файли чи каталоги.

Резюме

Використання файлових таблиць надаєряд істотних переваг:

Розширені можливості роботи рахунок доступу до даних засобами файлової системи;
Зменшення розміру БД та збільшення швидкодії рахунок зберігання великих обсягів даних у зовнішніх файлах;
Можливість зберігання необмеженого обсягу даних, оскільки дані файлових таблиць (як і «звичайні» дані FileStream) не підпадають під обмеження 2ГБ типу даних varbinary(max).

Більш складне розгортання та супровід. Зокрема тому, що на файлові таблиці поширюються самі обмеження, що й на FileStream;
Більш складна взаємодія із клієнтськими програмами внаслідок технологічних обмежень;
Незважаючи на простоту і зручність використання файлових таблиць в значній е знижує надійність і відмовостійкість.

Застосування файлових таблиць, як і будь-якої іншої технології, має бути, перш за все, виправданим.

Незважаючи на всі свої переваги, це вузькоспеціалізоване рішення призначене, перш за все для організації зберігання файлів. Також не рекомендується використовувати їх для зберігання файлів малого розміру (менше 1 МБ)

Важливо, що для коректної роботи необхідно організувати достатній захист фізичного місця зберігання даних файлової таблиці від несанкціонованого доступу та передбачити додаткові заходи щодо резервного копіювання та відновлення. Якщо ж це з будь-яких причин неможливо, краще не ризикувати і по можливості вдатися до «звичайної» форми зберігання даних у varbinary(max) [2].