Log Shipping 運作方式是
這種做法就會很像 AOAG 的非同步，Log Shipping 更適合用來實作 DR
這裡會跟第16天在做 AOAG 的時候差不多，只差一點點，LOG SHIPPING 我會用到 4 台SQL Server，比 AOAG 還要多一台。
因為我懶得重用一個所以我會沿用第16天做的 instance，但是名字沒法改所以先定義好
SQLNODE1\PRIMARYREPLICA = 主要伺服器
SQLNODE3\ASYNCDR = 災難復原伺服器
SQLNODE2\SYNHA = 報表伺服器
DC01\MONITOR = 監控伺服器
CREATE DATABASE Logshipping;
GO
ALTER DATABASE Logshipping SET RECOVERY FULL;
GO
USE Logshipping;
GO
DECLARE @Numbers TABLE
(
Number INT
);
;WITH CTE(Number)
AS
(
SELECT 1 Number
UNION ALL
SELECT Number + 1
FROM CTE
WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE;
DECLARE @Names TABLE
(
FirstName VARCHAR(30),
LastName VARCHAR(30)
);
INSERT INTO @Names
VALUES ('Peter', 'Carter'),
('Michael', 'Smith'),
('Danielle', 'Mead'),
('Reuben', 'Roberts'),
('Iris', 'Jones'),
('Sylvia', 'Davies'),
('Finola', 'Wright'),
('Edward', 'James'),
('Marie', 'Andrews'),
('Jennifer', 'Abraham');
CREATE TABLE dbo.Customers
(
CustomerID INT NOT NULL IDENTITY PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
BillingAddressID INT NOT NULL,
DeliveryAddressID INT NOT NULL,
CreditLimit MONEY NOT NULL,
Balance MONEY NOT NULL
);
SELECT * INTO #Customers
FROM
(
SELECT
(SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName,
(SELECT TOP 1 LastName FROM @Names ORDER BY NEWID()) LastName,
(SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()) BillingAddressID,
(SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()) DeliveryAddressID,
(SELECT TOP 1 CAST(RAND() * Number AS INT) * 10000
FROM @Numbers
ORDER BY NEWID()) CreditLimit,
(SELECT TOP 1 CAST(RAND() * Number AS INT) * 9000
FROM @Numbers
ORDER BY NEWID()) Balance
FROM @Numbers a
CROSS JOIN @Numbers b
) a;
INSERT INTO dbo.Customers
SELECT * FROM #Customers;
GO
這次做 log shipping 我會故意延遲 10 分鐘，才寫入交易紀錄，這是要模擬有人不小心刪除 table，只要十分鐘內通知，都還可以阻止救回來。
至於刪除檔案的時間，要考慮應該給專案多少間來發現問題並提出還原需求。
至於壓縮
是否使用 compression，取決於伺服器的資源限制。
如果你遇到的是網路頻寬限制，你通常會想要啟用 compression。
但如果你的伺服器瓶頸在 CPU，那麼你可能無法負擔 compression 所需要的額外 CPU cycles。
這邊我設定每五分鐘執行一次交易紀錄備份的原因是，設定 RPO 是 10 分鐘，然後 log shipping 不是即時同步。
Primary 產生 log backup
↓
Secondary/DR copy 這個 log backup
↓
Secondary/DR restore 這個 log backup
不能假設 primary server 一定可以用來取回 log backup
第 0 分鐘：剛做完 log backup
第 9 分 59 秒：Primary 壞掉
這時候最多可能已經有快 10 分鐘的交易還沒被備份出來。
如果每五分鐘備份一次
00:00 產生 log backup 1
00:05 產生 log backup 2
00:10 產生 log backup 3
然後 Copy job 也有時間把檔案搬到 DR。
00:05 Primary 產生 .trn
00:06 DR copy 到本機
00:07 DR restore
這樣 DR 端比較有機會維持在 10 分鐘以內的資料落後，不能忽略檔案傳送跟復原的時間。
這邊初始化次要資料庫有三個選項，字面都很好理解
阿因為我們這次沒有完整備份過，所以選第一個，然後他還原選項裡面的資料夾要去NODE3 開一個資料夾來放，你不放也可以她有預設路徑，我只是要說這是 NODE3 的路徑不是 NODE1 的不要亂寫。
阿那個資料夾記得也設定共用，這等等會用到
那個排程點進去時間最好設定成跟備份時間一樣五分鐘，這樣比較好管理。
這大概是整個 LOG SHIPPING 裡面最重要的一個選項，其實可以發現，LOG SHIPPING 我都沒有解釋太多，因為都很字面上的意思然後又很簡單。
但是這得不復原模式 跟 待命模式要搞清楚。
先說不復原模式 :
這意思是資料庫保持在還原中的狀態，他不能讀、不能寫，只能繼續接收下一個 LOG RESTORE。
這個非常適合用來當純粹的 DR Server。
他 failover / recovery 時候比較快
再說待命模式 :
這個就可以讓資料庫可以用唯讀的方式打開，但是下一次要 restore log 時，就要先把它改回去還原模式。
而這個適合 Reporting Server，但是 restore log 的間隔就不要太頻繁，阿不然你就是一直切來切去，reporting 也是沒那麼好用了。
好那為什麼待命模式會慢，是因為 undo 的關係。
最後一樣，這個還原的排程時間間隔最好設定成跟備份、傳送間隔一樣五分鐘。
這是一個在此時必須做出的重要決定，因為一旦設定完成，官方並沒有提供正式支援的方法，可以在不拆掉並重新設定 Log Shipping 的情況下，把 monitor server 加進既有拓樸。
技術上，之後仍然可以強行加入 monitor server，但這個過程需要手動更新 msdb 中的 Log Shipping metadata tables。
因此，這種做法不建議使用。
這是把所有指令匯出的結果，這用途是公司用來存起來管理，不是叫你用指令去開 log shipping，這應該沒有人背得起來。
-- 在主要伺服器端執行下列陳述式，以設定記錄傳送
-- 針對資料庫 [SQLNODE1,14343].[Logshipping]
-- 指令碼需要在主要伺服器端的 [msdb] 資料庫內容中執行。
-------------------------------------------------------------------------------------
-- 加入記錄傳送組態
-- ****** 開始: 要在主要伺服器端執行的指令碼: [SQLNODE1,14343] ******
DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
DECLARE @SP_Add_RetCode As int
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N'Logshipping'
,@backup_directory = N'C:\AOAGShare'
,@backup_share = N'\\SQLNODE1\AOAGShare'
,@backup_job_name = N'LSBackup_Logshipping'
,@backup_retention_period = 2880
,@backup_compression = 2
,@monitor_server = N'DC01,14343'
,@monitor_server_security_mode = 1
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
,@ignoreremotemonitor = 1
IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN
DECLARE @LS_BackUpScheduleUID As uniqueidentifier
DECLARE @LS_BackUpScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'LSBackupSchedule_SQLNODE1,143431'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 5
,@freq_recurrence_factor = 0
,@active_start_date = 20260625
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
,@schedule_id = @LS_BackUpScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID
EXEC msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1
END
EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'Logshipping'
,@secondary_server = N'SQLNODE3,14343'
,@secondary_database = N'Logshipping'
,@overwrite = 1
-- ****** 結束: 要在主要伺服器端執行的指令碼: [SQLNODE1,14343] ******
-- ****** 開始: 要在監視伺服器端執行的指令碼: [DC01,14343] ******
EXEC msdb.dbo.sp_processlogshippingmonitorprimary
@mode = 1
,@primary_id = N''
,@primary_server = N'SQLNODE1,14343'
,@monitor_server = N'DC01,14343'
,@monitor_server_security_mode = 1
,@primary_database = N'Logshipping'
,@backup_threshold = 60
,@threshold_alert = 14420
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
-- ****** 結束: 要在監視伺服器端執行的指令碼: [DC01,14343] ******
-- 在次要伺服器端執行下列陳述式，以設定記錄傳送
-- 針對資料庫 [SQLNODE3,14343].[Logshipping]
-- 指令碼需要在次要伺服器端的 [msdb] 資料庫內容中執行。
-------------------------------------------------------------------------------------
-- 加入記錄傳送組態
-- ****** 開始: 要在次要伺服器端執行的指令碼: [SQLNODE3,14343] ******
DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier
DECLARE @LS_Add_RetCode As int
EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N'SQLNODE1,14343'
,@primary_database = N'Logshipping'
,@backup_source_directory = N'\\SQLNODE1\AOAGShare'
,@backup_destination_directory = N'\\SQLNODE3\LogShippingDR'
,@copy_job_name = N'LSCopy_SQLNODE1,14343_Logshipping'
,@restore_job_name = N'LSRestore_SQLNODE1,14343_Logshipping'
,@file_retention_period = 2880
,@monitor_server = N'DC01,14343'
,@monitor_server_security_mode = 1
,@overwrite = 1
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryCopyJobScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'DefaultCopyJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 5
,@freq_recurrence_factor = 0
,@active_start_date = 20260625
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__CopyJobId
,@schedule_id = @LS_SecondaryCopyJobScheduleID
DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryRestoreJobScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'DefaultRestoreJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 5
,@freq_recurrence_factor = 0
,@active_start_date = 20260625
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__RestoreJobId
,@schedule_id = @LS_SecondaryRestoreJobScheduleID
END
DECLARE @LS_Add_RetCode2 As int
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N'Logshipping'
,@primary_server = N'SQLNODE1,14343'
,@primary_database = N'Logshipping'
,@restore_delay = 10
,@restore_mode = 0
,@disconnect_users = 0
,@restore_threshold = 30
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@overwrite = 1
,@ignoreremotemonitor = 1
END
IF (@@error = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__CopyJobId
,@enabled = 1
EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__RestoreJobId
,@enabled = 1
END
-- ****** 結束: 要在次要伺服器端執行的指令碼: [SQLNODE3,14343] ******
-- ****** 開始: 要在監視伺服器端執行的指令碼: [DC01,14343] ******
EXEC msdb.dbo.sp_processlogshippingmonitorsecondary
@mode = 1
,@secondary_server = N'SQLNODE3,14343'
,@secondary_database = N'Logshipping'
,@secondary_id = N''
,@primary_server = N'SQLNODE1,14343'
,@primary_database = N'Logshipping'
,@restore_threshold = 30
,@threshold_alert = 14420
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@monitor_server = N'DC01,14343'
,@monitor_server_security_mode = 1
-- ****** 結束: 要在監視伺服器端執行的指令碼: [DC01,14343] ******
那一樣做完之後我要去執行一次 failover
log shipping 是沒有自動 failover的 在設定的時候應該看的出來，只能手動。
所以當今天故障發生的時候，要先去備份結尾交易紀錄備份，這在備份的時候有說過，任何故障發生，第一件事情立刻去備份結尾交易紀錄，然後設定 norecovery。
但是很明顯，這個操作是主資料庫還可以存取的狀況才能做到。如果你主資料庫完全存取不了，那你只能去接受損失資料了。不過如果你有做 AOAG 同步的話，那倒還不會損失資料。
-- 這在 NODE1 操作
BACKUP LOG LogShipping
TO DISK = N'\\SQLNODE3\LogShippingDR\LogShipping_tail.trn'
WITH NO_TRUNCATE,
NAME = N'LogShipping-Full Database Backup',
NORECOVERY; --這是關鍵
GO
-- 這在 Node3 操作
RESTORE LOG LogShipping
FROM DISK = N'C:\LogShippingDR\LogShipping_tail.trn'
WITH FILE = 1, RECOVERY, STATS = 10; -- recovery 是關鍵
GO
到這裡，就會看到又報錯了
這代表備份結尾交易紀錄的時候 LSN 跟目前的進度對不起來。
那會對不起來就代表前面還有交易紀錄備份還沒有還原，但是Log shipping 應該要自動幫我們還原才對，所以這時候要去看一下 Log shipping 的紀錄
SELECT
j.name AS [Job名稱],
j.enabled AS [是否啟用],
h.run_date AS [執行日期],
h.run_time AS [執行時間],
h.run_status AS [執行結果代碼],
CASE h.run_status
WHEN 0 THEN N'失敗'
WHEN 1 THEN N'成功'
WHEN 2 THEN N'重試'
WHEN 3 THEN N'取消'
WHEN 4 THEN N'進行中'
END AS [執行結果],
h.message AS [訊息]
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobhistory AS h
ON j.job_id = h.job_id
WHERE j.name LIKE N'LSRestore%'
OR j.name LIKE N'LSCopy%'
ORDER BY
j.name,
h.run_date DESC,
h.run_time DESC;
就會看到這個失敗，這是因為我們從建立好到現在，從來都沒去調整過 agent 的帳號，只有調整 instance 的，所以去 node3 那邊調整 agent 帳號就可以了。
然後等個15~20分鐘
--再跑一次
RESTORE LOG LogShipping
FROM DISK = N'C:\LogShippingDR\LogShipping_tail.trn'
WITH FILE = 1, RECOVERY, STATS = 10;
GO
就成功了
這時候就可以看到 Node1 跟 node3 翻轉了
所以已經完成 DR切換了。
現在的狀態是
原本：
SQLNODE1 = Primary
SQLNODE3 = Secondary，Restoring
剛剛做完 WITH RECOVERY 後：
SQLNODE1 = 舊 Primary，Restoring
SQLNODE3 = 新 Primary，Online
如果沒有打算把 NODE1 修好然後把 PRIMARY 還回去，那就要去停掉 JOB，要不然會變得很奇怪。
原本是這樣
SQLNODE1 備份 log
↓
SQLNODE3 複製 log
↓
SQLNODE3 還原 log
可是現在已經把 SQLNODE3 打開成 Online 了。SQLNODE3 已經不是「等著還原 log 的 secondary」，它現在是新的主資料庫。
不停掉這三個 JOB
SQLNODE1 上的 LSBackup
SQLNODE3 上的 LSCopy
SQLNODE3 上的 LSRestore
就會變成
SQLNODE1 已經 Restoring，還想繼續備份 log
SQLNODE3 已經 Online，還想繼續 restore log
-- NODE1 執行
EXEC msdb.dbo.sp_update_job
@job_name = N'LSBackup_Logshipping',
@enabled = 0;
GO
-- NODE3 執行
USE [msdb];
GO
EXEC msdb.dbo.sp_update_job
@job_name = N'LSCopy_SQLNODE1,14343_Logshipping',
@enabled = 0;
GO
EXEC msdb.dbo.sp_update_job
@job_name = N'LSRestore_SQLNODE1,14343_Logshipping',
@enabled = 0;
GO
最後，去 DC01 查看
14420 : Primary database 在定義的 threshold 時間內，沒有產生 backup。
14421 : Secondary server 在定義的 threshold 時間內，沒有還原 transaction log。
iThome鐵人賽
看影片追技術