درس سیزدهم – بررسی FileStream در SQL Server

در نرم‌افزار SQL قابلیتی طراحی شده که بتوانید فایل‌هایی با حجم بالا را درآن ذخیره کنید. همیشه این طور نیست که فقط داده‎های متنی در دیتابیس ذخیره شوند، بلکه داده‎هایی مانند: عکس، موسیقی، فیلم نیز می‌‎توانند در SQL ذخیره شوند، اما برای این کار باید از قابلیت FileStream در SQL استفاده کنید؛ در نسخه‎های قدیمی SQL افراد برای ذخیره‌ی عکس، موسیقی و فیلم از داده‎هایی از نوع BLOB استفاده می‌کردند که این داده‌ها تا حداکثر ۲ گیگابایت را پشتیبانی می‌کرد و بیشتر از این عدد، دیگر نمی‌توانستند ذخیره کنند و یکی از مهم‌ترین مشکلات این نوع داده‌ها این بود که عملکرد سیستم را به شدت کاهش می‌داد و باعث کندی آن می‌شد، اما با استفاده از FileStream دیگر نگران مقدار فضا و کندی سرعت نرم‌افزار نخواهیم بود.

برای اینکه از FILESTREAM استفاده کنیم باید یک گروه برای آن ایجاد کنیم تا اطلاعات در گروه جدید قرار بگیرد و دیگر با گروه اصلی یا همان Primary کاری نداریم؛ برای استفاده از این قابلیت در جداول نیاز به یک رکورد با نوع varbinary داریم.

در شکل ۹۳-۳ نوع داده‌های قدیمی برای ذخیره عکس را مشاهده می‎کنید که از یک دیتابیس استفاده می‌کردند، اما در عکس ۹۴-۳ این موضوع از دیتابیس اصلی جدا شده است.

شکل ۹۳-۳  بررسی FILESTREAM

شکل ۹۴-۳ بررسی FILESTREAM

برای فعال‌سازی قابلیت FILESTREAM باید به‌مانند شکل ۹۵-۳ سرویسSQL Server 2022 Configuration Manager را اجرا کنید.

شکل ۹۵-۳ اجرای سرویس Configuration

در شکل ۹۶-۳ از سمت چپ بر روی SQL Server Services کلیک کنید و در صفحه­ ی بازشده بر روی SQL Server کلیک راست کنید و گزینه‌ی Properties را انتخاب کنید.

شکل ۹۶-۳ بررسی سرویس

در شکل ۹۷-۳ وارد تب FILESTREAM شوید و تیک هر سه گزینه را انتخاب کنید؛ گزینه‌ی اول برای دسترسی از طریق دستورات T-SQL است؛ گزینه‌ی دوم برای خواندن و نوشتن از ورودی و خروجی پرونده‌ها است و برای این کار باید یک نام برای به اشتراک‌گذاری آن وارد کنید و گزینه‌ی آخر، اجازه­ی دسترسی از راه دور به FILESTREAM را می‌دهد.

شکل ۹۷-۳ بررسی FileStream

برای اینکه FileStream را در SQL فعال کنیم باید تغییری در تنظیمات آن انجام دهیم؛ برای این کار وارد منوی فایل به‌مانند شکل ۹۸-۳ شوید و از قسمت New بر روی Query کلیک کنید.

شکل ۹۸-۳ بررسی FileStream

دستور زیر را اجرا کنید:

USE master
  Go
  EXEC sp_configure
  Go

این دستور تنظیمات کلی سرور SQL را به شما نشان می‌دهد که مربوط به قسمت Advanced است؛ در این قسمت که در شکل ۹۹-۳ مشخص شده است باید به قسمت File Stream Access Level مراجعه کنید و در قسمت ستون config_value عدد ۲ قرار دهید؛ با این کار دسترسی کامل به سرویس FILESTREAM فعال خواهد شد.

شکل ۹۹-۳ بررسی FileStream

 برای قراردادن عدد ۲ باید دستور زیر را وارد کنید.

EXEC sp_configure filestream_access_level, 2
  GO
  RECONFIGURE WITH OVERRIDE
  GO

