SQL Server镜像功能完全实现_SQL Server_数据库_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 数据库 > SQL Server > SQL Server镜像功能完全实现

SQL Server镜像功能完全实现

 2016/5/12 5:31:24    程序员俱乐部  我要评论(0)
  • 摘要:折腾SQLServer镜像搞了一天,终于有点成果,现在分享出来,之前按网上做的出了很多问题。现在尽量把所遇到的问题都分享出来。在域环境下我没配置成果,也许是域用户的原因,因为我在生产环境下搞的,更改域用户需要重启SQLServer,所以这个方法放弃了,只能用证书形式。环境:主机:192.168.10.2(代号A)镜像:192.168.10.1(代号B,为了一会说明方便)(条件有限我没有搞见证服务器。
  • 标签:Server 功能 实现 SQL

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

在域环境下我没配置成果,也许是域用户的原因,因为我在生产环境下搞的,更改域用户需要重启SQL Server ,所以这个方法放弃了,只能用证书形式。

环境:

主机:192.168.10.2 (代号A)

镜像:192.168.10.1 (代号B,为了一会说明方便)

(条件有限我没有搞见证服务器。)两台服务器上的都是SQL Server 2005

首先配置主机

主机上执行以下SQL

    class="dp-sql">
  1. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
  2. GO 
  3. --在10.2上为数据库实例创建证书 
  4. CREATE CERTIFICATE As_A_cert 
  5.    WITH SUBJECT = 'As_A_cert'
  6.   START_DATE = '09/02/2011'
  7.   EXPIRY_DATE = '01/01/2099'
  8. GO 
  9. --在10.2上使用上面创建的证书为数据库实例创建镜像端点 
  10. CREATE ENDPOINT Endpoint_As 
  11.    STATE = STARTED 
  12.    AS TCP ( 
  13.       LISTENER_PORT=5022, 
  14.       LISTENER_IP = ALL 
  15.    ) 
  16.    FOR DATABASE_MIRRORING ( 
  17.       AUTHENTICATION = CERTIFICATE As_A_cert, 
  18.       ENCRYPTION = REQUIRED ALGORITHM RC4, 
  19.       ROLE = ALL 
  20.    ); 
  21. GO 

注:这里要注意设置数据库的镜像端口。5022.

  1. --备份10.2上的证书并拷贝到10.1上 
  2. BACKUP CERTIFICATE As_A_cert TO FILE = 'D:\As_A_cert.cer'
  3. GO 

注:备份证书A,并将证书A拷贝到镜像服务器B上。

配置镜像服务器

  1. USE master;  
  2. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  
  3. GO  
  4. --在10.1 B上为数据库实例创建证书 
  5. CREATE CERTIFICATE As_B_cert  
  6.    WITH SUBJECT = 'As_B_cert'
  7.  START_DATE = '09/2/2011'
  8.  EXPIRY_DATE = '01/01/2099'
  9. GO  
  10. --在10.1 B上使用上面创建的证书为数据库实例创建镜像端点 
  11. CREATE ENDPOINT Endpoint_As  
  12.    STATE = STARTED  
  13.    AS TCP (  
  14.       LISTENER_PORT=5022  
  15.       , LISTENER_IP = ALL  
  16.    )  
  17.    FOR DATABASE_MIRRORING (  
  18.       AUTHENTICATION = CERTIFICATE As_B_cert  
  19.       , ENCRYPTION = REQUIRED ALGORITHM AES  
  20.       , ROLE = ALL  
  21.    );  
  22. GO  
  23. --备份10.1 B上的证书并拷贝到10.2 A上 
  24. BACKUP CERTIFICATE As_B_cert TO FILE = 'D:\As_B_cert.cer';  
  25. GO 

同样将备份的证书B 拷贝到A服务器上。

建立用于镜像登录的账户

在A上执行

  1. --交换证书, 
  2. --同步 Login 
  3. CREATE LOGIN B_login WITH PASSWORD = 'password';  
  4.  
  5. CREATE USER B_user FOR LOGIN B_login;  
  6.  
  7. CREATE CERTIFICATE As_B_cert AUTHORIZATION B_user FROM FILE = 'D:\As_B_cert.cer';  
  8.  
  9. GRANT CONNECT ON ENDPOINT::Endpoint_Bs TO [B_login]; 

在B上执行

  1. --交换证书, 
  2. --同步 Login 
  3. CREATE LOGIN A_login WITH PASSWORD = 'password';  
  4.  
  5. CREATE USER A_user FOR LOGIN A_login;  
  6.  
  7. CREATE CERTIFICATE As_A_cert AUTHORIZATION A_user FROM FILE = 'D:\As_A_cert.cer';  
  8.  
  9. GRANT CONNECT ON ENDPOINT::Endpoint_As TO [A_login]; 

记得两台服务器的端口5022是不被占用的,并且保证两个服务器可以连接

以后步骤执行没问题,镜像已经完成一半了。

接下来完整备份A服务器上的Test库

  1. --主机执行完整备份 
  2. USE master; 
  3. ALTER DATABASE Test SET RECOVERY FULL
  4. GO 
  5. BACKUP DATABASE Test 
  6.   TO DISK = 'D:\SQLServerBackups\Test.bak'  
  7.   WITH FORMAT; 
  8. GO 
  9. BACKUP LOG Test TO DISK = 'D:\SQLServerBackups\Test.bak'
  10. GO 
  11. --将备份文件拷贝到B上。 

一定要执行完整备份。

在B服务器上完整还原数据库

这里问题多多。一个一个说。

如果我们直接执行如下SQL.

  1. RESTORE DATABASE Test  
  2.     FROM DISK = 'D:\Back\Test.bak'  
  3.     WITH NORECOVERY  
  4. GO  
  5. RESTORE LOG Test  
  6.     FROM DISK = 'D:\Back\Test_log.bak'  
  7.     WITH FILE=1, NORECOVERY  
  8. GO 

可能会报:

消息 3154,级别 16,状态 4,第 1 行
备份集中的数据库备份与现有的 'Test’数据库不同。
消息 3013,级别 16,状态 1,第 1 行

可能是两个数据库的备份集名称不同导致,找了半天原因未果,所以采用下面sp_addumpdevice方法来做。

用sp_addumpdevice来建立一个还原设备。这样就保证了该备份文件是数据这个数据库的。

  1. exec sp_addumpdevice 'disk','Test_backup'
  2.         'E:\backup\Test.bak' 
  3. exec sp_addumpdevice 'disk','Test_log_backup'
  4.         'E:\backup\Test_log.bak' 
  5. go 

成功之后我们来执行完成恢复

  1. RESTORE DATABASE Test  
  2.   FROM Test_backup 
  3.   WITH DBO_ONLY,  
  4.     NORECOVERY,STATS; 
  5. go 
  6. RESTORE LOG Test  
  7.   FROM Test_log_backup 
  8.   WITH file=1, 
  9.     NORECOVERY; 
  10. GO 

这里如果之前备份过多次数据库的话,肯会产生多个备份集。所以这里的 file就不能指定为1了。

这个错误可能是:

消息 4326,级别 16,状态 1,第 1 行
此备份集中的日志终止于 LSN 36000000014300001,该 LSN 太早,无法应用到数据库

。可以还原包含 LSN 36000000018400001 的较新的日志备份。

可以通过这条语句来查询该备份文件的备份集

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

找到最后一个的序号指定给file就可以。

还需要注意的是第一次完整恢复的时候需要指定NORECOVERY。

至此所有准备工作都已经完成我们开启镜像了

先在镜像服务器上执行

  1. ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022'

成功之后再在主机上执行

  1. ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022'

这样两台服务器的镜像就同步了。

files/uploadimg/20110907/1447320.jpg" />

删除镜像:

  1. ALTER DATABASE Test SET PARTNER OFF 

如果主机出现问题,在主机执行

  1. USE MASTER  
  2.  
  3. Go  
  4.  
  5. ALTER DATABASE Test SET PARTNER FAILOVER  
  6.  
  7. Go 

总结:

如果在建立镜像的时候中间的那个步骤出问题,需要重新执行的时候一定要把该删得东西删除掉。

  1. --查询镜像 
  2. select * from sys.endpoints 
  3. --删除端口 
  4. drop endpoint Endpoint_As 
  5. --查询证书 
  6. select * from sys.symmetric_keys 
  7. --删除证书,先删除证书再删除主键 
  8. DROP CERTIFICATE As_A_cert 
  9. --删除主键 
  10. DROP MASTER KEY    
  11. --删除镜像 
  12. alter database <dbname> set partner off    
  13. --删除登录名 
  14. drop login <login_name> 

sp_addumpdevice 的语法

  1. sp_addumpdevice [ @devtype = ] 'device_type' 
  2.  
  3.         , [ @logicalname = ] 'logical_name' 
  4.  
  5.         , [ @physicalname = ] 'physical_name' 
  6.  
  7.       ] 
  8. 其中参数有: 
  9. @devtype:设备类型,可以支持的值为disk和tape,其中disk为磁盘文件;tape为 
  10. windows支持的任何磁带设备。 
  11. @logicalname:备份设备的逻辑名称,设备名称。 
  12. @physicalname:备份设备的物理名称,路径 

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

发表评论
用户名: 匿名