SQL Server 2005 鏡像構(gòu)建手冊(sql2005數(shù)據(jù)庫同步鏡像方案)

一、 鏡像簡介

1、 簡介

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

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

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

2、 優(yōu)點

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

2、 缺點

（1）由于SQL Server是一個實例多個數(shù)據(jù)庫的產(chǎn)品，數(shù)據(jù)庫鏡像技術(shù)是基于數(shù)據(jù)庫級別的，因此每次主數(shù)據(jù)庫新增數(shù)據(jù)庫都必須為備機增加數(shù)據(jù)庫并且為新增的數(shù)據(jù)庫建立鏡像關(guān)系。

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

（3）數(shù)據(jù)庫作業(yè)不能得到相應(yīng)的維護。

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

（5）跨數(shù)據(jù)庫事務(wù)和分布式事務(wù)均不支持?jǐn)?shù)據(jù)庫鏡像。

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

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

將主備數(shù)據(jù)庫按照如圖所示連接：

2、 檢查SQL Server 2005數(shù)據(jù)庫

只有SQL Server 2005 標(biāo)準(zhǔn)版、企業(yè)版和開發(fā)版才可以建立數(shù)據(jù)鏡像。其他版本即Express只能作為見證服務(wù)器。如果實在不清楚什么版本，執(zhí)行如下語句查看：

select @@version;

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

 USE master;
 ALTER DATABASE DatabaeName> 
 SET RECOVERY FULL;

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

1、創(chuàng)建證書（主備可并行執(zhí)行）

--主機執(zhí)行：

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

--備機執(zhí)行：

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、創(chuàng)建連接的端點（主備可并行執(zhí)行）

--主機執(zhí)行：

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

--備機執(zhí)行：

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、備份證書以備建立互聯(lián)（主備可并行執(zhí)行）

--主機執(zhí)行：

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

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

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

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

--主機執(zhí)行：

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];

--備機執(zhí)行：

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];

三、建立鏡像關(guān)系
以下步驟是針對每個數(shù)據(jù)庫進行的，例如：現(xiàn)有主機中有5個數(shù)據(jù)庫以下過程就要執(zhí)行5次。

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

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

在主數(shù)據(jù)庫中執(zhí)行如下語句：

USE master;
select sid,name from syslogins;

查找出相應(yīng)的用戶名和sid，例如：上述的'myuser'

在備數(shù)據(jù)庫中執(zhí)行如下語句：

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

這里的'LoginName'即主數(shù)據(jù)庫中的登錄名，sid即是上述通過SQL語句查找出的sid。

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

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

則建立登錄名的SQL語句：

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

到此為止可以認(rèn)為備機數(shù)據(jù)庫的環(huán)境已經(jīng)與主機同步了，還差數(shù)據(jù)庫內(nèi)的數(shù)據(jù)未同步。

2、 準(zhǔn)備備機數(shù)據(jù)庫
承接上文，該節(jié)是描述如何同步主備數(shù)據(jù)庫內(nèi)的數(shù)據(jù)。

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

如果執(zhí)行成功數(shù)據(jù)庫將會變成這個樣子：

3、 建立鏡像

由于是實驗，沒有為服務(wù)器配置雙網(wǎng)卡，IP地址與圖有點不一樣，但是原理一樣。

--主機執(zhí)行：

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

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

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

如果執(zhí)行成功，則主備數(shù)據(jù)庫將會呈現(xiàn)如上圖所示的圖標(biāo)。

如果建立失敗，提示類似數(shù)據(jù)庫事務(wù)日志未同步，則說主備數(shù)據(jù)庫的數(shù)據(jù)（日志）未同步，為保證主備數(shù)據(jù)庫內(nèi)的數(shù)據(jù)一致，應(yīng)在主數(shù)據(jù)庫中實施一次“事務(wù)日志”備份，并還原到備數(shù)據(jù)庫上。備份“事務(wù)日志”如圖所示：

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

成功還原以后再執(zhí)行建立鏡像的SQL語句。

四、測試操作

1、主備互換

--主機執(zhí)行：

USE master;
ALTER DATABASE DatabaseName> SET PARTNER FAILOVER;

2、主服務(wù)器Down掉,備機緊急啟動并且開始服務(wù)

--備機執(zhí)行：

USE master;
ALTER DATABASE DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

3、原來的主服務(wù)器恢復(fù),可以繼續(xù)工作,需要重新設(shè)定鏡像

4、原來的主服務(wù)器恢復(fù),可以繼續(xù)工作

--默認(rèn)情況下，事務(wù)安全級別的設(shè)置為 FULL，即同步運行模式，而且SQL Server 2005 標(biāo)準(zhǔn)版只支持同步模式。

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

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