راهنمای لاگ شیپینگ(Log Shipping) سفارشی SQL Server افزایش دسترس پذیری پایگاه داده

راهنمای جامع راه‌اندازی لاگ شیپینگ(Log Shipping) سفارشی در SQL Server: افزایش دسترس‌پذیری پایگاه داده

مدیریت و نگهداری پایگاه‌های داده حیاتی در محیط‌های سازمانی همواره یکی از چالش‌های اصلی مدیران پایگاه داده (DBA) بوده است. برای اطمینان از دسترس‌پذیری بالا و بازیابی سریع در صورت بروز فاجعه، راهکارهای مختلفی مورد استفاده قرار می‌گیرد که یکی از مهم‌ترین آن‌ها لاگ شیپینگ (Log Shipping) است. لاگ شیپینگ استاندارد SQL Server ابزاری قدرتمند است، اما در برخی سناریوها ممکن است محدودیت‌هایی داشته باشد؛ به عنوان مثال، هنگام نیاز به سفارشی‌سازی بیشتر، کنترل دقیق‌تر بر فرآیندها یا ادغام با سیستم‌های نظارتی خاص.

در این مقاله، به بررسی رویکردی برای پیاده‌سازی لاگ شیپینگ سفارشی (Custom Log Shipping) در SQL Server می‌پردازیم. این روش به شما امکان می‌دهد تا با استفاده از T-SQL و اسکریپت‌نویسی، کنترل کاملی بر مراحل پشتیبان‌گیری، کپی و بازیابی لاگ‌های تراکنش داشته باشید و آن را با نیازهای دقیق سازمان خود تطبیق دهید.

لاگ شیپینگ چیست؟

لاگ شیپینگ یک راهکار پرکاربرد برای بازیابی فاجعه (Disaster Recovery) و پیاده‌سازی سرورهای Standby گرم است. این فرآیند شامل سه مرحله اصلی است:

1. **پشتیبان‌گیری (Backup):** در این مرحله، فایل‌های لاگ تراکنش از پایگاه داده اصلی (Primary Database) تهیه می‌شوند.
2. **کپی (Copy):** فایل‌های پشتیبان لاگ از سرور اصلی به یک یا چند سرور مقصد (Secondary Servers) کپی می‌شوند.
3. **بازیابی (Restore):** فایل‌های لاگ کپی شده بر روی پایگاه داده‌های Standby در سرورهای مقصد بازیابی می‌شوند. این پایگاه داده‌ها معمولاً در حالت NORECOVERY یا STANDBY قرار می‌گیرند تا بتوانند لاگ‌های جدید را بپذیرند.

این سه مرحله به طور متوالی و با زمان‌بندی منظم انجام می‌شوند تا پایگاه داده ثانویه تا حد امکان با پایگاه داده اصلی همگام بماند.

چرا لاگ شیپینگ سفارشی؟

با وجود قابلیت‌های لاگ شیپینگ داخلی SQL Server، دلایل مختلفی برای انتخاب رویکرد سفارشی وجود دارد:

• **انعطاف‌پذیری بیشتر:** کنترل دقیق بر زمان‌بندی، مکان فایل‌ها، و نحوه بازیابی.
• **مدیریت خطا:** امکان پیاده‌سازی منطق پیچیده‌تر برای مدیریت خطا و اعلان‌ها.
• **یکپارچه‌سازی:** ادغام آسان‌تر با سیستم‌های مانیتورینگ و ابزارهای اتوماسیون موجود.
• **سناریوهای خاص:** مانند نیاز به بازیابی در چند سرور، یا اعمال تغییرات خاص در حین بازیابی.

پیاده‌سازی لاگ شیپینگ سفارشی با T-SQL

برای پیاده‌سازی لاگ شیپینگ سفارشی، ما سه اسکریپت T-SQL مجزا ایجاد می‌کنیم: یکی برای پشتیبان‌گیری، یکی برای کپی، و یکی برای بازیابی. هر اسکریپت را می‌توان به صورت جداگانه زمان‌بندی کرد، معمولاً با استفاده از SQL Server Agent Jobs.

اسکریپت پشتیبان‌گیری لاگ (Log Backup Script)

این اسکریپت مسئول تهیه پشتیبان از لاگ تراکنش پایگاه داده اصلی است. باید به صورت منظم اجرا شود (مثلاً هر 15 دقیقه). مسیر ذخیره‌سازی پشتیبان‌ها باید قابل دسترسی برای سرور مقصد باشد (مثلاً یک مسیر شبکه مشترک).

-- Script: LogBackup.sql
-- Description: Backs up the transaction log for a specified database.

