一 概述

數(shù)據(jù)庫(kù)鏡像是SQL SERVER 2005用于提高數(shù)據(jù)庫(kù)可用性的新技術(shù)。數(shù)據(jù)庫(kù)鏡像將事務(wù)日志記錄直接從一臺(tái)服務(wù)器傳輸?shù)搅硪慌_(tái)服務(wù)器，并且能夠在出現(xiàn)故障時(shí)快速轉(zhuǎn)移到備用服務(wù)器?？梢跃帉?xiě)客戶端程序自動(dòng)重定向連接信息，這樣一旦出現(xiàn)故障轉(zhuǎn)移就可以自動(dòng)連接到備用服務(wù)器和數(shù)據(jù)庫(kù)。

優(yōu)勢(shì):數(shù)據(jù)庫(kù)鏡像可以在不丟失已提交數(shù)據(jù)的前提下進(jìn)行快速故障轉(zhuǎn)移，無(wú)須專門(mén)的硬件，并且易于配置和管理。

二 環(huán)境準(zhǔn)備

操作系統(tǒng):Window 2003 enterprise sp2(至少兩臺(tái),如要啟用自動(dòng)故障轉(zhuǎn)移,必需三臺(tái))

SQL版本:MSSQL SERVER 2005 SP3

檢查SQL SERVER版本:

exec xp_msver

select SERVERPROPERTY('productlevel')

數(shù)據(jù)庫(kù)準(zhǔn)備:準(zhǔn)備一個(gè)數(shù)據(jù)庫(kù):ccerp_jzt ,備份此數(shù)據(jù)庫(kù)還原到另外一臺(tái)機(jī)器上,另外一臺(tái)必須是with no recovery

這里我假設(shè)服務(wù)器A,B,C

A為主體服務(wù)器,B為鏡像服務(wù)器,C為見(jiàn)證服務(wù)器

A服務(wù)器

use master

go

restore filelistonly from disk=N'f:\databak\ccerp_jzt_backup_200911250100.bak'

restore database ccerp_jzt from disk=N'f:\databak\ccerp_jzt_backup_200911250100.bak' with replace,recovery,

move 'ccerp_ydswzip_Data' to 'd:\data\ccerp_jzt.mdf',

move 'ccerp_ydswzip_Log' to 'd:\data\ccerp_jzt_log.ldf'

exec sp_helpdb 'ccerp_jzt'

backup database ccerp_jzt to disk =N'f:\databak\sk.bak' with init

--更改恢復(fù)模式

alter database ccerp_jzt set recovery full

B服務(wù)器:

CREATE DATABASE ccerp_jzt

ON

( NAME = Sales_dat,

FILENAME = 'd:\data\ccerp_jzt.mdf',

SIZE = 10

)

LOG ON

( NAME = 'ccerp_jzt_log',

FILENAME = 'd:\data\ccerp_jzt_log.ldf',

SIZE = 5MB

)

GO

restore filelistonly from disk=N'f:\xxzx\data\sk.bak'

use master

go

restore database ccerp_jzt from disk=N'f:\xxzx\data\sk.bak' with replace,norecovery,

exec sp_helpdb 'ccerp_jzt'

C服務(wù)器只要裝上SQL SERVER 2005就可以,無(wú)需其他準(zhǔn)備

準(zhǔn)備完成后如下圖所示：

三 三種模式的搭建

數(shù)據(jù)庫(kù)鏡像要建立必需得建立信任關(guān)系,那么在WIN環(huán)境下建立信任關(guān)系可以通過(guò)三種方式:域帳戶,證書(shū)信任,windows 匿名登陸,現(xiàn)就前兩種模式做配置說(shuō)明.

3.1 域帳戶模式:

3.1.1 更改mssqlserver服務(wù)的的登陸方式為域帳戶登陸方式:

進(jìn)入windows服務(wù)管理控制臺(tái),更改服務(wù)登陸帳戶,使域賬戶有更改MSSQL SERVER服務(wù)狀態(tài)的權(quán)限．三臺(tái)機(jī)器都做同樣設(shè)置

將域帳戶賦予sysadmin角色

3.1.2 建立端點(diǎn):

通過(guò)圖形界面建立端點(diǎn)：

啟動(dòng)SQLWB，按圖一直下一步

用域帳戶登陸

3.2 證書(shū)模式

3.2.1建立證書(shū)端點(diǎn)

參與數(shù)據(jù)庫(kù)鏡像會(huì)話的服務(wù)器必須彼此信任。對(duì)于本地通信而言，例如一個(gè)域內(nèi)的通信，信任意味著SQL Server實(shí)例登陸賬號(hào)必須有權(quán)限連接到其他鏡像服務(wù)器，也包括endpoints。首先在每個(gè)服務(wù)器上使用CREATE LOGIN命令，然后使用GRANT CONNECT ON ENDPOINT命令．非信任域之間的通信必須使用證書(shū)。如果使用CREATE CERTIFICATE語(yǔ)句創(chuàng)建自簽名的證書(shū)，基本上所有數(shù)據(jù)鏡像證書(shū)的要求都可以滿足。確認(rèn)在CREATE CERTIFICATE語(yǔ)句中將證書(shū)標(biāo)記為ACTIVE FOR BEGIN_DIALOG。

一　建立證書(shū)：

鏡像服務(wù)器上執(zhí)行：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST';

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT='HOST_A certificate', START_DATE='2010-03-10';

主體服務(wù)器上執(zhí)行：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST';

CREATE CERTIFICATE HOST_B_cert WITH SUBJECT='HOST_B certificate', START_DATE='2010-03-10';

見(jiàn)證服務(wù)器上執(zhí)行：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST';

CREATE CERTIFICATE HOST_C_cert WITH SUBJECT='HOST_C certificate', START_DATE='2010-03-10';

二　建立端點(diǎn)：

鏡像服務(wù)器上執(zhí)行：

--create mirror endpoint on primary A

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

主體服務(wù)器上執(zhí)行：

--Create endpoint on mirror server B

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

見(jiàn)證服務(wù)器上執(zhí)行：

--Create endpoint on witness server C

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE HOST_C_cert ,

ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = witness );

SELECT * FROM sys.database_mirroring_endpoints;

證書(shū)互備：

鏡像服務(wù)器上執(zhí)行：

--backup certificate

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'e:\HOST_A_cert.cer'

主體服務(wù)器上執(zhí)行

--backup certificate

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'e:\HOST_B_cert.cer'

見(jiàn)證服務(wù)器上執(zhí)行：

BACKUP CERTIFICATE HOST_c_cert TO FILE = 'e:\HOST_C_cert.cer'

將備份到的證書(shū)進(jìn)行互換，即HOST_A_cert.cer復(fù)制到B機(jī)的e:\ 將HOST_B_cert.cer復(fù)制到A機(jī)的E:\,也就是每臺(tái)服務(wù)器有三個(gè)證書(shū)

三：建立登陸用戶：

鏡像服務(wù)器上執(zhí)行：

--Create user

CREATE LOGIN HOST_B_login WITH PASSWORD = 'test';

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'e:\HOST_B_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

CREATE LOGIN HOST_C_login WITH PASSWORD = 'test';

CREATE USER HOST_C_user FOR LOGIN HOST_c_login;

CREATE CERTIFICATE HOST_c_cert AUTHORIZATION HOST_c_user FROM FILE = 'e:\HOST_c_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_c_login];

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

--query user sid

select loginname,name,sid From syslogins

主體服務(wù)器上執(zhí)行：

--Create user

CREATE LOGIN HOST_A_login WITH PASSWORD = 'test';

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'e:\HOST_A_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

-- add witness user

CREATE LOGIN HOST_C_login WITH PASSWORD = 'test';

CREATE USER HOST_C_user FOR LOGIN HOST_c_login;

CREATE CERTIFICATE HOST_c_cert AUTHORIZATION HOST_c_user FROM FILE = 'e:\HOST_c_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_c_login];

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

--query sid

select loginname,name,sid From syslogins

見(jiàn)證服務(wù)器上執(zhí)行：

--Create user

CREATE LOGIN HOST_A_login WITH PASSWORD = 'test';

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'e:\HOST_A_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

--add user host_b_login to have pemission to access witness

CREATE LOGIN HOST_B_login WITH PASSWORD = 'test';

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'e:\HOST_B_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

grant connect on endpoint::endpoint_mirroring to HOST_C_login

USE master;

exec sp_addlogin

@loginame = 'HOST_B_login',

@passwd = 'test',

@sid = 0x1A914CA3D1D00C4793EBC96E4C4F4352 ;

ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.32:5022';

四．建立鏡像：

先在鏡像服務(wù)器上執(zhí)行：

ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.44:5022';

接著主體服務(wù)器執(zhí)行：

ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.32:5022';

ALTER DATABASE ccerp_jzt SET witness = 'TCP://192.168.137.49:5022';

至此引證書(shū)建立完畢

四、測(cè)試操作

1、主備互換

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

1USE master;
2ALTER DATABASE DatabaseName> SET PARTNER FAILOVER;

2、主服務(wù)器Down掉,備機(jī)緊急啟動(dòng)并且開(kāi)始服務(wù)

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

1USE master;
2ALTER DATABASE DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS; 

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

1--備機(jī)執(zhí)行：
2USE master;
3ALTER DATABASE DatabaseName> SET PARTNER RESUME; --恢復(fù)鏡像
4ALTER DATABASE DatabaseName> SET PARTNER FAILOVER; --切換主備 

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

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

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

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

錯(cuò)誤說(shuō)明：
消息1498，級(jí)別16，狀態(tài)3，第1 行

默認(rèn)情況下，數(shù)據(jù)庫(kù)鏡像是被禁用的。當(dāng)前提供的數(shù)據(jù)庫(kù)鏡像僅供評(píng)估使用，并不應(yīng)使用于生產(chǎn)環(huán)境中。若要以評(píng)估為目的啟用數(shù)據(jù)庫(kù)鏡像，請(qǐng)?jiān)趩?dòng)過(guò)程中使用跟蹤標(biāo)志1400。有關(guān)跟蹤標(biāo)志和啟動(dòng)選項(xiàng)的詳細(xì)信息，請(qǐng)參閱SQL Server 聯(lián)機(jī)叢書(shū)。

解決辦法：沒(méi)打SP1以上補(bǔ)丁．強(qiáng)烈建議打SP3

消息1475，級(jí)別16，狀態(tài)2，第1 行

由于"ccerp_jzt" 數(shù)據(jù)庫(kù)可能有尚未備份的大容量日志記錄更改，所以無(wú)法啟用數(shù)據(jù)庫(kù)鏡像。必須在鏡像上還原主體數(shù)據(jù)庫(kù)的上一次日志備份。

主體上：backup log ccerp_jzt to disk ='e:\log.trn' with no_truncate

鏡像上：restore log ccerp_jzt from disk='e:\log.trn' with norecovery