折騰SQL Server 鏡像搞了一天,終於有點成果,現在分享出來,之前按網上做的出了很多問題。現在盡量把所遇到的問題都分享出來。
在域環境下我沒配置成果,也許是域用戶的原因,因為我在生產環境下搞的,更改域用戶需要重啟SQL Server ,所以這個方法放棄了,只能用證書形式。
環境:
主機:192.168.10.2 (代號A)
鏡像:192.168.10.1 (代號B,為了一會說明方便)
(條件有限我沒有搞見證服務器。)兩台服務器上的都是SQL Server 2005
首先配置主機
主機上執行以下SQL
- CREATE MASTER KEY ENCRYPTION BY PASSWord = 'passWord';
- GO
- --在10.2上為數據庫實例創建證書
- CREATE CERTIFICATE As_A_cert
- WITH SUBJECT = 'As_A_cert',
- START_DATE = '09/02/2011',
- EXPIRY_DATE = '01/01/2099';
- GO
- --在10.2上使用上面創建的證書為數據庫實例創建鏡像端點
- 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
注:這裡要注意設置數據庫的鏡像端口。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上為數據庫實例創建證書
- CREATE CERTIFICATE As_B_cert
- WITH SUBJECT = 'As_B_cert',
- START_DATE = '09/2/2011',
- EXPIRY_DATE = '01/01/2099';
- GO
- --在10.1 B上使用上面創建的證書為數據庫實例創建鏡像端點
- 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上執行
- --交換證書,
- --同步 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上執行
- --交換證書,
- --同步 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是不被占用的,並且保證兩個服務器可以連接
以後步驟執行沒問題,鏡像已經完成一半了。
接下來完整備份A服務器上的Test庫
- --主機執行完整備份
- 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上。
一定要執行完整備份。
在B服務器上完整還原數據庫
這裡問題多多。一個一個說。
如果我們直接執行如下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
可能會報:
消息 3154,級別 16,狀態 4,第 1 行
備份集中的數據庫備份與現有的 'Test’數據庫不同。
消息 3013,級別 16,狀態 1,第 1 行
可能是兩個數據庫的備份集名稱不同導致,找了半天原因未果,所以采用下面sp_addumpdevice方法來做。
用sp_addumpdevice來建立一個還原設備。這樣就保證了該備份文件是數據這個數據庫的。
- exec sp_addumpdevice 'disk','Test_backup',
- 'E:\backup\Test.bak'
- exec sp_addumpdevice 'disk','Test_log_backup',
- 'E:\backup\Test_log.bak'
- go
成功之後我們來執行完成恢復
- RESTORE DATABASE Test
- FROM Test_backup
- WITH DBO_ONLY,
- NORECOVERY,STATS;
- go
- RESTORE LOG Test
- FROM Test_log_backup
- WITH file=1,
- NORECOVERY;
- GO
這裡如果之前備份過多次數據庫的話,肯會產生多個備份集。所以這裡的 file就不能指定為1了。
這個錯誤可能是:
消息 4326,級別 16,狀態 1,第 1 行
此備份集中的日志終止於 LSN 36000000014300001,該 LSN 太早,無法應用到數據庫
。可以還原包含 LSN 36000000018400001 的較新的日志備份。
可以通過這條語句來查詢該備份文件的備份集
restore headeronly from disk = 'E:\backup\Test_log.bak'
找到最後一個的序號指定給file就可以。
還需要注意的是第一次完整恢復的時候需要指定NORECOVERY。
至此所有准備工作都已經完成我們開啟鏡像了
先在鏡像服務器上執行
- ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022';
成功之後再在主機上執行
- ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022';
這樣兩台服務器的鏡像就同步了。
刪除鏡像:
- ALTER DATABASE Test SET PARTNER OFF
如果主機出現問題,在主機執行
- USE MASTER
- Go
- ALTER DATABASE Test SET PARTNER FAILOVER
- Go
總結:
如果在建立鏡像的時候中間的那個步驟出問題,需要重新執行的時候一定要把該刪得東西刪除掉。
- --查詢鏡像
- 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 的語法
- sp_addumpdevice [ @devtype = ] 'device_type'
- , [ @logicalname = ] 'logical_name'
- , [ @physicalname = ] 'physical_name'
- ]
- 其中參數有:
- @devtype:設備類型,可以支持的值為disk和tape,其中disk為磁盤文件;tape為
- Windows支持的任何磁帶設備。
- @logicalname:備份設備的邏輯名稱,設備名稱。
- @physicalname:備份設備的物理名稱,路徑