DECLARE @db_name SYSNAME = 'YourDatabaseName'; -- نام پایگاه داده خود را اینجا قرار دهید
DECLARE @backup_path NVARCHAR(500) = '\\YourPrimaryServer\LogBackups\'; -- مسیر شبکه برای ذخیره پشتیبان‌ها
DECLARE @backup_file_name NVARCHAR(500);
DECLARE @command NVARCHAR(1000);

SET @backup_file_name = @backup_path + @db_name + '_' + REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR, GETDATE(), 120), '-', ''), ':', ''), ' ', '_') + '.trn';

SET @command = 'BACKUP LOG [' + @db_name + '] TO DISK = N''' + @backup_file_name + ''' WITH NO_COMPRESSION, CHECKSUM';

EXEC sp_executesql @command;

-- Optional: Clean up old backup files on the primary server after a certain period.
-- You might want to implement a separate cleanup job.

این اسکریپت ابتدا نام پایگاه داده و مسیر ذخیره‌سازی فایل‌های پشتیبان را تعریف می‌کند. سپس یک نام فایل پشتیبان منحصر به فرد با استفاده از تاریخ و زمان فعلی ایجاد می‌کند و در نهایت دستور BACKUP LOG را برای تهیه پشتیبان لاگ تراکنش اجرا می‌کند. حتماً YourDatabaseName و \\YourPrimaryServer\LogBackups\ را با مقادیر واقعی خود جایگزین کنید.

اسکریپت کپی لاگ (Log Copy Script)

این اسکریپت فایل‌های پشتیبان لاگ را از مسیر شبکه مشترک به یک پوشه محلی در سرور مقصد کپی می‌کند. این اسکریپت نیز باید به صورت منظم اجرا شود، کمی بعد از اسکریپت پشتیبان‌گیری.

-- Script: LogCopy.sql
-- Description: Copies transaction log backup files from primary to secondary server.

DECLARE @source_path NVARCHAR(500) = '\\YourPrimaryServer\LogBackups\'; -- مسیر شبکه پشتیبان‌های اصلی
DECLARE @destination_path NVARCHAR(500) = 'D:\LogShipping\LogBackups\'; -- مسیر محلی برای ذخیره در سرور مقصد
DECLARE @command NVARCHAR(1000);

-- Ensure the destination directory exists
SET @command = 'IF NOT EXIST (' + QUOTENAME(@destination_path, '''') + ') MKDIR ' + QUOTENAME(@destination_path, '''');
EXEC xp_cmdshell @command, NO_OUTPUT;

-- Copy files using xcopy
SET @command = 'XCOPY ' + QUOTENAME(@source_path + '*.trn', '''') + ' ' + QUOTENAME(@destination_path, '''') + ' /Y /D';
EXEC xp_cmdshell @command;

-- Optional: Delete copied files from source after copy to prevent re-copying and manage space
-- Be very careful with this step! Only do it if you are sure files are successfully copied and restored.
-- SET @command = 'DEL ' + QUOTENAME(@source_path + '*.trn', '''');
-- EXEC xp_cmdshell @command;

این اسکریپت از xp_cmdshell برای اجرای دستورات سیستمی (مانند MKDIR برای ایجاد پوشه و XCOPY برای کپی فایل‌ها) استفاده می‌کند. مطمئن شوید که xp_cmdshell بر روی SQL Server فعال است و حساب سرویس SQL Server دارای مجوزهای لازم برای دسترسی به مسیرهای شبکه است. /Y برای جایگزینی فایل‌ها بدون درخواست تایید و /D برای کپی تنها فایل‌های جدید یا تغییر یافته استفاده می‌شود.

اسکریپت بازیابی لاگ (Log Restore Script)

این اسکریپت مهم‌ترین بخش لاگ شیپینگ سفارشی است. وظیفه آن بازیابی فایل‌های لاگ تراکنش کپی شده بر روی پایگاه داده Standby در سرور مقصد است. این اسکریپت باید به صورت منظم (مثلاً هر 15 دقیقه، بعد از اسکریپت کپی) اجرا شود.

-- Script: LogRestore.sql
-- Description: Restores transaction log backup files to the secondary database.

DECLARE @db_name SYSNAME = 'YourDatabaseName'; -- نام پایگاه داده ثانویه
DECLARE @backup_path NVARCHAR(500) = 'D:\LogShipping\LogBackups\'; -- مسیر محلی فایل‌های پشتیبان در سرور مقصد
DECLARE @file_name NVARCHAR(500);
DECLARE @command NVARCHAR(1000);
DECLARE @last_restored_file NVARCHAR(500);

