بخش دوم — مدیریت Database

© Peter A. Carter 2019 — P. A. Carter, Pro SQL Server 2019 Administration — doi.org/10.1007/978-1-4842-5089-1_6

فصل 6 — پیکربندی Database

در یک Database، Data در یک یا چند Data File ذخیره می‌شود و این Fileها در Containerهای منطقی به نام Filegroup گروه‌بندی می‌شوند. هر Database حداقل یک Log File نیز دارد. Log Fileها خارج از Filegroup قرار می‌گیرند و قوانین Data File را دنبال نمی‌کنند. این فصل ابتدا Strategyهای Filegroup را بررسی می‌کند و سپس Maintenance مربوط به Data File و Log File را توضیح می‌دهد.

ذخیره‌سازی Data

پیش از انتخاب Strategy مناسب Filegroup باید شیوه ذخیره Data در SQL Server را شناخت. Figure 6-1 سلسله‌مراتب Storage داخل Database را نشان می‌دهد.

Figure 6-1 — نحوه ذخیره Data در SQL Server.

هر Database حداقل یک Filegroup دارد که حداقل یک File در آن قرار دارد. نخستین File، Primary File است و به‌طور پیش‌فرض پسوند .mdf دارد. این File هم می‌تواند Data نگهداری کند و هم Metadata لازم برای Startup Database و Pointer به سایر Fileها را ذخیره می‌کند. Filegroup حاوی Primary File، Primary Filegroup نام دارد.

Fileهای اضافی Secondary File هستند و معمولاً پسوند .ndf دارند. آن‌ها را می‌توان داخل Primary Filegroup یا Secondary Filegroup قرار داد. Secondary File و Secondary Filegroup اختیاری‌اند، اما برای DBA بسیار مفید هستند.

Filegroupها

Table و Index روی Filegroup ذخیره می‌شوند، نه روی File مشخص. اگر Filegroup چند File داشته باشد، شما تعیین نمی‌کنید Object دقیقاً روی کدام File برود. SQL Server Allocation را به روش Round-robin و با Proportional Fill انجام می‌دهد، بنابراین Object ممکن است روی همه Fileهای Filegroup پخش شود.

Listing 6-1 Database‌ای با یک Filegroup و سه File می‌سازد، Table را ایجاد و Populate می‌کند و با %%physloc%% محل فیزیکی Rowها را پیدا می‌کند تا تعداد Row در هر File شمارش شود.

Listing 6-1 — SQL Server Round-Robin Allocation، بخش اول

USE Master
GO
--Create a database with three files in the primary filegroup.
CREATE DATABASE [Chapter6]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Chapter6', FILENAME = N'F:\MSSQL\MSSQL15.PROSQLADMIN\MSSQL\DATA\Chapter6.mdf'),
( NAME = N'Chapter6_File2', FILENAME = N'F:\MSSQL\MSSQL15.PROSQLADMIN\MSSQL\DATA\Chapter6_File2.ndf'),
( NAME = N'Chapter6_File3', FILENAME = N'F:\MSSQL\MSSQL15.PROSQLADMIN\MSSQL\DATA\Chapter6_File3.ndf')
 LOG ON
( NAME = N'Chapter6_log', FILENAME = N'E:\MSSQL\MSSQL15.PROSQLADMIN\MSSQL\DATA\Chapter6_log.ldf');
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
    ALTER DATABASE [Chapter6] MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO
USE Chapter6
GO
--Create a table in the new database. The table contains a wide, fixed-length column
--to increase the number of allocations.

ادامه Listing 6-1

CREATE TABLE dbo.RoundRobinTable
(
    ID       INT     IDENTITY    PRIMARY KEY,
    DummyTxt NCHAR(1000),
);
GO
DECLARE @Numbers TABLE(Number INT)
;WITH CTE(Number) AS
(
    SELECT 1 Number
    UNION ALL
    SELECT Number +1 FROM CTE WHERE Number <= 99
)
INSERT INTO @Numbers SELECT * FROM CTE;
INSERT INTO dbo.RoundRobinTable
SELECT 'DummyText' FROM @Numbers a CROSS JOIN @Numbers b;
--Select all the data from the table, plus physical location.

پایان Listing 6-1

SELECT b.file_id, COUNT(*) AS [RowCount]
FROM
(
    SELECT ID, DummyTxt, a.file_id
    FROM dbo.RoundRobinTable
    CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) a
) b
GROUP BY b.file_id;

Figure 6-2 نشان می‌دهد Rowها تقریباً برابر بین سه File پخش شده‌اند. اگر Fileها Size متفاوت داشته باشند، File با Free Space بیشتر به‌دلیل الگوریتم Proportional Fill سهم بیشتری از Allocation می‌گیرد تا Data در Fileها متوازن‌تر توزیع شود.

Figure 6-2 — توزیع یکنواخت Rowها.

