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：备份设备的物理名称，路径
  

※清风※

镜像没搞过  暂时不知怎么用

jetty2012

看了，自己试下，还是有点不清楚。

taotao420

好强大的样子

宇智波·佐助

好东西没有人回复可惜了