-- Get the last restored file name (if any) to ensure sequential restore
SELECT TOP 1 @last_restored_file = restore_file
FROM msdb.dbo.restorehistory
WHERE destination_database_name = @db_name
ORDER BY restore_date DESC;

-- Loop through all .trn files in the backup path and restore them
DECLARE backup_files_cursor CURSOR FOR
SELECT name
FROM sys.dm_os_enumerate_file_directory(@backup_path, '*.trn')
ORDER BY name;

OPEN backup_files_cursor;
FETCH NEXT FROM backup_files_cursor INTO @file_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Only restore files that are newer than the last restored file
    IF @file_name > ISNULL(RIGHT(@last_restored_file, LEN(@file_name)), '')
    BEGIN
        SET @command = 'RESTORE LOG [' + @db_name + '] FROM DISK = N''' + @backup_path + @file_name + ''' WITH NORECOVERY, STATS = 10';
        PRINT 'Restoring: ' + @backup_path + @file_name;
        BEGIN TRY
            EXEC sp_executesql @command;
            -- Update last restored file after successful restore
            SET @last_restored_file = @backup_path + @file_name;
            -- Optional: Delete the file after successful restore to prevent re-restoring
            -- SET @command = 'DEL ' + QUOTENAME(@backup_path + @file_name, '''');
            -- EXEC xp_cmdshell @command, NO_OUTPUT;
        END TRY
        BEGIN CATCH
            PRINT 'Error restoring: ' + @backup_path + @file_name + ' - ' + ERROR_MESSAGE();
            -- Log the error and potentially stop the process if critical
        END CATCH
    END
    FETCH NEXT FROM backup_files_cursor INTO @file_name;
END

CLOSE backup_files_cursor;
DEALLOCATE backup_files_cursor;

-- Optional: Consider changing the database to STANDBY if read access is needed
-- ALTER DATABASE YourDatabaseName SET STANDBY = 'D:\LogShipping\StandbyFile.bak';

این اسکریپت تمامی فایل‌های .trn موجود در مسیر مشخص شده را شناسایی کرده و به ترتیب زمانی بر روی پایگاه داده ثانویه بازیابی می‌کند. بسیار مهم است که پایگاه داده مقصد در حالت NORECOVERY باشد تا بتواند لاگ‌های بعدی را بپذیرد. همچنین، این اسکریپت فایل‌های لاگی را که قبلاً بازیابی شده‌اند، نادیده می‌گیرد و فقط فایل‌های جدیدتر را بازیابی می‌کند. خطایابی مناسب در این بخش اهمیت زیادی دارد.

مانیتورینگ لاگ شیپینگ سفارشی

یکی از مزایای لاگ شیپینگ سفارشی، توانایی ادغام آسان آن با سیستم‌های مانیتورینگ موجود است. می‌توانید با استفاده از msdb.dbo.restorehistory برای بررسی زمان آخرین بازیابی، و یا با ذخیره‌سازی اطلاعات در یک جدول سفارشی، وضعیت لاگ شیپینگ را رصد کنید. همچنین می‌توانید اعلان‌هایی (Notifications) را برای زمانی که تاخیر بازیابی (Restore Lag) بیش از حد مجاز می‌شود، تنظیم کنید.

برای بررسی آخرین زمان بازیابی، می‌توانید از کوئری زیر استفاده کنید:

SELECT destination_database_name, MAX(restore_date) AS LastRestoreTime
FROM msdb.dbo.restorehistory
WHERE restore_type = 'L' -- 'L' for Log restores
GROUP BY destination_database_name;

این کوئری به شما کمک می‌کند تا زمان آخرین عملیات بازیابی لاگ برای هر پایگاه داده را مشاهده کنید و از صحت عملکرد لاگ شیپینگ اطمینان حاصل کنید.

نتیجه‌گیری

پیاده‌سازی لاگ شیپینگ سفارشی در SQL Server با استفاده از T-SQL، انعطاف‌پذیری و کنترل بی‌نظیری را برای مدیران پایگاه داده فراهم می‌کند. این رویکرد به شما امکان می‌دهد تا فرآیند حمل و نقل لاگ را کاملاً مطابق با نیازهای خاص سازمان خود سفارشی‌سازی کنید، از مدیریت خطاهای پیشرفته گرفته تا یکپارچه‌سازی با سیستم‌های مانیتورینگ موجود. با پیروی از مراحل و اسکریپت‌های ارائه شده در این مقاله، می‌توانید یک راهکار قدرتمند و قابل اعتماد برای دسترس‌پذیری بالا و بازیابی فاجعه پایگاه داده‌های حیاتی خود پیاده‌سازی کنید.