首頁/資料庫

SQL Server 2012 Standard 資料庫手動容錯備援

2024年09月25日 資料庫

主機資訊

請先準備三台主機，來源和鏡像主機需使用 Windows 2012，監控主機使用 Windows 10/11 即可。

名稱 作業系統 資料庫 IP 主機名稱
來源主機 Windows 2012 Standard R2 SQL Server 2012 Standard 192.168.xxx.xxx SQLA
鏡像主機 Windows 2012 Standard R2 SQL Server 2012 Standard 192.168.xxx.xxx SQLB
監控主機 Windows 11 23H2 SQL Server 2012 Express 192.168.xxx.xxx SQLWitness

注意事項

在進行設定鏡像之前，請先確認以下事項：

  1. SQLA 與 SQLB 的資料庫必須是同一版本。

  2. SQLA 與 SQLB 的資料庫路徑必須相同。

  3. 在設定鏡像之前請先備份資料庫。

備份主資料庫

連接來源主機 SQLA

使用 SQL Management Studio 連接到 來源主機 SQLA。

建立資料庫備份目錄

建立目錄 C:\DbBackup。

如果要備份到其他目位置，例如：外接硬碟，請自行調整目錄名稱。

備份主資料庫

在 SQL Management Studio 新增查詢。

VirtualBox_SQLA_24_09_2024_17_37_35

執行以下指令：

USE master;
GO

ALTER DATABASE STDB SET TRUSTWORTHY OFF
GO

ALTER DATABASE STDB
    SET RECOVERY FULL;
GO

BACKUP DATABASE STDB
    TO DISK = 'C:\DbBackup\STDB.bak'
    WITH FORMAT
GO

BACKUP LOG STDB
    TO DISK = 'C:\DbBackup\STDBLog.bak'
GO

VirtualBox_SQLA_24_09_2024_17_40_23

複製資料庫備份檔案到鏡像主機

  1. 將 C:\DbBackup\STDB.bak 和 C:\DbBackup\STDBLog.bak 複製到外接硬碟。

  2. 將外接硬碟插入鏡像主機 SQLB。

  3. 在鏡像主機 SQLB 建立目錄 C:\DbBackup。

  4. 將外接硬碟上的 STDB.bak 和 STDBLog.bak 複製到 C:\DbBackup。

在鏡像主機還原資料庫

在還原之前，請先確認步驟 2.4 已完成。

連接鏡像主機 SQLB

使用 SQL Management Studio 連接到鏡像主機 SQLB。

還原資料庫

在 SQL Management Studio 新增查詢。

VirtualBox_SQLB_24_09_2024_17_45_58

執行以下指令

USE master;
GO

RESTORE DATABASE STDB
    FROM DISK = 'C:\DbBackup\STDB.bak'
    WITH NORECOVERY
GO

RESTORE LOG STDB
    FROM DISK = 'C:\DbBackup\STDBLog.bak'
    WITH FILE=1, NORECOVERY
GO

VirtualBox_SQLB_24_09_2024_17_53_47

設定監控主機

建立目錄

監控主機建立目錄 C:\DbBackup。

設定監控主機

在監控主機執行以下指令：

USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'edwin2024!@#';
GO

CREATE CERTIFICATE HOST_witness_cert
    WITH SUBJECT = 'HOST_witness certificate for database mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
    STATE = STARTED
    AS TCP (
        LISTENER_PORT = 5022
        , LISTENER_IP = ALL
        )
    FOR DATABASE_MIRRORING (
        AUTHENTICATION = CERTIFICATE HOST_witness_cert
        , ENCRYPTION = REQUIRED ALGORITHM AES
        , ROLE = WITNESS
        );
GO

BACKUP CERTIFICATE HOST_witness_cert TO FILE = 'C:\DbBackup\HOST_witness_cert.cer';
GO

將憑證複製到來源主機和鏡像主機