در دستور بالا، قسمت config_value مربوط به filestream_access_level به عدد ۲ تغییر می‌کند که در شکل ۱۰۰-۳ این موضوع را مشاهده می‌کنید.

شکل ۱۰۰-۳ بررسی FileStream

برای اینکه متوجه شویم که کار به ‌درستی انجام شده است یا نه باید به‌مانند شکل ۱۰۱-۳ بر روی سرور SQL کلیک راست کنید و وارد قسمت Properties شوید.

شکل ۱۰۱ -۳ بررسی FileStream

در شکل ۱۰۲-۳ از سمت چپ وارد قسمت Advanced شوید و قسمت FILESTREAM Access Level را مشاهده کنید که بر روی FULL access enabled قرار گرفته است، البته شما می‎توانستید دستورات قبلی را وارد نکنید و مستقیم در این قسمت، این گزینه را انتخاب کنید.

شکل ۱۰۲-۳ بررسی FileStream

بعد از اتمام کار، حتماً باید سرور را Restart کنید؛ برای این کار باید به‌مانند شکل ۱۰۳-۳ بر روی سرور SQL کلیک راست کنید و گزینه‌ی Restart را انتخاب کنید تا تنظیمات به‌ درستی اعمال شود.

شکل ۱۰۳-۳ بررسی FileStream

نکاتی که قبل از استفاده از FILEDTREAM باید بدانیم شامل:

• می‌توانیم از عباراتSELECT ،INSERT ،UPDATE و DELETE مشابه­ی پرس‌وجو استاندارد پایگاه‌داده در FILESTREAM استفاده کنیم.
• اگر اندازه­ی شی یا Object بیشتر از ۱ مگابایت باشد باید از FILESTREAM استفاده کنیم.
• هر سطر باید یک ID ردیف منحصربه‌فرد داشته باشد تا از این قابلیت استفاده کند و نباید حاوی مقادیر NULL باشد.
• نمی‌توانیم داده‌های FILESTREAM را رمزگذاری کنیم.

۳-۱-۷-۱ ایجاد دیتابیس برای استفاده از FILESTREAM

برای اینکه بتوانیم از قابلیت FILESTREAM استفاده کنیم باید یک دیتابیس جدید ایجاد و بر روی آن کار کنیم، البته می‌توانیم از دیتابیس‌های قدیمی خود استفاده کنیم؛ به‌مانند شکل ۱۰۴-۳ بر روی Databases کلیک راست کنید و گزنیه‌ی New Databases را انتخاب کنید.

شکل ۱۰۴-۳ ایجاد FileStream

به‌مانند شکل ۱۰۵-۳، یک نام برای دیتابیس خود وارد کنید که در اینجا، FILESTREAM-DB وارد شده است، بعد از این کار بر روی ADD کیک کنید تا دیتابیس مورد نظر ایجاد شود.

شکل ۱۰۵-۳ ایجاد FileStream

بعد از ایجاد دیتابیس، به‌مانند شکل ۱۰۶-۳ بر روی آن کلیک راست کنید و گزینه‌ی Properties را انتخاب کنید.

شکل ۱۰۶-۳ ایجاد FileStream

در شکل ۱۰۷-۳ باید وارد FileGroup شوید و در قسمت FILESTREAM بر روی Add کلیک کنید و نام گروه مورد نظر خود را برای تخصیص به FILESTREAM وارد کنید.

شکل ۱۰۷-۳ ایجاد FileStream

در ادامه­ی کار به‌مانند شکل ۱۰۸-۳ وارد قسمت Files شوید و بر روی Add کلیک کنید؛ در ستونLogical Name یک نام به‌دلخواه وارد کنید؛ در ستون File Type نوع فایل را FILESTREM Data در نظر بگیرید و Filegroup را نیز انتخاب کنید، البته به‌صورت اتوماتیک خودش انتخاب خواهد شد؛ در قسمت Path نیز باید مسیری را انتخاب کنید که از ظرفیت خوبی برخوردار باشد؛ در آخر بر روی OK کلیک کنید تا تنظیمات ذخیره شود.

شکل ۱۰۸-۳ ایجاد FileStream

