SQLServer 2005做热备镜像

网上很多讲怎么做热备镜像的，但是基本都是抄来抄去，没见的有几个人真正做过，发现按照那些个帖子博客做出来的有超多问题，所以自己把过程写出来，自己也是经过一番折腾才做成功，才明白最容易错的地方在哪里

主数据库服务器Server A：12.2.2.161
镜像数据库服务器ServerB：12.2.2.132

需要做镜像的数据库列表：test1，test2，test3
目前是主数据库上有这三个数据库，镜像数据库上还没有

1、镜像数据库server上创建数据库：
create database test1
on primary
(name=’test1′,
filename=’D:SMSDBtest1.mdf’,
size=20mb,
filegrowth=10%
)
log on
(name=’test1_log’,
filename=’D:SMSDBtest1_log.ldf’,
size=2mb,
filegrowth=1mb
);

create database test2
on primary
(name=’test2′,
filename=’D:SMSDBtest2.mdf’,
size=20mb,
filegrowth=10%
)
log on
(name=’test2_log’,
filename=’D:SMSDBtest2_log.ldf’,
size=2mb,
filegrowth=1mb
);

create database test3
on primary
(name=’test3′,
filename=’D:SMSDBtest3.mdf’,
size=20mb,
filegrowth=10%
)
log on
(name=’test3_log’,
filename=’D:SMSDBtest3_log.ldf’,
size=2mb,
filegrowth=1mb
);

 

2、查看当前SQL Server版本号，2005 SP2至以下的版本做镜像都是有问题的，需要打补丁

select @@version；

Microsoft SQL Server 2005 -9.00.1399.00[Intel X86] Feb9 2007 22:47.07 Copyright(c) 1988-2005 Microsoft Corporation Enterprice Edtion on Windows NT 5.2 (Build 3790 Service Pack 2)

如果是2008以上的版本就没有什么问题了

这个版本我做下来是不行的，到后面无法做镜像，需要打补丁包，可以去官网下，这个是SP2，就下SP2的补丁包就可以了。

打完补丁包之后的版本如下，做镜像之前查看下版本是否已经改变，如果没有改变，重启下服务器：

Microsoft SQL Server 2005 -9.00.3042.00[Intel X86] Feb9 2007 22:47.07 Copyright(c) 1988-2005 Microsoft Corporation Enterprice Edtion on Windows NT 5.2 (Build 3790 Service Pack 2)

PS：安装好补丁包之后一定要重启服务器，这样才会生效。

3、设置serverA & ServerB 为完整恢复
alter database test1 set recovery FULL;
alter database test2 set recovery FULL;
alter database test3 set recovery FULL;

4、创建证书及端点,并备份证书
server A：

create master key encryption by password = ‘Yxgs12345’;
GO
create certificate Ser_A_cert with subject = ‘Ser_A certificate’, start_date = ‘2007/11/01’, expiry_date = ‘2020/11/01’;
GO
Create endpoint db_mirroring state = started
as tcp(listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate Ser_A_cert, encryption = disabled, role = all);
GO
Backup certificate Ser_A_cert to file = ‘d:Ser_A_cert.cer’;
GO

ServerB：

create master key encryption by password = ‘Yxgs12345’;
GO
create certificate Ser_B_cert with subject = ‘Ser_B certificate’, start_date = ‘2007/11/01’, expiry_date = ‘2020/11/01’;
GO
Create endpoint db_mirroring state = started
as tcp(listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate Ser_B_cert, encryption = disabled, role = all);
GO
Backup certificate Ser_B_cert to file = ‘d:Ser_B_cert.cer’;
GO

5、互换证书之后 创建用户

互换证书，即拷贝ServerA上证书到ServerB，拷贝ServerB上的证书到ServerA，保证两个Server上都有两个证书
ServerA：

create login Ser_B_login with PASSWORD = ‘Yxgs12345’;
GO

create user Ser_B_user from login Ser_B_login;
GO

Create certificate Ser_B_cert
Authorization Ser_B_user
From file = ‘D:Ser_B_cert.cer’;
GO

Grant CONNECT ON Endpoint::db_mirroring to [Ser_B_login];
GO
ServerB：

create login Ser_A_login with PASSWORD = ‘Yxgs12345’;
GO

create user Ser_A_user from login Ser_A_login;
GO

Create certificate Ser_A_cert
Authorization Ser_A_user
From file = ‘D:Ser_A_cert.cer’;
GO

Grant CONNECT ON Endpoint::db_mirroring to [Ser_A_login];
GO

6、镜像服务器上还原数据库

ServerA备份数据库：
backup database test1 to disk=’d:test1.bak’ with format;
backup database test2 to disk=’d:test2.bak’ with format;
backup database test3 to disk=’d:test3.bak’ with format;

ServerB还原数据库：
restore database test1 from disk=’d:test1.bak’ with replace,norecovery;
restore database test2 from disk=’d:test2.bak’ with replace,norecovery;
restore database test3 from disk=’d:test3.bak’ with replace,norecovery;

ServerA备份事务日志：
backup log test1 to disk=’d:test1.trn’;
backup log test2 to disk=’d:test2.trn’;
backup log test3 to disk=’d:test3.trn’;

ServerB上还原事务日志：
restore log test1 from disk=’d:test1.trn’ with norecovery;
restore log test2 from disk=’d:test2.trn’ with norecovery;
restore log test3 from disk=’d:test3.trn’ with norecovery;

PS：做好之后，ServerB上的数据库都处于正在还原的状态

7、开启镜像
ServerB:
ALTER DATABASE test1
SET PARTNER=’TCP://12.2.2.161:5022′;
go

ServerA上指定通讯伙伴为ServerB：
ALTER DATABASE test1
SET PARTNER=’TCP://12.2.2.132:5022′;
go

PS:开启镜像的时候，先开备库，再开主库，数据库一个一个做

开启镜像的时候报错最多，网上看到还要对网卡配置进行更改，需要启动netbios，还有DNS域名服务器的地址，两个服务器也要相同，如果这块报错可以看下这些地方设置是否正确。

还有就是SQL Server本身的log日志，log日志可以很容易的帮你定位到错误的地方

打开事务安全模式：
ServerA：
ALTER DATABASE EJ_AJZQ set SAFETY FULL;
ALTER DATABASE Ej_AJZQ_FSZT set SAFETY FULL;
ALTER DATABASE ReportServer set SAFETY FULL;

ServerB：
ALTER DATABASE EJ_AJZQ set SAFETY FULL;
ALTER DATABASE Ej_AJZQ_FSZT set SAFETY FULL;
ALTER DATABASE ReportServerset SAFETY FULL;

也可以如下方式打开事务安全模式：
ServerA：(主体服务器)
ALTER DATABASE <database> SET PARTNER SAFETY FULL

关闭事务安全模式：
ServerA：(主体服务器)
ALTER DATABASE <database> SET PARTNER SAFETY OFF