SQL Server鏡像功能完全實現

折騰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 行
備份組中的Database Backup與現有的 '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：備份裝置的實體名稱，路徑