SQL Server High Availability Step by Step Database Mirror

SQL Server 2019 資料庫鏡像設定實戰

許家瑋 Alex Hsu 2022/03/04 14:28:13
429

前言

資料庫鏡像(Database Mirror)為微軟SQL Server高可用性解決方案之一,本文將說明Database Mirror在SQL Server 2019的設定步驟,以及如何進行自動與手動容錯移轉驗證。

若您有類似需求,可以考慮評估 Always On Availability Group (AG)的解決方案,他可算是Database Mirror的後繼接班人。

 

名詞解釋

主體資料庫(Principle)

資料庫鏡像中的一種可讀寫資料庫,該資料庫的交易記錄檔記錄會套用至資料庫的唯讀副本 (鏡像資料庫)。

鏡像資料庫(Mirror)

通常會與主體資料庫完全同步處理的資料庫副本,需與主體資料庫為同一個版本。

見證(Witness)

只能搭配高安全性模式使用的一種 SQL Server 選擇性執行個體,可讓鏡像伺服器辨別何時要起始自動容錯移轉。 與兩個容錯移轉夥伴不同的是,見證並不是為資料庫服務。 支援自動容錯移轉是見證的唯一角色。

 

本次範例環境設定說明

為了更快理解後續步驟中的語法與截圖內容,本次範例相關環境設定如下說明:

主體資料庫(Principle): MIS-GO-DB1(10.100.90.225)

鏡像資料庫(Mirror): MIS-GO-DB2(10.100.90.226)

見證 (Witness): MIS-GO-WITNESS(10.100.90.218)

資料庫名稱:kgo

 

在開始之前,請先確定主體資料庫實體檔案(mdf, ldf)所在的磁碟儲存空間是否充足。

若空間不足,請移轉到其他磁碟儲存空間,並預留至少四倍以上的可用空間。

 

確認資料庫還原狀態

確認Principlekgo資料庫還原狀態是否為full。

SELECT * FROM sys.databases

 

開啟備份還原工具

Principle內資料庫kgo按下右鍵『工作』->『備份』。

 

進行完整備份

備份Principle的kgo資料庫。設定備份壓縮。

 

按一下上方產生指令碼,如下圖:

 

產生指令經調整後如下:

BACKUP DATABASE [kgo] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQL2019\MSSQL\Backup\kgo_20220127_FULL.bak' WITH NOFORMAT, NOINIT, NAME = N'kgo-完整 資料庫 備份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATUS = 10
GO

 

執行後請見如下方紀錄,即表示成功備份完成。

 

進行交易紀錄備份

備份Principle上的kgo資料庫交易紀錄。

設定備份壓縮。

 

按一下上方產生指令碼,如下圖:

 

產生指令經調整後如下:

BACKUP DATABASE [kgo] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQL2019\MSSQL\Backup\kgo_20220127_LOG.trn' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATUS = 10
GO

 

執行後請見如下方紀錄,即表示成功備份完成。

 

確認備份完成檔案

可至C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQL2019\MSSQL\Backup路徑下確認已成功備份檔案,如下圖。

 

進行還原

將備份檔案從Principle搬移至Mirror硬碟空間。此路徑底下:C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQL2019\MSSQL\Backup

 

Mirror資料庫按下右鍵,選擇『還原資料庫』。

 

從選擇裝置尋找已複製備份檔進行還原。

 

於選項部分設定復原狀態為「RESTORE WITH NORECOVERY」。

 

按一下上方Script按鈕,產生語法後微調如下:

USE [master]
RESTORE DATABASE [kgo] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQL2019\MSSQL\Backup\kgo_20220127_FULL.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [kgo] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQL2019\MSSQL\Backup\kgo_20220127_LOG.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

GO

 

待處理百分比至100即完成,如下圖:

 

確認資料庫於正在還原狀態。

 

資料庫鏡像連線設定

Principle點選kgo資料庫,按下右鍵點開『資料庫屬性』,選擇『鏡像』。

 

點選『設定安全性』並依照步驟設定。

 

於見證伺服器執行個體設定處選擇『是』。

 

於安全設定組態處,也勾選見證伺服器。

 

進行Principle設定,預設值為5022,若有防火牆記得要在Principle、Mirror和Witness間互相開通。

 

進行Mirror設定,請留意連接字串處設定,必須與Principle為同一帳號。

 

進行Witness設定,詳見下圖。

 

服務帳戶部分,若伺服器有加入AD並且使用AD帳號開啟資料庫執行個體服務,則需填寫。

反之若三台執行個體皆未加入AD環境,故必須留白。

 

按下完成。

 

等待精靈跑完,再按下關閉。

 

設定生成後,先不要啟動鏡像

 

確認Principle、Mirror、Witness這三台設定正確後,再按下啟動鏡像。

 

啟動進行中,此處需要一點時間,有時甚至介面會有當機的感覺,需等待約莫五分鐘。

 

完成後,介面結果如下所示。

 

啟用鏡像後可見到Principle資料庫狀態正確。

 

Mirror資料庫狀態正確。

 

PrincipleMirror資料庫底下都可看到資料庫鏡像端點。

 

鏡像移轉資料同步驗證

鏡像啟用完成後要知道有沒有效果,必須實際進行容錯移轉驗證。

 

自動鏡像移轉資料同步驗證

Principle建立驗證table,並新增幾筆資料

 

Principle將資料庫服務停機

 

Principle資料庫服務停止後,撈取Mirrorkgo資料庫於第一點所新增的資料表資料,如下圖撈取顯示結果,資料同步成功。

同時下圖左方kgo資料庫狀態由鏡像(Mirror)改為主體(Principle),表示自動移轉成功。

 

手動鏡像移轉資料同步驗證

以直接停止服務的方式,模擬完自動容錯移轉後,接著進行手動使用介面工具,進行主體和鏡像的移轉驗證。

重新於Principle啟動資料庫服務

 

Mirror重新整理資料庫狀態後,kgo(主體已中斷連線)變成(主體已同步處理)。

*由於先前停止MIS-GO-DB1的服務後,原MIS-GO-DB2從鏡像已換為主體的角色,故在後面手動切換前,此時仍為主體。

而已中斷連線變為已同步處理這塊,表示MIS-GO-DB1服務重啟後,有發現自己應為鏡像角色,開始進行同步作業。

 

Principle重新整理資料庫狀態後,kgo變成(鏡像已同步處理/正在還原中)。

 

Mirror drop測試用資料表 tmp_20220127_test。

 

確定Mirrorkgo資料庫沒有tmp_20220127_test資料表。

 

Mirror點選kgo資料庫檢視其屬性,並點選鏡像頁面。

 

點選『容錯移轉』,並於確認對話方塊按下『是』。

 

按下後,Principlekgo資料庫狀態已從(鏡像已同步處理/正在還原中)變成(主體已同步處理),表示手動移轉成功。

 

Principle確認kgo資料庫內已同步刪除tmp_20220127_test資料表,如下圖,表示資料手動同步移轉成功。

許家瑋 Alex Hsu