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 行
备份集中的数据库备份与现有的 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  set partner off    
--删除登录名 
drop login  

　　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