非域環境下SQL Server mirror建立

環境：

SQLServer2012SP1

主：10.202.11.47

備：10.202.11.49

不配置見證伺服器

 

步驟:

第1 步：建立master key(主金鑰)

主庫中執行：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Aa000000'

檢視主金鑰是否建立：

select is_master_key_encrypted_by_server ,*   from sys . databases

 

備庫中執行同樣操作:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Aa000000'

 

第2 步：建立證照使用金鑰加密

主庫中執行： 

USE master

GO  

CREATE CERTIFICATE Host_A_Cert  

WITH Subject = 'Host_47 Certificate' ,

Expiry_Date = '2055-1-1' ; -- 過期日期

 

備庫中執行：

USE master

GO  

CREATE CERTIFICATE Host_B_Cert  

WITH Subject = 'Host_49 Certificate' ,

Expiry_Date = '2055-1-1' ; -- 過期日期

 

第3 步：建立endpoint

主庫中執行：

IF NOT EXISTS

( SELECT 1 FROM sys . database_mirroring_endpoints )  

    BEGIN  

    CREATE ENDPOINT [Mirroring] STATE = STARTED AS TCP (

    LISTENER_PORT = 5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =

    CERTIFICATE Host_A_Cert , ENCRYPTION = REQUIRED Algorithm AES , ROLE =ALL )

    END

 

如果存在endpoint則可以修改使用

SELECT   *    FROM   sys . database_mirroring_endpoints

 

ALTER ENDPOINT [Mirroring] STATE = STARTED AS TCP (

    LISTENER_PORT = 5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =

    CERTIFICATE Host_A_Cert , ENCRYPTION = REQUIRED Algorithm AES , ROLE =ALL )

 

備庫中執行：

IF NOT EXISTS

( SELECT 1 FROM sys . database_mirroring_endpoints )  

    BEGIN  

    CREATE ENDPOINT [Mirroring] STATE = STARTED AS TCP (

    LISTENER_PORT = 5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =

    CERTIFICATE Host_B_Cert , ENCRYPTION = REQUIRED Algorithm AES , ROLE =ALL )

    END

 

第4 步：備份證照

主備庫OS中建立資料夾 C:\MIRROR

 

主庫中執行：

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\MIRROR\HOST_A_cert.cer' ;

 

備庫中執行：

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\MIRROR\HOST_B_cert.cer' ;

主備庫OS中相互copy證照

 

第5 步：建立登入login

主庫中執行：

CREATE LOGIN Host_B_Login WITH PASSWORD = 'Password666'

 

備庫中執行：

CREATE LOGIN Host_A_Login WITH PASSWORD = 'Password666'

 

第6 步：建立User並對映到login

主庫中執行：

CREATE USER Host_B_User For Login Host_B_Login ;

 

備庫中執行：

CREATE USER Host_A_User For Login Host_A_Login ;

 

 

第7 步：建立證照，並使用從夥伴伺服器中copy過來的證照匯入，再授權證照給賬號

*****************************************

如果存在則先刪除

IF EXISTS( select * from sys . certificates WHERE name = 'HOST_B_cert' )

DROP CERTIFICATE HOST_B_cert

GO

*****************************************

 

主庫中執行：

CREATE CERTIFICATE Host_B_Cert AUTHORIZATION Host_B_User FROM FILE = 'C:\MIRROR\Host_B_Cert.cer' ;

 

備庫中執行：

CREATE CERTIFICATE Host_A_Cert AUTHORIZATION Host_A_User FROM FILE = 'C:\MIRROR\Host_A_Cert.cer' ;

 

第8 步：授權connect on endpoint許可權至login

主庫中執行：

GRANT CONNECT ON ENDPOINT :: [Mirroring] TO [Host_B_Login]

 

備庫中執行：

GRANT CONNECT ON ENDPOINT :: [Mirroring] TO [Host_A_Login]

 

第9 步：備份還原db

主庫中執行：

右鍵需要做mirror的DB->Tasks->back up...

Backup type選擇Full，並設定back up to:備份檔案目錄，之後點OK完成Full backup

再Backup type選擇Transaction log，並設定back up to:備份檔案目錄， Options->Overwrite media->Back up to the existing media set，之後點OK完成Transaction backup

備庫中執行：

右鍵database->restore database

Device選擇主庫已備份的檔案

Options->recovery state:選擇restore with norecovery模式還原，否則會報錯Msg 1416

點選OK

 

第10 步：開啟mirror

在備庫上執行（ 必須備庫上先執行 ）：

IP TCP://10.202.11.47:5022 為主庫IP

ALTER DATABASE mesdb SET PARTNER = 'TCP://10.202.11.47:5022' ;

 

在主庫上執行：

IP TCP://10.202.11.49:5022 為備主庫IP

ALTER DATABASE mesdb SET PARTNER = 'TCP://10.202.11.49:5022' ;

 

此時，mirror已經開啟。主庫狀態已變為principal, synchronized

 

另，根據需求可以修改mirror為high performance模式(預設試high safety)

ALTER DATABASE mesdb SET SAFETY OFF

 

第11 步：可以對備庫mirror DB建立只讀快照DB(選用)

備庫中執行：

create database snap_mesdb

on ( name = mesdb ,   -- 檔案邏輯名，需要和 mes 中一致

filename = 'c:\db_snapshot\snap_mes_1.ss' –- 快照物理檔名，可隨便取

)

as snapshot of mesdb --- 需要做快照的 DB

 

至此，非域環境中SQL Server mirror的搭建完成