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.