SQL Server镜像功能完全实现

折腾SQL Server 镜像搞了一天，终于有点成果，现在分享出来，之前按网上做的出了很多问题。现在尽量把所遇到的问题都分享出来。

成都创新互联从2013年创立，先为肃州等服务建站，肃州等地企业，进行企业商务咨询服务。为肃州企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

在域环境下我没配置成果，也许是域用户的原因，因为我在生产环境下搞的，更改域用户需要重启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：备份设备的物理名称，路径

文章题目：SQL Server镜像功能完全实现
文章路径：http://www.shufengxianlan.com/qtweb/news49/542499.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容