折腾SQL Server 镜像搞了一天,终于有点成果,现在分享出来,之前按网上做的出了很多问题。现在尽量把所遇到的问题都分享出来。
在域环境下我没配置成果,也许是域用户的原因,因为我在生产环境下搞的,更改域用户需要重启SQL Server ,所以这个方法放弃了,只能用证书形式。
环境:
主机:192.168.10.2 (代号A)
镜像:192.168.10.1 (代号B,为了一会说明方便)
(条件有限我没有搞见证服务器。)两台服务器上的都是SQL Server 2005
首先配置主机
主机上执行以下SQL
class="dp-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';
这样两台服务器的镜像就同步了。
files/uploadimg/20110907/1447320.jpg" />
删除镜像:
- ALTER DATABASE Test SET PARTNER OFF
如果主机出现问题,在主机执行
- USE MASTER
- Go
- ALTER DATABASE Test SET PARTNER FAILOVER
- Go
总结:
如果在建立镜像的时候中间的那个步骤出问题,需要重新执行的时候一定要把该删得东西删除掉。
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