將憑證檔案 C:\DbBackup\HOST_witness_cert.cer 複製到來源主機和鏡像主機的 C:\DbBackup。

建立鏡像主機的憑證

連接鏡像主機 SQLB

使用 SQL Management Studio 連接到鏡像主機 SQLB。

建立憑證

在鏡像主機 執行以下指令：

VirtualBox_SQLB_25_09_2024_09_38_08

USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'edwin2024!@#';
GO

CREATE CERTIFICATE HOST_mirror_cert
    WITH SUBJECT = 'HOST_mirror certificate for database mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
    STATE = STARTED
    AS TCP (
        LISTENER_PORT = 5022
        , LISTENER_IP = ALL
        )
    FOR DATABASE_MIRRORING (
        AUTHENTICATION = CERTIFICATE HOST_mirror_cert
        , ENCRYPTION = REQUIRED ALGORITHM AES
        , ROLE = ALL
        );
GO

BACKUP CERTIFICATE HOST_mirror_cert TO FILE = 'C:\DbBackup\HOST_mirror_cert.cer';
GO

複製憑證到來源主機與監控主機

將憑證檔案 C:\DbBackup\HOST_mirror_cert.cer 複製到來源主機和監控主機的 C:\DbBackup。

建立來源主機的憑證

連接來源主機 SQLB

使用 SQL Management Studio 連接到來源主機 SQLA。

建立憑證

在鏡像主機 執行以下指令：

VirtualBox_SQLA_25_09_2024_09_43_29

USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'edwin2024!@#';
GO

CREATE CERTIFICATE HOST_principal_cert
    WITH SUBJECT = 'HOST_A principal';
GO

CREATE ENDPOINT Endpoint_Mirroring
    STATE = STARTED
    AS TCP (
        LISTENER_PORT = 5022
        , LISTENER_IP = ALL
        )
    FOR DATABASE_MIRRORING (
        AUTHENTICATION = CERTIFICATE HOST_principal_cert
        , ENCRYPTION = REQUIRED ALGORITHM AES
        , ROLE = ALL
        );
GO

BACKUP CERTIFICATE HOST_principal_cert TO FILE = 'C:\DbBackup\HOST_principal_cert.cer';
GO

複製憑證到鏡像主機與與監控主機

將憑證檔案 C:\DbBackup\HOST_principal_cert.cer 複製到鏡像主機與與監控主機的 C:\DbBackup。

設定連線

設定監控主機連線

在監控主機執行以下指令：

USE master;
GO

CREATE LOGIN HOST_principal_login WITH PASSWORD = 'edwin2024!@#';
CREATE LOGIN HOST_mirror_login WITH PASSWORD = 'edwin2024!@#';
GO

--Create a user for that login.-
CREATE USER HOST_principal_user FOR LOGIN HOST_principal_login;
CREATE USER HOST_mirror_user FOR LOGIN HOST_mirror_login;
GO

--Associate the certificate with the user.-
CREATE CERTIFICATE HOST_principal_cert
    AUTHORIZATION HOST_principal_user
    FROM FILE = 'C:\DbBackup\HOST_principal_cert.cer'
GO

--Grant CONNECT permission on the login for the remote mirroring endpoint.-
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_principal_login;
GO

--Associate the certificate with the user.
CREATE CERTIFICATE HOST_mirror_cert
    AUTHORIZATION HOST_mirror_user
    FROM FILE = 'C:\DbBackup\HOST_mirror_cert.cer'
GO

--Grant CONNECT permission on the login for the remote mirroring endpoint.-
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_mirror_login;
GO

設定鏡像主機連線

在鏡像主機執行以下指令：

USE master;
GO

CREATE LOGIN HOST_principal_login WITH PASSWORD = 'edwin2024!@#';
CREATE LOGIN HOST_witness_login WITH PASSWORD = 'edwin2024!@#';
GO

--Create a user for that login.-