Data و Index استاندارد در Pageهای 8KB ذخیره می‌شوند. هر Page یک Header برابر 96 Byte و 8096 Byte برای Data دارد. هشت Page پیوسته یک Extent می‌سازند و Extent کوچک‌ترین واحدی است که SQL Server از Disk می‌خواند.

FILESTREAM Filegroup

FILESTREAM امکان ذخیره Binary Data به‌شکل Unstructured در File System را می‌دهد و در عین حال Transactional Consistency میان این Data و Structured Metadata داخل Database حفظ می‌شود. این فناوری محدودیت 2GB برای Object منفرد را دور می‌زند و برای Binary Objectهای بزرگ معمولاً Performance بهتری از ذخیره مستقیم داخل Database دارد؛ برای Fileهای بالاتر از حدود 1MB، Read Performance می‌تواند بهتر باشد.

Objectهای FILESTREAM از Windows Cache استفاده می‌کنند، نه SQL Server Buffer Cache. مزیت آن این است که File بزرگ Buffer Cache را پر نمی‌کند؛ اما در تنظیم Max Server Memory باید Memory اضافی موردنیاز Windows برای Binary Cache را در نظر گرفت.

FILESTREAM به Filegroup جداگانه نیاز دارد. این Filegroup به‌جای File معمولی به Folderهای Operating System اشاره می‌کند که Container نام دارند. پیش از ایجاد FILESTREAM Filegroup، FILESTREAM باید در Instance فعال باشد؛ این کار در Setup یا Instance Properties انجام می‌شود.

در GUI می‌توان از تب Filegroups در Database Properties با Add Filegroup یک FILESTREAM Filegroup به Chapter6 اضافه کرد و سپس در تب Files، Container جدیدی با File Type برابر FILESTREAM Data تعریف و آن را به Filegroup موردنظر متصل کرد.

Figure 6-3 — تب Filegroups.

همین کار با T-SQL نیز قابل انجام است. Listing 6-2 Filegroup مخصوص FILESTREAM ایجاد و سپس Container را اضافه می‌کند.

Listing 6-2 — افزودن FILESTREAM Filegroup

ALTER DATABASE [Chapter6] ADD FILEGROUP [Chapter6_FS_FG] CONTAINS FILESTREAM;
GO
ALTER DATABASE [Chapter6] ADD FILE
( NAME = N'Chapter6_FA_File1',
  FILENAME = N'F:\MSSQL\MSSQL15.PROSQLADMIN\MSSQL\DATA\Chapter6_FA_File1' )
TO FILEGROUP [Chapter6_FS_FG];
GO

Figure 6-4 — تب Files.

برای مشاهده Folder Structure مربوط به FILESTREAM ابتدا Table و Data می‌سازیم. Listing 6-3 Tableای با Unique Identifier الزامی برای FILESTREAM، یک Description متنی و ستون VARBINARY(MAX) FILESTREAM می‌سازد و یک Image را با OPENROWSET وارد می‌کند. File Path باید متناسب با سیستم شما تغییر کند.

Listing 6-3 — ساخت Table دارای FILESTREAM Data

USE Chapter6
GO
CREATE TABLE dbo.FilestreamExample
(
    ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
    PictureDescription NVARCHAR(500),
    Picture VARBINARY(MAX) FILESTREAM
);
GO
INSERT INTO FilestreamExample
SELECT NEWID(), 'Figure 6-1. Diagram showing the SQL Server storage hierachy.', *
FROM OPENROWSET(BULK N'c:\Figure_6-1.jpg', SINGLE_BLOB) AS import;

در File System، Container شامل Folderی با نام GUID برای Table و Folder GUID دیگری برای ستون FILESTREAM است. File واقعی Binary داخل آن قرار می‌گیرد و نام File بر اساس Log Sequence Number زمان ایجاد است. تغییر Extension و بازکردن مستقیم File از نظر فنی ممکن است، اما توصیه نمی‌شود زیرا می‌تواند اثر نامطلوب روی SQL Server داشته باشد. در Root Container فایل filestream.hdr برای Metadata و Folder برابر $FSLog برای ساختار معادل Transaction Log FILESTREAM وجود دارد. Figure 6-5 این Hierarchy را نشان می‌دهد.

FileTable

FileTable روی FILESTREAM ساخته شده و Data را در File System نگهداری می‌کند. برای استفاده از آن باید FILESTREAM با Streaming Access فعال باشد. تفاوت مهم FileTable این است که Nontransactional Access نیز می‌دهد؛ بنابراین Application موجود می‌تواند File را از طریق File System ببیند و حتی با Windows Explorer مانند File عادی باز یا تغییر دهد، بدون اینکه Application برای SQL Transaction بازنویسی شود.

SQL Server برای این کار به Windows Application اجازه می‌دهد بدون Transaction درخواست File Handle کند. به همین دلیل باید در Database مشخص شود چه سطحی از Nontransactional Access مجاز است؛ جزئیات و دستور آن در مقاله بعدی ادامه پیدا می‌کند.

Figure 6-5 — Hierarchy پوشه FILESTREAM.