SQL Server 2005 鏡像構建手冊(sql2005數據庫同步鏡像方案)

一、 鏡像簡介

1、 簡介

數據庫鏡像是將數據庫事務處理從一個SQL Server數據庫移動到不同SQL Server環境中的另一個SQL Server數據庫中。鏡像不能直接訪問;它只用在錯誤恢復的情況下才可以被訪問。

要進行數據庫鏡像所需的最小需求包括了兩個不同的SQL Server運行環境。主服務器被稱為“主機”，第二個服務器被稱作“備機”。主機數據庫就是你實際用著的數據庫，鏡像數據庫就是你的數據庫的備用拷貝。當事務寫入你的基本服務器的時候，他們也同樣被傳送到並寫入你的鏡像數據庫中。

除了基本和鏡像之外，你還可以引入另一個可選的組件，名為“見證”。見證服務器是第三個SQL Server 2005運行實例，它是在判斷什麼時候進行錯誤恢復的時候，用於基本和鏡像之間內部交流。只有當你想實現自動錯誤恢復的時候用到這個選項。它實現了2比1投票的能力，當我的一個組件不可達，並因此需要進行錯誤恢復的時候。見證服務器只有在你想實現自動錯誤恢復的時候才需要用到。

2、 優點

下表是SQL Server可用性官方解決方案的一個對照表，現時我中心使用的恢復模式是“冷備份”中的“備份/恢復”，通常來說“熱備份”比“冷備份”的可用性更高，恢復更快，更適合我中心現時的實際情況。如果不從成本考慮的話，“熱備份”中的“故障轉移群集”的可用性是最高的，但是故障轉移群集需要借助磁盤陣列而且建設本身復雜性較高。數據庫鏡像的建立並沒有太多的硬件要求，最起碼沒有像“故障轉移群集”需要共享存儲這麼高的要求。

2、 缺點

（1）由於SQL Server是一個實例多個數據庫的產品，數據庫鏡像技術是基於數據庫級別的，因此每次主數據庫新增數據庫都必須為備機增加數據庫並且為新增的數據庫建立鏡像關系。

（2）數據庫的登錄名和用戶是存儲在master數據庫，master數據庫是不能做鏡像的，所以每次操作數據庫的登錄名和用戶也是需要多維護一份，

（3）數據庫作業不能得到相應的維護。

（4）微軟號稱鏡像可以讓客戶端對故障透明，但是實際測試中發現只有滿足特定的條件才能實現透明化，而且透明化得客戶端支持才可行（.net Framework 2.0以上，Microsoft jdbc驅動 1.1以上）。

（5）跨數據庫事務和分布式事務均不支持數據庫鏡像。

縱觀其他幾種方式，僅有“熱備份”的“故障轉移群集”沒有這些問題。

一、配置主備機1、 物理連接

將主備數據庫按照如圖所示連接：

2、 檢查SQL Server 2005數據庫

只有SQL Server 2005 標准版、企業版和開發版才可以建立數據鏡像。其他版本即Express只能作為見證服務器。如果實在不清楚什麼版本，執行如下語句查看：

select @@version;

若要對此數據庫進行數據庫鏡像，必須將它更改為使用完整恢復模式。若要用 Transact-SQL 實現此目的，請使用 ALTER DATABASE 語句：

 USE master;
 ALTER DATABASE <DatabaeName> 
 SET RECOVERY FULL;

二、主備實例互通
實現互通可以使用域或證書來實現，考慮實現的簡單，以下選取證書的方式實現。注意：實現“主備數據庫實例互通”的操作只需要做一次，例如為了將兩個SQL Server 2005的實例中的5個數據庫建成鏡像關系，則只需要做一次以下操作就可以了；或者這樣理解：每一對主備實例（不是數據庫）做一次互通。

1、創建證書（主備可並行執行）

--主機執行：

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , 
START_DATE = '01/01/2008';

--備機執行：

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 
START_DATE = '01/01/2008';

2、創建連接的端點（主備可並行執行）

--主機執行：

CREATE ENDPOINT 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 );

--備機執行：

CREATE ENDPOINT 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 );

3、備份證書以備建立互聯（主備可並行執行）

--主機執行：

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';
 --備機執行：

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';

