SQL Server2008 R2 數(shù)據(jù)庫(kù)鏡像實(shí)施手冊(cè)(雙機(jī))SQL Server2014同樣適用

一、配置主備機(jī)

1、 服務(wù)器基本信息

主機(jī)名稱為：HOST_A，IP地址為：192.168.1.155

備機(jī)名稱為：HOST_B，IP地址為：192.168.1.156

二、主備實(shí)例互通

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

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

--主機(jī)執(zhí)行：
USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456'; 
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , 
START_DATE = '2012-08-02', 
EXPIRY_DATE = '2099-08-02'; 

--備機(jī)執(zhí)行：

USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456'; 
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 
START_DATE = '2012-08-02', 
EXPIRY_DATE = '2099-08-02'; 

2、創(chuàng)建連接的端點(diǎn)（主備可并行執(zhí)行）

--主機(jī)執(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 ); 

 

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

--主機(jī)執(zhí)行：

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


--備機(jī)執(zhí)行：

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

4、互換證書(shū)

將備份到C:\SQLBackup\的證書(shū)進(jìn)行互換，即HOST_A_cert.cer復(fù)制到備機(jī)的C:\SQLBackup\。HOST_B_cert.cer復(fù)制到主機(jī)的C:\SQLBackup\。

5、添加登陸名、用戶（主備可并行執(zhí)行）

以下操作只能通過(guò)命令行運(yùn)行，通過(guò)圖形界面無(wú)法完成。（截至SQL Server2005的補(bǔ)丁號(hào)為SP2）

--主機(jī)執(zhí)行：
CREATE LOGIN HOST_B_login WITH PASSWORD = '123456'; 
CREATE USER HOST_B_user FOR LOGIN HOST_B_login; 
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'C:\SQLBackup\HOST_B_cert.cer'; 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]; 
 
--備機(jī)執(zhí)行：
CREATE LOGIN HOST_A_login WITH PASSWORD = '123456'; 
CREATE USER HOST_A_user FOR LOGIN HOST_A_login; 
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'C:\SQLBackup\HOST_A_cert.cer'; 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; 

三、建立鏡像關(guān)系

以下步驟是針對(duì)每個(gè)數(shù)據(jù)庫(kù)進(jìn)行的，例如：現(xiàn)有主機(jī)中有5個(gè)數(shù)據(jù)庫(kù)以下過(guò)程就要執(zhí)行5次。

1、 手工同步登錄名和密碼

在上文中提到數(shù)據(jù)庫(kù)鏡像的缺點(diǎn)之一是無(wú)法維護(hù)登錄名，所以需要我們手工維護(hù)登錄。

通常來(lái)說(shuō)數(shù)據(jù)庫(kù)都將會(huì)有若干個(gè)用戶作為訪問(wèn)數(shù)據(jù)庫(kù)的用戶，并且數(shù)據(jù)庫(kù)會(huì)有相應(yīng)的登錄名，但是在備機(jī)中缺少與之相對(duì)應(yīng)的登錄名，例如某業(yè)務(wù)系統(tǒng)使用'myuser'作為登錄名訪問(wèn)數(shù)據(jù)庫(kù)，但是在備機(jī)中沒(méi)有'myuser'這個(gè)登錄名，因此一旦主備切換，業(yè)務(wù)系統(tǒng)就無(wú)法登錄數(shù)據(jù)庫(kù)了，這種情況稱為"孤立用戶"。在主機(jī)和備機(jī)數(shù)據(jù)庫(kù)上建立相同用戶名及密碼即可。

2、 準(zhǔn)備備機(jī)數(shù)據(jù)庫(kù)（主機(jī)備份及鏡像還原）

在主機(jī)上備份數(shù)據(jù)庫(kù)，先做完整備份，再做日志事務(wù)備份。

1、主數(shù)據(jù)必須設(shè)置成完整模式進(jìn)行備份，如下圖：

  上圖中將“恢復(fù)模式”選成“完整模式”。

2、備份數(shù)據(jù)庫(kù)，如下圖：

備份時(shí)將“備份類(lèi)型”選成“完整”。

3、備份事務(wù)日志，如下圖：

將“備份類(lèi)型”選成“事務(wù)日志”且備份目錄與備份數(shù)據(jù)庫(kù)的目錄一致。

將備份文件在備機(jī)上使用主機(jī)的全備文件進(jìn)行還原，在還原數(shù)據(jù)的時(shí)候需要使用選上“with non recover”。如圖所示：

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

3、 建立鏡像

--在備機(jī)中執(zhí)行如下語(yǔ)句：

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

說(shuō)明：shishan為數(shù)據(jù)庫(kù)名，需要根據(jù)實(shí)際進(jìn)行修改。192.168.1.155為主機(jī)IP地址，需根據(jù)實(shí)際進(jìn)行修改。

--主機(jī)執(zhí)行：

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

說(shuō)明：shishan為數(shù)據(jù)庫(kù)名，需要根據(jù)實(shí)際進(jìn)行修改。192.168.1.156為備機(jī)IP地址，需根據(jù)實(shí)際進(jìn)行修改。

執(zhí)行成功后：

到此，SQL鏡像熱備配置完成。

四、常見(jiàn)命令

--切換主備 
use master;
alter database testdb set partner failover;
 
-- 備機(jī)強(qiáng)制切換
use master;
alter database testdb set partner force_service_allow_data_loss;
 
--恢復(fù)鏡像
use master;
alter database testdb set partner resume;
 
 
--取消見(jiàn)證服務(wù)器
ALTER DATABASE testdb SET WITNESS OFF ;
 
--取消鏡像
ALTER DATABASE testdb SET PARTNER OFF;
 
--設(shè)置鏡像數(shù)據(jù)庫(kù)還原為正常
RESTORE DATABASE testdb WITH RECOVERY;

備份主數(shù)據(jù)庫(kù)出現(xiàn)：Backup a database on a HDD with a different sector size，可以執(zhí)行以下語(yǔ)句備份：
BACKUP DATABASE MyDB TO DISK = N'D:\MyDB.bak' WITH INIT , NOUNLOAD , NAME = N'MyDB backup', STATS = 10, FORMAT

總結(jié)

要進(jìn)行以上sql server的鏡像設(shè)置一定要使用sql server 的配置管理器開(kāi)啟TCP/IP協(xié)議，如下圖

如果沒(méi)有啟用TCP/IP協(xié)議則只能在同一個(gè)網(wǎng)段內(nèi)的機(jī)器配置鏡像，前面的配置步驟里面所用到的IP地址要換成對(duì)應(yīng)的實(shí)例名。同一個(gè)網(wǎng)段配置并使用鏡像的時(shí)實(shí)性、傳輸速率更高，適用于大數(shù)據(jù)量的同步，跨網(wǎng)段或者跨公網(wǎng)的sql server 鏡像一般適用于數(shù)據(jù)量小，時(shí)實(shí)性要求不高的數(shù)據(jù)同步，而且數(shù)據(jù)庫(kù)在公網(wǎng)上同步也不安全。