CREATE USER HOST_principal_user FOR LOGIN HOST_principal_login;
CREATE USER HOST_witness_user FOR LOGIN HOST_witness_login;
GO

--Associate the certificate with the user.-
CREATE CERTIFICATE HOST_principal_cert
    AUTHORIZATION HOST_principal_user
    FROM FILE = 'C:\DbBackup\HOST_principal_cert.cer'
GO

--Grant CONNECT permission on the login for the remote mirroring endpoint.-
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_principal_login;
GO

--Associate the certificate with the user.
CREATE CERTIFICATE HOST_witness_cert
    AUTHORIZATION HOST_witness_user
    FROM FILE = 'C:\DbBackup\HOST_witness_cert.cer'
GO

--Grant CONNECT permission on the login for the remote mirroring endpoint.-
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_witness_login;
GO

設定來源主機連線

在來源主機執行以下指令：

USE master;
GO

CREATE LOGIN HOST_mirror_login WITH PASSWORD = 'edwin2024!@#';
CREATE LOGIN HOST_witness_login WITH PASSWORD = 'edwin2024!@#';
GO

--Create a user for that login.
CREATE USER HOST_mirror_user FOR LOGIN HOST_mirror_login;
CREATE USER HOST_witness_user FOR LOGIN HOST_witness_login;
GO

--Associate the certificate with the user.
CREATE CERTIFICATE HOST_mirror_cert
    AUTHORIZATION HOST_mirror_user
    FROM FILE = 'C:\DbBackup\HOST_mirror_cert.cer'
GO

--Grant CONNECT permission on the login for the remote mirroring endpoint.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_mirror_login;
GO
CREATE CERTIFICATE HOST_witness_cert
    AUTHORIZATION HOST_witness_user
    FROM FILE = 'C:\DbBackup\HOST_witness_cert.cer'
GO

--Grant CONNECT permission on the login for the remote mirroring endpoint.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_witness_login;
GO

設定夥伴關係

設定監控主機

在監控主機執行以下指令：

USE master;
GO

ALTER DATABASE STDB
    SET PARTNER = 'TCP://192.168.xxx.xxx:5022';
GO

設定來源主機

在來源主機執行以下指令：

USE master;
GO

ALTER DATABASE STDB
    SET PARTNER = 'TCP://192.168.xxx.xxx:5022';
GO

ALTER DATABASE STDB
    SET WITNESS = 'TCP://192.168.xxx.xxx:5022'
GO

確認狀態

確認來源主機狀態

來源主機的資料庫 STDB 狀態應為 (主體, 已同步處理)

VirtualBox_SQLA_25_09_2024_10_02_14

確認備份主機狀態

備份主機的資料庫 STDB 狀態應為 (鏡像, 已同步處理/正在還原…)

VirtualBox_SQLB_25_09_2024_10_03_57

災難復原

來源主機故障，鏡像主機自動變為主體

當來源主機故障時，鏡像主機會自動升級為主體資料庫。此時 STDB 資料庫狀態為(主體, 已中斷連線)。

VirtualBox_SQLB_25_09_2024_10_15_48

來源主機恢復運作，手動切換鏡像主機狀態

當來源主機恢復時運作，資料庫 STDB 的狀態為 (鏡像, 已同步處理 / 正在還原…)

VirtualBox_SQLA_25_09_2024_10_21_38

此時使用 SQL Management Studio 連到 SQLB，在 STDB 資料庫上按滑鼠右鍵 工作\鏡像

VirtualBox_SQLB_25_09_2024_10_22_41

點選容錯移轉

VirtualBox_SQLB_25_09_2024_10_23_10

點選是

VirtualBox_SQLB_25_09_2024_10_26_22

等資料同步完成之後，來源主機的狀態又回到 (主體, 已同步處理)

VirtualBox_SQLA_25_09_2024_10_27_53

而鏡像主機的狀態則回到 (鏡像, 已同步處理/正在還原…)

VirtualBox_SQLB_25_09_2024_10_28_34