SQL Server Log Shipping規劃與實作考量與注意事項

 SQL Server Log Shipping規劃與實作考量與注意事項

1.目的是在異地維持一份hot standby DB，以利災難發生時可以快速讓DB上線接替

2.資料更新 0~45分鐘以上(依據資料量/網路傳輸速度/磁碟IO速度而定)

預設為每隔15分鐘執行以下作業

資料同步(資料的更新)是透過Job來執行備份Tlog、複製Tlog file到異地SQL主機、執行資料庫還原Tlog三個動作

如果檔案小則很快完成，如果檔案大則任一個作業執行都需要更多時間

Job執行間格可調整成最短每隔10秒執行一次

3.佈建與實作考量

(1)資料庫復原模式recovery model必須為[完整](Full)或[大量記錄](bulk-logged)

(2)建立一個交易紀錄備份用目錄並設定分享，如果只有兩台SQL Server通常建立在主要伺服器Primary Server上，此目錄也可以建立在第三台主機上。

此範例的目錄建立在主要伺服器上，D:\SQLLogShipBackup

並給予

主要伺服器Primary Server的SQL Agent啟動帳戶SQLservice有寫入與讀取權限

次要伺服器Secondary Server的SQL Agent啟動帳戶SQLservice有讀取權限

(3)如果名稱無法解析並且DNS無法手動新增record，可以改用本機host來做名稱解析

C:\Windows\System32\drivers\etc\host

將雙方需要解析的電腦名稱與IP對應新增到此檔

(4)建立一個放置要還原的交易紀錄檔目錄並設定分享，此目錄建立在次要伺服器secondary Server主機上。

此範例的目錄建立在次要伺服器上，D:\SQLLogShipRestore

並給予次要伺服器secondary Server的SQL Agent啟動帳戶SQLservice有寫入與讀取權限

(5)可以Job使用Proxy Account

 A.如果SQL Server與SQL Agent服務啟動帳號要維持比較安全的虛擬帳號(Virtual Account)的話，則Log Shipping的Job就只能改用Proxy Account

 B.Proxy Account所使用的[認證]Credential，可以是此本機Windows帳號，然後可以不用是Windows管理員(不用加入Administrators群組)，例如 建立一個名為SQLsrv本機windows帳號

 C.此Windows帳號必須設定為SQL Server管理員，也就是伺服器角色需勾選sysadmin

不然備份Job會出現以下錯誤

訊息

Microsoft (R) SQL Server Log Shipping Agent

[組件版本 = 16.0.0.0，檔案版本 = 16.0.1000.6]

Copyright (c) 2016 Microsoft. 著作權所有，並保留一切權利。

2025-10-23 23:38:02.07 ----- START OF TRANSACTION LOG BACKUP -----

2025-10-23 23:38:02.12 *** 錯誤: 無法擷取主要識別碼 'd076feaf-8bc0-4842-884e-8a0d3cb554a4' 的備份設定。(Microsoft.SqlServer.Management.LogShipping) ***

2025-10-23 23:38:02.12 *** 錯誤: 指定的登入不是伺服器 'PMIS-DB01' 的系統管理員 (sysadmin) 固定伺服器角色的成員。(Microsoft.SqlServer.Management.LogShipping) ***

2025-10-23 23:38:02.12 ----- END OF TRANSACTION LOG BACKUP   -----

 D.Log Shipping設定完成後，再去修改這三個JOB，將Step執行身分改為proxy。

備份Tlog在主要伺服器Primary Server(本地)

複製Tlog file到異地SQL主機、執行資料庫還原Tlog則在次要伺服器Secondary server(異地)

(6)建議用T-SQL包在一個GO裡面執行還原資料庫的動作，並且第一個tlog backup file一定要是Differential backup 後的第一個tlog bakcup(可以query msdb的備份紀錄找出順序)，不然整個還原最後全部重來，避免造成還原問題，然後查不到原因

如果restore第一個tlog backup弄錯，可能會出現以下錯誤

The log in the backup set is too recent to apply to the database

-- 可以用以下語法查詢備份紀錄，找出Differential backup 後的第一個tlog bakcup

SELECT 

   'RESTORE LOG ['+msdb.dbo.backupset.database_name+'] FROM  DISK = N'''+ msdb.dbo.backupmediafamily.physical_device_name + ''' WITH NORECOVERY' as [restoreSQL],

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 

   msdb.dbo.backupset.database_name, 

   msdb.dbo.backupset.backup_start_date, 

   msdb.dbo.backupset.backup_finish_date, 

   msdb.dbo.backupset.expiration_date, 

   CASE msdb..backupset.type 

      WHEN 'D' THEN 'Database' 

      WHEN 'L' THEN 'Log' 

      END AS backup_type, 

   msdb.dbo.backupset.backup_size, 

   msdb.dbo.backupmediafamily.logical_device_name, 

   msdb.dbo.backupmediafamily.physical_device_name, 

   msdb.dbo.backupset.name AS backupset_name, 

   msdb.dbo.backupset.description 

FROM 

   msdb.dbo.backupmediafamily 

   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 

WHERE 

   (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 

ORDER BY 

   msdb.dbo.backupset.database_name, 

   msdb.dbo.backupset.backup_finish_date;

建議用T-SQL執行，比較容易疑難排解

USE [master]

-- restore full backup

RESTORE DATABASE [DB01] FROM  DISK = N'D:\Temp\F\DB01_backup_2025_10_19.bak' WITH NORECOVERY

GO

-- restore differential backup

RESTORE DATABASE [DB01] FROM  DISK = N'D:\Temp\D\DB01_backup_2025_10_23.bak' WITH NORECOVERY

GO

-- restore tlog

RESTORE LOG [DB01] FROM  DISK = N'D:\Temp\T\DB01\DB01_backup_2025_10_23_01.trn' WITH NORECOVERY

RESTORE LOG [DB01] FROM  DISK = N'D:\Temp\T\DB01\DB01_backup_2025_10_23_02.trn' WITH NORECOVERY

RESTORE LOG [DB01] FROM  DISK = N'D:\Temp\T\DB01\DB01_backup_2025_10_23_03.trn' WITH NORECOVERY

GO