4、互換證書
將備份到D:\SQLBackup\的證書進行互換，即HOST_A_cert.cer復制到備機的D:\SQLBackup\。HOST_B_cert.cer復制到主機的D:\SQLBackup\

5、添加登陸名、用戶（主備可並行執行）
以下操作只能通過命令行運行，通過圖形界面無法完成。（截至文檔編寫結束，SQL Server2005的不定號為SP2）

--主機執行：

CREATE LOGIN HOST_B_login WITH PASSWORD = 'killkill';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

--備機執行：

CREATE LOGIN HOST_A_login WITH PASSWORD = 'killkill';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

三、建立鏡像關系
以下步驟是針對每個數據庫進行的，例如：現有主機中有5個數據庫以下過程就要執行5次。

1、 手工同步登錄名和密碼
在第一章中提到數據庫鏡像的缺點之一是無法維護登錄名，所以需要我們手工維護登錄。

通常來說數據庫都將會有若干個用戶作為訪問數據庫的用戶，並且數據庫會有相應的登錄名，但是在備機中缺少與之相對應的登錄名，例如某業務系統使用'myuser'作為登錄名訪問數據庫，但是在備機中沒有'myuser'這個登錄名，因此一旦主備切換，業務系統就無法登錄數據庫了，這種情況稱為"孤立用戶"

在主數據庫中執行如下語句：

USE master;
select sid,name from syslogins;

查找出相應的用戶名和sid，例如：上述的'myuser'

在備數據庫中執行如下語句：

USE master;
exec sp_addlogin 
@loginame = '<LoginName>', 
@passwd = '<Password>', 
@sid = <sid> ;

這裡的'LoginName'即主數據庫中的登錄名，sid即是上述通過SQL語句查找出的sid。

例如,查詢得到的sid和name如下所示。

sid name
---------------------------------- -----------------
0x074477739DCA0E499C29394FFFC4ADE4 cz_account

則建立登錄名的SQL語句：

USE master;
exec sp_addlogin 
@loginame = 'cz_account', 
@passwd = 'password', 
@sid = 0x074477739DCA0E499C29394FFFC4ADE4;

到此為止可以認為備機數據庫的環境已經與主機同步了，還差數據庫內的數據未同步。

2、 准備備機數據庫
承接上文，該節是描述如何同步主備數據庫內的數據。

可以嘗試從剛剛使用的全備文件進行還原，在還原數據的時候需要使用選上“with non recover”。如圖所示：

如果執行成功數據庫將會變成這個樣子：

3、 建立鏡像

由於是實驗，沒有為服務器配置雙網卡，IP地址與圖有點不一樣，但是原理一樣。

--主機執行：

ALTER DATABASE shishan SET PARTNER = 'TCP://10.168.6.45:5022';

--如果主體執行不成功，嘗試在備機中執行如下語句：

ALTER DATABASE shishan SET PARTNER = 'TCP://10.168.6.49:5022';

如果執行成功，則主備數據庫將會呈現如上圖所示的圖標。

如果建立失敗，提示類似數據庫事務日志未同步，則說主備數據庫的數據（日志）未同步，為保證主備數據庫內的數據一致，應在主數據庫中實施一次“事務日志”備份，並還原到備數據庫上。備份“事務日志”如圖所示：

還原事務日志時需在選項中選擇“restore with norecovery”，如圖所示：

成功還原以後再執行建立鏡像的SQL語句。

四、測試操作

1、主備互換

--主機執行：

USE master;
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;

2、主服務器Down掉,備機緊急啟動並且開始服務

--備機執行：

USE master;
ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

3、原來的主服務器恢復,可以繼續工作,需要重新設定鏡像

4、原來的主服務器恢復,可以繼續工作

--默認情況下，事務安全級別的設置為 FULL，即同步運行模式，而且SQL Server 2005 標准版只支持同步模式。

--關閉事務安全可將會話切換到異步運行模式，該模式可使性能達到最佳。

USE master;
ALTER DATABASE <DatabaseName> SET PARTNER SAFETY FULL; --事務安全,同步模式
ALTER DATABASE <DatabaseName> SET PARTNER SAFETY OFF; --事務不安全,異步模式