SQLServer 镜像功能完全实现

时间:2021-08-31 12:33:19

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

环境: 

主机:192.168.10.2 (代号A) 

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

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

首先配置主机 

主机上执行以下SQL 

复制代码代码如下:


--创建主机数据库主密钥 
USE master; 
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 
[code] 
可能会报: 

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

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

用sp_addumpdevice来建立一个还原的设备。这样就保证了改备份文件是数据这个数据库的。 
[code] 
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'; 
这样两台服务器的镜像就同步了。 

SQLServer 镜像功能完全实现

删除镜像:

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

 

 

参考:
http://msdn.microsoft.com/zh-cn/library/ms186289.aspx