折腾SQL Server 镜像搞了一天,终于有点成果,现在分享出来,之前按网上做的出了很多问题。现在尽量把所遇到的问题都分享出来。
在域环境下我没配置成果,也许是域用户的原因,因为我在生产环境下搞的,更改域用户需要重启SQL Server ,所以这个方法放弃了,只能用证书形式。
环境:
主机:192.168.10.2 (代号A)
镜像:192.168.10.1 (代号B,为了一会说明方便)
(条件有限我没有搞见证服务器。)两台服务器上的都是SQL Server 2005
首先配置主机
主机上执行以下SQL
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'; GO CREATE CERTIFICATE As_A_cert WITH SUBJECT = 'As_A_cert', START_DATE = '09/02/2011', EXPIRY_DATE = '01/01/2099'; GO 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.
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
-
- CREATE CERTIFICATE As_B_cert
- WITH SUBJECT = 'As_B_cert',
- START_DATE = '09/2/2011',
- EXPIRY_DATE = '01/01/2099';
- GO
-
- 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
-
- BACKUP CERTIFICATE As_B_cert TO FILE = 'D:\As_B_cert.cer';
- GO
|
同样将备份的证书B 拷贝到A服务器上。