[SQL]SQL Server 使用 AlwaysON 搭配維護計畫的資料庫備份設定 - 交易紀錄與差異備份

透過維護計畫裡面的備份資料庫,對 AlwaysON 上的資料庫去進行交易紀錄備份

在前一篇文章「SQL Server 使用 AlwaysON 搭配維護計畫的資料庫備份設定」寫完之後,忽然之間有一堆人點閱,看起來還蠻多人關心 AlwaysON 的。其中也有些朋友詢問,那維護計畫中的備份資料庫,也有支援交易紀錄備份和差異備份,那是否這一個部分也都有支援呢 ?

基本上我想先說結論好了,基本上 AlwaysON 的次要伺服器上能支援的備份能有以下的幾種

  主要伺服器 次要伺服器
完整備份 支援 支援 ( 要加入 COPY_ONLY )
交易紀錄備份 支援 支援
差異備份 支援 不支援

當我們在使用維護計畫裡面的備份資料庫的工作,當他判斷有使用 AlwaysON 的時候,會使用一個系統 DMF - sys.fn_hadr_backup_is_preferred_replica 來判斷,所以也就會像我們在前一篇文章中所展示的,如果我們要做完整資料庫備份的時候,雖然主要伺服器上是有支援,但維護計畫在會選擇在次要伺服器上去做備份,因此就必須要加入 COPY_ONLY 的選項。

所以可以參考上面的表,當使用維護計畫裡面的備份資料庫工作時候,完整備份和交易紀錄備份這兩個都可以正常執行,但就沒有辦法執行差異備份了。


交易紀錄備份測試

我下面用個例子來測試用維護計畫進行交易紀錄備份,首先我有一個測試用的資料庫,預設資料庫的資料檔和交易紀錄檔都是 8MB

因此我用下面的語法去建立一個測試資料表,並且塞入 50000 的資料

CREATE TABLE T1
(
	F1	INT	IDENTITY ( 1, 1 )  PRIMARY KEY,
	F2	NCHAR(128)
)
GO

INSERT INTO T1(F2) VALUES ( CAST( NEWID() AS NVARCHAR(64)) );
GO 50000

當上述語法完成之後,我們會看到資料檔和交易紀錄檔案都有不小的成長

此時我們利用 DBCC LOGINFO 的指令來看一下資料庫交易紀錄檔的狀況,可以看到目前到最後面的 FSeqNo 欄位的值是跑到 133

因此我們就按照一開始的結論,在次要伺服器上透過維護計畫去執行交易紀錄備份,從圖上我們可以看到當選擇交易紀錄備份的時候,下方也沒有任何警告訊息

因此我們就放下去執行囉

執行完後如果單純看 DBCC LOGINFO ,是比較看不出有甚麼變化,因此我們用之前的指令,只是這次塞入 10000 筆的資料

INSERT INTO T1(F2) VALUES ( CAST( NEWID() AS NVARCHAR(64)) );
GO 10000

此時我們再透過 DBCC LOGINFO 的指令來查看,從下圖中您可以看出,此時 FSeqNo 已經回到第一筆紀錄上面,Parity 的值也從之前的 64 變成 128,因此從這樣的實驗中可以看出來,當我們在次要伺服器上去執行交易紀錄備份之後,是會影響主要伺服器上的交易紀錄檔可以重頭開始使用了,也就不會讓交易紀錄檔去瘋狂長大了。

至於差異備份的部分我就不寫了,因為不論是在主要或者是次要上,都沒有辦法透過維護計畫裡面的備份資料庫來執行,如果真的要執行的話,那可能就要自己手動去寫指令來進行了。