بعد از انجام تنظیمات، اگر به‌مانند شکل ۱۰۹-۳ وارد آدرسی مربوط به Stream شوید، مشاهده خواهید کرد که فایل STREM در این مسیر ایجاد شده است.

شکل ۱۰۹-۳ ایجاد FileStream

۳-۱-۷-۲ ایجاد جدول در دیتابیس FILESTREAM

برای اینکه یک جدول با داده‌های FILESTREAM داشته باشیم باید به‌صورت زیر عمل کنیم؛ همان‌طور که گفتیم برای استفاده از FILESTREAM باید داده از نوع VARBINARY باشد که در دستور زیر مشخص شده است و یکی دیگر از شرط‌های FILESTREAM این است که حتماً باید یک کلید منحصربه‌فرد داشته باشد و حتماً نیز باید NOT NULL باشد؛ در دستور زیر سه فیلد ایجاد شده است که در Fileid نوع داده، UNIQUIDENTIFIER و NOT NULL است؛ داده‌ی FileName نیز از نوع معمولی VARCHAR با حداکثر کاراکتر ۲۵ است و FILE نیز از نوع VARBINARY است.

Use [FILESTREAM-DB]
Go
CREATE TABLE [FileStreamTable_1] (
[FileId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[FileName] VARCHAR (۲۵),
[File] VARBINARY (MAX) FILESTREAM);
GO

بعد از اجرای دستور در مسیر مشخص­شده که در شکل ۱۱۰-۳ مشخص شده است، یک پوشه با مقدار GUID ایجاد شده است.

شکل ۱۱۰-۳ بررسی فایل FileStream

اگر وارد این پوشه در شکل ۱۱۱-۳ شوید، یک‌ پوشه‌ی دیگر با GUID جدید مشاهده می‌‎کنید که مربوط به ستون FILESTREAM در جدول جدید است؛ این موضوع را در شکل ۶۲ مشاهده می‌کنید.

شکل ۱۱۱-۳ بررسی فایل FileStream

برای اینکه بیشتر با FILESTREAM کار کنیم، می‌خواهیم یک فایل موسیقی را درون دیتابیس مورد نظر ذخیره کنیم؛ اولین کاری که باید انجام دهید این است که فایل موسیقی را در مسیر C:\Music ذخیره کنیم،

بعد از آن باید از دستور زیر استفاده کنید؛، در این دستور، یک جدول جدید با نام FileStreamTable_2 ایجاد می‌شود و در ادامه با دستور DECLARE، متغیر FILE با نوع varbinary صدا زنده می‌شود، فایل MP3 در آن قرار می‌گیرد.

Use [FILESTREAM-DB]
Go
CREATE TABLE [FileStreamTable_2] (
[FileId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[FileName] VARCHAR (۲۵),
[File] VARBINARY (MAX) FILESTREAM);
GO
DECLARE @File varbinary(MAX); 
SELECT 
@File = CAST( 
bulkcolumn as varbinary(max) 
) 
FROM 
OPENROWSET(BULK 'c:\music\a.mp3', SINGLE_BLOB) as MyData;
INSERT INTO FileStreamTable_2 
VALUES 
( 
NEWID(), 
'Sample Music', 
@File
)

اگر به شکل ۱۱۲-۳ توجه کنید، یک فایل جدید به حجم تقریبی ۷ مگابایت که همان فایل MP3است در مسیر مورد نظر ایجاد شده است.

شکل ۱۱۲-۳ بررسی فایل FileStream

توجه داشته باشید، اگر فایل موسیقی در شکل ۱۱۲-۳ را در نرم‌افزار مورد نظر قرار دهید به ‌راحتی اجرا خواهد شد؛ این موضوع را در شکل ۱۱۳-۳ مشاهده می‌کنید.

شکل ۱۱۳-۳ بررسی فایل FileStream

اگر وارد SQL شوید و بر روی جدول مورد نظر کلیک راست کنید و گزینه‌ی Select Top 1000 Rows را انتخاب کنید، خروجی جدول مورد نظر را نشان می‌دهد که در شکل ۱۱۴-۳ مشخص شده است.

شکل ۱۱۴-۳ بررسی فایل FileStream