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

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

環(huán)境：

主機:192.168.10.2 (代號A)

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

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

首先配置主機

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'; 
GO 
--在10.2上為數據庫實例創(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)建的證書為數據庫實例創(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 

注：這里要注意設置數據庫的鏡像端口。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上為數據庫實例創(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)建的證書為數據庫實例創(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í)行沒問題，鏡像已經完成一半了。

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

--主機執(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服務器上完整還原數據庫

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

如果我們直接執(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 

可能會報：

消息 3154，級別 16，狀態(tài) 4，第 1 行
備份集中的數據庫備份與現有的 'Test’數據庫不同。
消息 3013，級別 16，狀態(tài) 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 

成功之后我們來執(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 

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

這個錯誤可能是：

消息 4326，級別 16，狀態(tài) 1，第 1 行
此備份集中的日志終止于 LSN 36000000014300001，該 LSN 太早，無法應用到數據庫

?？梢赃€原包含 LSN 36000000018400001 的較新的日志備份。

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

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

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

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

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

先在鏡像服務器上執(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 

如果主機出現問題，在主機執(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 <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：備份設備的物理名稱，路徑 

原文鏈接：http://www.cnblogs.com/xiaogangqq123/archive/2011/09/05/2167846.html

【編輯推薦】

1. 數據挖掘中易犯的幾大錯誤
2. 整理索引碎片，提升SQL Server速度
3. 大數據平臺：探索數據價值
4. Big Data技術綜述
5. SQL Server引入Hadoop大數據處理能力