SQLServer 鏡像功能完全實現(xiàn)

折騰sqlserver 鏡像搞了一天,終于有點成果,現(xiàn)在分享出來,之前按網(wǎng)上做的出了很多問題。現(xiàn)在盡量把所遇到的問題都分享出來。

在域環(huán)境下我沒配置成果,也許是域用戶的原因,因為我在生產(chǎn)環(huán)境下搞的,更改域用戶需要重啟SQLServer ,所以這個方法放棄了,只能用證書形式。

環(huán)境:

主機:192.168.10.2 (代號A)

鏡像:192.168.10.1 (代號B,為了一會說明方便)

(條件有限我沒有搞見證服務器。)兩臺服務器上的都是SQLServer2005

首先配置主機

主機上執(zhí)行以下SQL

代碼如下:
–創(chuàng)建主機數(shù)據(jù)庫主密鑰
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password’;
GO
–在10.2上為數(shù)據(jù)庫實例創(chuàng)建證書
CREATE CERTIFICATE As_A_cert
WITH SUBJECT = ‘As_A_cert’,
START_DATE = ’09/02/2011′,
EXPIRY_DATE = ’01/01/2099′;
GO
–在10.2上使用上面創(chuàng)建的證書為數(shù)據(jù)庫實例創(chuàng)建鏡像端點
CREATE ENDPOINT Endpoint_As
STATE = STARTED
AS TCP (
LISTENER_PORT=5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE As_A_cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
);
GO

注:這里要注意設置數(shù)據(jù)庫的鏡像端口。5022.

–備份10.2上的證書并拷貝到10.1上
BACKUP CERTIFICATE As_A_cert TO FILE = ‘D:As_A_cert.cer’;
GO
注:備份證書A,并將證書A拷貝到鏡像服務器B上。

配置鏡像服務器
代碼如下:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password’;
GO
–在10.1 B上為數(shù)據(jù)庫實例創(chuàng)建證書
CREATE CERTIFICATE As_B_cert
WITH SUBJECT = ‘As_B_cert’,
START_DATE = ’09/2/2011′,
EXPIRY_DATE = ’01/01/2099′;
GO
–在10.1 B上使用上面創(chuàng)建的證書為數(shù)據(jù)庫實例創(chuàng)建鏡像端點
CREATE ENDPOINT Endpoint_As
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE As_B_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

–備份10.1 B上的證書并拷貝到10.2 A上
BACKUP CERTIFICATE As_B_cert TO FILE = ‘D:As_B_cert.cer’;
GO
同樣將備份的證書B 拷貝到A服務器上。

建立用于鏡像登錄的賬戶

在A上執(zhí)行

–交換證書,
–同步 Login
代碼如下:
CREATE LOGIN B_login WITH PASSWORD = ‘password’;

CREATE USER B_user FOR LOGIN B_login;

CREATE CERTIFICATE As_B_cert AUTHORIZATION B_user FROM FILE = ‘D:As_B_cert.cer’;

GRANT CONNECT ON ENDPOINT::Endpoint_Bs TO [B_login];

在B上執(zhí)行
代碼如下:
–交換證書,
–同步 Login
CREATE LOGIN A_login WITH PASSWORD = ‘password’;

CREATE USER A_user FOR LOGIN A_login;

CREATE CERTIFICATE As_A_cert AUTHORIZATION A_user FROM FILE = ‘D:As_A_cert.cer’;

GRANT CONNECT ON ENDPOINT::Endpoint_As TO [A_login];

記得兩臺服務器的端口5022是不被占用的,并且保證兩個服務器可以連接

以后步驟執(zhí)行沒問題,鏡像已經(jīng)完成一半了。

接下來完整備份A服務器上的Test庫
代碼如下:
–主機執(zhí)行完整備份
USE master;
ALTER DATABASE Test SET RECOVERY FULL;
GO
BACKUP DATABASE Test
TO DISK = ‘D:SQLServerBackupsTest.bak’
WITH FORMAT;
GO
BACKUP LOG Test TO DISK = ‘D:SQLServerBackupsTest.bak’;
GO

–將備份文件拷貝到B上。
一定要執(zhí)行完整備份。

在B服務器上完整歡迎數(shù)據(jù)庫

這里問題多多。一個一個說。

如果我們直接執(zhí)行如下SQL.

代碼如下:
RESTORE DATABASE Test
FROM DISK = ‘D:BackTest.bak’
WITH NORECOVERY
GO
RESTORE LOG Test
FROM DISK = ‘D:BackTest_log.bak’
WITH FILE=1, NORECOVERY
GO
[code]
可能會報:

消息 3154,級別 16,狀態(tài) 4,第 1 行
備份集中的數(shù)據(jù)庫備份與現(xiàn)有的 ‘Test’數(shù)據(jù)庫不同。
消息 3013,級別 16,狀態(tài) 1,第 1 行

可能是兩個數(shù)據(jù)庫的備份集名稱不同導致,找了半天原因未果,所以采用下面sp_addumpdevice方法來做。

用sp_addumpdevice來建立一個還原的設備。這樣就保證了改備份文件是數(shù)據(jù)這個數(shù)據(jù)庫的。
[code]
exec sp_addumpdevice ‘disk’,’Test_backup’,
‘E:backupTest.bak’
exec sp_addumpdevice ‘disk’,’Test_log_backup’,
‘E:backupTest_log.bak’
go

成功之后我們來執(zhí)行完成恢復
代碼如下:
RESTORE DATABASE Test
FROM Test_backup
WITH DBO_ONLY,
NORECOVERY,STATS;
go
RESTORE LOG Test
FROM Test_log_backup
WITH file=1,
NORECOVERY;
GO

這里如果之前備份過多次數(shù)據(jù)庫的話,肯會產(chǎn)生多個備份集。所以這里的 file就不能指定為1了。

這個錯誤可能是:
消息 4326,級別 16,狀態(tài) 1,第 1 行
此備份集中的日志終止于 LSN 36000000014300001,該 LSN 太早,無法應用到數(shù)據(jù)庫
。可以還原包含 LSN 36000000018400001 的較新的日志備份。
可以通過這句話來查詢該備份文件的備份集
restore headeronly from disk = ‘E:backupTest_log.bak’
找到最后一個的序號就指定給file就可以。
還需要注意的是第一次完整恢復的時候需要指定NORECOVERY。
至此所有準備工作都已經(jīng)完成我們開始執(zhí)行鏡像
先在鏡像服務器上執(zhí)行
ALTER DATABASE Test SET PARTNER = ‘TCP://192.168.10.2:5022’;
成功之后再在主機上執(zhí)行
ALTER DATABASE Test SET PARTNER = ‘TCP://192.168.10.2:5022’;
這樣兩臺服務器的鏡像就同步了。

刪除鏡像:

ALTER DATABASE Test SET PARTNER OFF

如果主機出現(xiàn)問題,在主機執(zhí)行
代碼如下:
USE MASTER
Go
ALTER DATABASE Test SET PARTNER FAILOVER
Go

總結:

如果在建立鏡像的時候中間的那個步驟出國,需要重新執(zhí)行的時候一定要把該刪得東西刪除掉。

–查詢鏡像
select * from sys.endpoints
–刪除端口
drop endpoint Endpoint_As
–查詢證書
select * from sys.symmetric_keys
–刪除證書,先刪除證書再刪除主鍵
DROP CERTIFICATE As_A_cert
–刪除主鍵
DROP MASTER KEY
–刪除鏡像
alter database set partner off
–刪除登錄名
drop login
sp_addumpdevice 的語法

代碼如下:
sp_addumpdevice [ @devtype = ] ‘device_type’
, [ @logicalname = ] ‘logical_name’
, [ @physicalname = ] ‘physical_name’
]
其中參數(shù)有:
@devtype:設備類型,可以支持的值為disk和tape,其中disk為磁盤文件;tape為
windows支持的任何磁帶設備。
@logicalname:備份設備的邏輯名稱,設備名稱。
@physicalname:備份設備的物理名稱,路徑

參考:

? 版權聲明
THE END
喜歡就支持一下吧
點贊10 分享