SQL 2008配置有证书镜像的步聚共有四步:
1.      配置出站连接:创建主秘钥、证书和端点,备份证书并拷贝到另一台服务器
2.      配置入站连接:分配登陆用户、赋予登陆用户connect本机端点的权限
3.      创建镜像数据:备主数据库,还原镜像数据库
4.      配置镜像伙伴关系:顺序:镜像----主体
 
测试环境:
             主数据库服务器:     192.168.200.91
             镜像数据库服务器:   192.168.200.95
 
以下是测试的详细脚本:
============================================================================
一.配置出站:配置完证书后,分别互存到各数据库服务器的对应路径,注意端口
============================================================================
配置HOST_91出站
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mo%123';
GO
CREATE CERTIFICATE HOST_91_cert
   WITH SUBJECT = 'HOST_91 certificate for database mirroring';
GO
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE HOST_91_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO
BACKUP CERTIFICATE HOST_91_cert TO FILE = 'C:\HOST_91_cert.cer';
GO
配置HOST_95出站
USE master;
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = mo%123';
GO
 
CREATE CERTIFICATE HOST_95_cert
   WITH SUBJECT = 'HOST_95 certificate for database mirroring';
GO
 
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE HOST_95_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO
 
BACKUP CERTIFICATE HOST_95_cert TO FILE = 'C:\HOST_95_cert.cer';
GO
=============================================================================
二.入站镜像连接配置服务器:注意证书的路径,登录账户的命名
=============================================================================
 
HOST_91的入站配置:
USE master;
CREATE LOGIN HOST_95_LOGIN
   WITH PASSWORD = 'mo%123';                
GO
USE master;
CREATE USER HOST_95_USER FOR LOGIN HOST_95_LOGIN;
GO
CREATE CERTIFICATE HOST_95_cert
   AUTHORIZATION HOST_95_USER
   FROM FILE = 'C:\HOST_95_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_95_LOGIN
GO
==============================================================
HOST_95的入站配置:
USE master;
CREATE LOGIN HOST_91_LOGIN
   WITH PASSWORD = 'mo%123';
GO
USE master;
CREATE USER HOST_91_USER FOR LOGIN HOST_91_LOGIN;
GO
CREATE CERTIFICATE HOST_91_cert
   AUTHORIZATION HOST_91_USER
   FROM FILE = 'C:\HOST_91_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_91_LOGIN
GO
====================================================================
三.配置数据库:备份包括数据库和日志
=====================================================================
备份主数据库HOST_91
USE master;
GO
ALTER DATABASE livechat
SET RECOVERY FULL;
GO
BACKUP DATABASE  livechat
    TO DISK = 'e:\livechat.bak'
    WITH FORMAT
GO
BACKUP LOG  livechat
    TO DISK = 'e:\livechat_log.bak'
GO
 
还原镜像数据库HOST_95
RESTORE DATABASE  livechat
    FROM DISK = 'C:\livechat.bak'
    WITH NORECOVERY
GO
RESTORE LOG  livechat
    FROM DISK = 'C:\livechat_log.bak'
    WITH FILE=1, NORECOVERY
GO
===========================================================================
四.配置镜像:顺序:镜像----主体
===========================================================================
先执行HOST_95服务器:
ALTER DATABASE  livechat
    SET PARTNER = 'TCP://192.168.200.91:7024';
GO
再执行HOST_91服务器:
ALTER DATABASE  livechat
    SET PARTNER = 'TCP://192.168.200.95:7024';
GO
 
====================================================================
五.测试镜像:在主数据库上执行以下代码可以强制将镜像数据库启用为主数据库,可以查看数据同步的完整性。
=============================================================================
主数据库HOST_91执行:
USE master;
ALTER DATABASE  livechat
SET PARTNER FAILOVER
GO
 
 
    总结:以上是在没有验证服务器的情况下,两台数据库做的镜像,经测试在强制切换后数据完整,符合镜像数据的要求。

  遇到问题:服务器的网络地址“TCP :/ / ......” 无法访问或不存在。 检查网络地址名称,并为本地和远程端点的端口是否正常运行。

   解决:The problem was that the admin account did not have read/write rights on c:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ . After changing that I also had to recreate the master keys and certificates to make it work.