SQL Server鏡像功能完全實(shí)現(xiàn)

字號(hào):


    折騰SQL Server 鏡像搞了一天,終于有點(diǎn)成果,現(xiàn)在分享出來(lái),之前按網(wǎng)上做的出了很多問(wèn)題。現(xiàn)在盡量把所遇到的問(wèn)題都分享出來(lái)。
    在域環(huán)境下我沒(méi)配置成果,也許是域用戶的原因,因?yàn)槲以谏a(chǎn)環(huán)境下搞的,更改域用戶需要重啟SQL Server ,所以這個(gè)方法放棄了,只能用證書形式。
    環(huán)境:
    主機(jī):192.168.10.2 (代號(hào)A)
    鏡像:192.168.10.1 (代號(hào)B,為了一會(huì)說(shuō)明方便)
    (條件有限我沒(méi)有搞見證服務(wù)器。)兩臺(tái)服務(wù)器上的都是SQL Server 2005
    首先配置主機(jī)
    主機(jī)上執(zhí)行以下SQL
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
    GO
    --在10.2上為數(shù)據(jù)庫(kù)實(shí)例創(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ù)庫(kù)實(shí)例創(chuàng)建鏡像端點(diǎn)
    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è)置數(shù)據(jù)庫(kù)的鏡像端口。5022.
    --備份10.2上的證書并拷貝到10.1上
    BACKUP CERTIFICATE As_A_cert TO FILE = 'D:\As_A_cert.cer';
    GO
    注:備份證書A,并將證書A拷貝到鏡像服務(wù)器B上。
    配置鏡像服務(wù)器
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
    GO
    --在10.1 B上為數(shù)據(jù)庫(kù)實(shí)例創(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ù)庫(kù)實(shí)例創(chuàng)建鏡像端點(diǎn)
    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服務(wù)器上。
    建立用于鏡像登錄的賬戶
    在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];
    記得兩臺(tái)服務(wù)器的端口5022是不被占用的,并且保證兩個(gè)服務(wù)器可以連接
    以后步驟執(zhí)行沒(méi)問(wèn)題,鏡像已經(jīng)完成一半了。
    接下來(lái)完整備份A服務(wù)器上的Test庫(kù)
    --主機(jī)執(zhí)行完整備份
    USE master;
    ALTER DATABASE Test SET RECOVERY FULL;
    GO
    BACKUP DATABASE Test
    TO DISK = 'D:\SQLServerBackups\Test.bak'
    WITH FORMAT;
    GO
    BACKUP LOG Test TO DISK = 'D:\SQLServerBackups\Test.bak';
    GO
    --將備份文件拷貝到B上。
    一定要執(zhí)行完整備份。
    在B服務(wù)器上完整還原數(shù)據(jù)庫(kù)
    這里問(wèn)題多多。一個(gè)一個(gè)說(shuō)。
    如果我們直接執(zhí)行如下SQL.
    RESTORE DATABASE Test
    FROM DISK = 'D:\Back\Test.bak'
    WITH NORECOVERY
    GO
    RESTORE LOG Test
    FROM DISK = 'D:\Back\Test_log.bak'
    WITH FILE=1, NORECOVERY
    GO
    可能會(huì)報(bào):
    消息 3154,級(jí)別 16,狀態(tài) 4,第 1 行
    備份集中的數(shù)據(jù)庫(kù)備份與現(xiàn)有的 'Test’數(shù)據(jù)庫(kù)不同。
    消息 3013,級(jí)別 16,狀態(tài) 1,第 1 行
    可能是兩個(gè)數(shù)據(jù)庫(kù)的備份集名稱不同導(dǎo)致,找了半天原因未果,所以采用下面sp_addumpdevice方法來(lái)做。
    用sp_addumpdevice來(lái)建立一個(gè)還原設(shè)備。這樣就保證了該備份文件是數(shù)據(jù)這個(gè)數(shù)據(jù)庫(kù)的。
    exec sp_addumpdevice 'disk','Test_backup',
    'E:\backup\Test.bak'
    exec sp_addumpdevice 'disk','Test_log_backup',
    'E:\backup\Test_log.bak'
    go
    成功之后我們來(lái)執(zhí)行完成恢復(fù)
    RESTORE DATABASE Test
    FROM Test_backup
    WITH DBO_ONLY,
    NORECOVERY,STATS;
    go
    RESTORE LOG Test
    FROM Test_log_backup
    WITH file=1,
    NORECOVERY;
    GO
    這里如果之前備份過(guò)多次數(shù)據(jù)庫(kù)的話,肯會(huì)產(chǎn)生多個(gè)備份集。所以這里的 file就不能指定為1了。
    這個(gè)錯(cuò)誤可能是:
    消息 4326,級(jí)別 16,狀態(tài) 1,第 1 行
    此備份集中的日志終止于 LSN 36000000014300001,該 LSN 太早,無(wú)法應(yīng)用到數(shù)據(jù)庫(kù)
    ??梢赃€原包含 LSN 36000000018400001 的較新的日志備份。
    可以通過(guò)這條語(yǔ)句來(lái)查詢?cè)搨浞菸募膫浞菁?BR>    restore headeronly from disk = 'E:\backup\Test_log.bak'
    找到最后一個(gè)的序號(hào)指定給file就可以。
    還需要注意的是第一次完整恢復(fù)的時(shí)候需要指定NORECOVERY。
    至此所有準(zhǔn)備工作都已經(jīng)完成我們開啟鏡像了
    先在鏡像服務(wù)器上執(zhí)行
    ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022';
    成功之后再在主機(jī)上執(zhí)行
    ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022';
    這樣兩臺(tái)服務(wù)器的鏡像就同步了。
    名單
    刪除鏡像:
    ALTER DATABASE Test SET PARTNER OFF
    如果主機(jī)出現(xiàn)問(wèn)題,在主機(jī)執(zhí)行
    USE MASTER
    Go
    ALTER DATABASE Test SET PARTNER FAILOVER
    Go
    總結(jié):
    如果在建立鏡像的時(shí)候中間的那個(gè)步驟出問(wèn)題,需要重新執(zhí)行的時(shí)候一定要把該刪得東西刪除掉。
    --查詢鏡像
    select * from sys.endpoints
    --刪除端口
    drop endpoint Endpoint_As
    --查詢證書
    select * from sys.symmetric_keys
    --刪除證書,先刪除證書再刪除主鍵
    DROP CERTIFICATE As_A_cert
    --刪除主鍵
    DROP MASTER KEY
    --刪除鏡像
    alter database <dbname> set partner off
    --刪除登錄名
    drop login <login_name>
    sp_addumpdevice 的語(yǔ)法
    sp_addumpdevice [ @devtype = ] 'device_type'
    , [ @logicalname = ] 'logical_name'
    , [ @physicalname = ] 'physical_name'
    ]
    其中參數(shù)有:
    @devtype:設(shè)備類型,可以支持的值為disk和tape,其中disk為磁盤文件;tape為
    windows支持的任何磁帶設(shè)備。
    @logicalname:備份設(shè)備的邏輯名稱,設(shè)備名稱。
    @physicalname:備份設(shè)備的物理名稱,路徑