程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server鏡像功能完全實現

SQL Server鏡像功能完全實現

編輯:關於SqlServer

折騰SQL Server 鏡像搞了一天,終於有點成果,現在分享出來,之前按網上做的出了很多問題。現在盡量把所遇到的問題都分享出來。

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

環境:

主機:192.168.10.2 (代號A)

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

(條件有限我沒有搞見證服務器。)兩台服務器上的都是SQL Server 2005

首先配置主機

主機上執行以下SQL

  1. CREATE MASTER KEY ENCRYPTION BY PASSWord = 'passWord'
  2. GO 
  3. --在10.2上為數據庫實例創建證書 
  4. CREATE CERTIFICATE As_A_cert 
  5.    WITH SUBJECT = 'As_A_cert'
  6.   START_DATE = '09/02/2011'
  7.   EXPIRY_DATE = '01/01/2099'
  8. GO 
  9. --在10.2上使用上面創建的證書為數據庫實例創建鏡像端點 
  10. CREATE ENDPOINT Endpoint_As 
  11.    STATE = STARTED 
  12.    AS TCP ( 
  13.       LISTENER_PORT=5022, 
  14.       LISTENER_IP = ALL 
  15.    ) 
  16.    FOR DATABASE_MIRRORING ( 
  17.       AUTHENTICATION = CERTIFICATE As_A_cert, 
  18.       ENCRYPTION = REQUIRED ALGORITHM RC4, 
  19.       ROLE = ALL 
  20.    ); 
  21. GO 

注:這裡要注意設置數據庫的鏡像端口。5022.

  1. --備份10.2上的證書並拷貝到10.1上 
  2. BACKUP CERTIFICATE As_A_cert TO FILE = 'D:\As_A_cert.cer'
  3. GO 

注:備份證書A,並將證書A拷貝到鏡像服務器B上。

配置鏡像服務器

  1. USE master;  
  2. CREATE MASTER KEY ENCRYPTION BY PASSWord = 'passWord';  
  3. GO  
  4. --在10.1 B上為數據庫實例創建證書 
  5. CREATE CERTIFICATE As_B_cert  
  6.    WITH SUBJECT = 'As_B_cert'
  7.  START_DATE = '09/2/2011'
  8.  EXPIRY_DATE = '01/01/2099'
  9. GO  
  10. --在10.1 B上使用上面創建的證書為數據庫實例創建鏡像端點 
  11. CREATE ENDPOINT Endpoint_As  
  12.    STATE = STARTED  
  13.    AS TCP (  
  14.       LISTENER_PORT=5022  
  15.       , LISTENER_IP = ALL  
  16.    )  
  17.    FOR DATABASE_MIRRORING (  
  18.       AUTHENTICATION = CERTIFICATE As_B_cert  
  19.       , ENCRYPTION = REQUIRED ALGORITHM AES  
  20.       , ROLE = ALL  
  21.    );  
  22. GO  
  23. --備份10.1 B上的證書並拷貝到10.2 A上 
  24. BACKUP CERTIFICATE As_B_cert TO FILE = 'D:\As_B_cert.cer';  
  25. GO 

同樣將備份的證書B 拷貝到A服務器上。

建立用於鏡像登錄的賬戶

在A上執行

  1. --交換證書, 
  2. --同步 Login 
  3. CREATE LOGIN B_login WITH PASSWord = 'passWord';  
  4.  
  5. CREATE USER B_user FOR LOGIN B_login;  
  6.  
  7. CREATE CERTIFICATE As_B_cert AUTHORIZATION B_user FROM FILE = 'D:\As_B_cert.cer';  
  8.  
  9. GRANT CONNECT ON ENDPOINT::Endpoint_Bs TO [B_login]; 

在B上執行

  1. --交換證書, 
  2. --同步 Login 
  3. CREATE LOGIN A_login WITH PASSWord = 'passWord';  
  4.  
  5. CREATE USER A_user FOR LOGIN A_login;  
  6.  
  7. CREATE CERTIFICATE As_A_cert AUTHORIZATION A_user FROM FILE = 'D:\As_A_cert.cer';  
  8.  
  9. GRANT CONNECT ON ENDPOINT::Endpoint_As TO [A_login]; 

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

以後步驟執行沒問題,鏡像已經完成一半了。

接下來完整備份A服務器上的Test庫

  1. --主機執行完整備份 
  2. USE master; 
  3. ALTER DATABASE Test SET RECOVERY FULL
  4. GO 
  5. BACKUP DATABASE Test 
  6.   TO DISK = 'D:\SQLServerBackups\Test.bak'  
  7.   WITH FORMAT; 
  8. GO 
  9. BACKUP LOG Test TO DISK = 'D:\SQLServerBackups\Test.bak'
  10. GO 
  11. --將備份文件拷貝到B上。 

一定要執行完整備份。

在B服務器上完整還原數據庫

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

如果我們直接執行如下SQL.

  1. RESTORE DATABASE Test  
  2.     FROM DISK = 'D:\Back\Test.bak'  
  3.     WITH NORECOVERY  
  4. GO  
  5. RESTORE LOG Test  
  6.     FROM DISK = 'D:\Back\Test_log.bak'  
  7.     WITH FILE=1, NORECOVERY  
  8. GO 

可能會報:

消息 3154,級別 16,狀態 4,第 1 行
備份集中的數據庫備份與現有的 'Test’數據庫不同。
消息 3013,級別 16,狀態 1,第 1 行

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

用sp_addumpdevice來建立一個還原設備。這樣就保證了該備份文件是數據這個數據庫的。

  1. exec sp_addumpdevice 'disk','Test_backup'
  2.         'E:\backup\Test.bak' 
  3. exec sp_addumpdevice 'disk','Test_log_backup'
  4.         'E:\backup\Test_log.bak' 
  5. go 

成功之後我們來執行完成恢復

  1. RESTORE DATABASE Test  
  2.   FROM Test_backup 
  3.   WITH DBO_ONLY,  
  4.     NORECOVERY,STATS; 
  5. go 
  6. RESTORE LOG Test  
  7.   FROM Test_log_backup 
  8.   WITH file=1, 
  9.     NORECOVERY; 
  10. GO 

這裡如果之前備份過多次數據庫的話,肯會產生多個備份集。所以這裡的 file就不能指定為1了。

這個錯誤可能是:

消息 4326,級別 16,狀態 1,第 1 行
此備份集中的日志終止於 LSN 36000000014300001,該 LSN 太早,無法應用到數據庫

。可以還原包含 LSN 36000000018400001 的較新的日志備份。

可以通過這條語句來查詢該備份文件的備份集

restore   headeronly   from   disk   =   'E:\backup\Test_log.bak'

找到最後一個的序號指定給file就可以。

還需要注意的是第一次完整恢復的時候需要指定NORECOVERY。

至此所有准備工作都已經完成我們開啟鏡像了

先在鏡像服務器上執行

  1. ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022'

成功之後再在主機上執行

  1. ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022'

這樣兩台服務器的鏡像就同步了。

刪除鏡像:

  1. ALTER DATABASE Test SET PARTNER OFF 

如果主機出現問題,在主機執行

  1. USE MASTER  
  2.  
  3. Go  
  4.  
  5. ALTER DATABASE Test SET PARTNER FAILOVER  
  6.  
  7. Go 

總結:

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

  1. --查詢鏡像 
  2. select * from sys.endpoints 
  3. --刪除端口 
  4. drop endpoint Endpoint_As 
  5. --查詢證書 
  6. select * from sys.symmetric_keys 
  7. --刪除證書,先刪除證書再刪除主鍵 
  8. DROP CERTIFICATE As_A_cert 
  9. --刪除主鍵 
  10. DROP MASTER KEY    
  11. --刪除鏡像 
  12. alter database <dbname> set partner off    
  13. --刪除登錄名 
  14. drop login <login_name> 

sp_addumpdevice 的語法

  1. sp_addumpdevice [ @devtype = ] 'device_type' 
  2.  
  3.         , [ @logicalname = ] 'logical_name' 
  4.  
  5.         , [ @physicalname = ] 'physical_name' 
  6.  
  7.       ] 
  8. 其中參數有: 
  9. @devtype:設備類型,可以支持的值為disk和tape,其中disk為磁盤文件;tape為 
  10. Windows支持的任何磁帶設備。 
  11. @logicalname:備份設備的邏輯名稱,設備名稱。 
  12. @physicalname:備份設備的物理名稱,路徑 
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved