SQL2005 使用证书实现数据库镜像

 数据库镜像可以使用两种类型的传输安全性：Windows 身份验证或基于证书的身份验证，就证书验证的镜像实现做个学习笔记。

   配置环境:
HOST_A : Windows Server 2003  Standard Edition(SP2)+SQL Server 2005 Enterprise Edition(SP2 9.00.3042.00)
HOST_B : Windows Server 2003  Standard Edition(SP2)+SQL Server 2005 Enterprise Edition(SP2 9.00.3042.00)

主体
HOST_A
镜像
HOST_B

一. 在主体数据库HOST_A 上:
USE master;

--a.创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'madefuckgan!';
GO
--可用以下语句查看生成的数据库主密钥
--SELECT * from  sys.symmetric_keys ;

--b.创建一个证书
CREATE CERTIFICATE DB_HOST_A_cert
   WITH SUBJECT = 'DB_HOST_A certificate for database mirroring' ,
   START_DATE = '12/31/2007' ,
   EXPIRY_DATE = '12/31/2010'
GO
--SELECT * FROM sys.certificates;

--c.创建镜像端点
CREATE ENDPOINT DB_MIRR
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5021
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE DB_HOST_A_cert
      , ENCRYPTION = REQUIRED ALGORITHM RC4
      , ROLE = ALL
   );
GO
--SELECT * FROM sys.tcp_endpoints
--SELECT * FROM sys.database_mirroring_endpoints;

--d.备份证书
BACKUP CERTIFICATE DB_HOST_A_cert TO FILE = 'D:/db_A_run.cer';
GO

--e.创建登入

CREATE LOGIN TO_HOST_A_login WITH PASSWORD = 'openthedoorHostA!';
GO

CREATE USER TO_HOST_A_user FOR LOGIN TO_HOST_A_login;
GO

二. 在镜像数据库HOST_B 上:              
USE master;

--1.创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'madefuckgan#2!';
GO
--可用以下语句查看生成的数据库主密钥
--SELECT * from  sys.symmetric_keys ;

--2.创建一个证书
CREATE CERTIFICATE DB_HOST_B_cert
   WITH SUBJECT = 'DB_HOST_B certificate for database mirroring' ,
   START_DATE = '12/31/2007' ,
   EXPIRY_DATE = '12/31/2010'
GO
--SELECT * FROM sys.certificates;

--3.创建镜像端点
CREATE ENDPOINT DB_MIRR
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE DB_HOST_B_cert
      , ENCRYPTION = REQUIRED ALGORITHM RC4
      , ROLE = ALL
   );
GO
--SELECT * FROM sys.tcp_endpoints
--SELECT * FROM sys.database_mirroring_endpoints;

--4.备份证书
BACKUP CERTIFICATE DB_HOST_B_cert TO FILE = 'D:/db_B_run.cer';
GO

--5.创建登入

CREATE LOGIN TO_HOST_B_login WITH PASSWORD = 'openthedoorHostB!';
GO

CREATE USER TO_HOST_B_user FOR LOGIN TO_HOST_B_login;
GO

三.回到主体数据库HOST_A 上:
f.从HOST_B上D:/下拷贝备份出来的证书db_B_run.cer到HOST_A的D:/下
g.--还原B证书到HOST_A上；
use master
CREATE CERTIFICATE DB_HOST_B_cert
   AUTHORIZATION TO_HOST_A_user
   FROM FILE = 'D:/db_B_run.cer'
GO
--赋权
--Grant CONNECT permission on the login for the remote mirroring endpoint.
GRANT CONNECT ON ENDPOINT::DB_MIRR TO [TO_HOST_A_login];
GO
h.
CREATE DATABASE MIRR_TEST
--数据库镜像必须更改为使用完整恢复模式
ALTER DATABASE MIRR_TEST
   SET RECOVERY FULL;
GO
--备份数据库
BACKUP DATABASE MIRR_TEST
    TO DISK = N'D:/MT.BAK'
    WITH FORMAT
GO
--备份日志
--！笔者试过只备份数据库不成功，一定要备份日志.
BACKUP LOG MIRR_TEST
    TO DISK = N'D:/MT.BAK'
GO          
四.在镜像数据库HOST_B 上:
6.从HOST_A上D:/下拷贝备份出来的证书db_A_run.cer到HOST_B的D:/下
7.
--还原A证书到HOST_B上；
use master
CREATE CERTIFICATE DB_HOST_A_cert
   AUTHORIZATION TO_HOST_B_user
   FROM FILE = 'D:/db_A_run.cer'
GO
--赋权
--Grant CONNECT permission on the login for the remote mirroring endpoint.
GRANT CONNECT ON ENDPOINT::DB_MIRR TO [TO_HOST_B_login];
GO
8.
--拷贝从HOST_A备份的数据库备份文件到D:/.
--还原数据库及日志
RESTORE DATABASE MIRR_TEST
    FROM DISK = N'D:/MT.BAK'
    WITH NORECOVERY ;
GO
RESTORE LOG MIRR_TEST
    FROM DISK = N'D:/NGT.BAK'
    WITH FILE=2, NORECOVERY;
GO
五.设置伙伴（自动启动镜像）
9.在HOST_B
ALTER DATABASE MIRR_TEST
    SET PARTNER = 'TCP://HOST_A:5021';
GO

i.在HOST_A
ALTER DATABASE MIRR_TEST
    SET PARTNER = 'TCP://HOST_B:5022';
GO

六. OVER!
PS: 如果HOST_A、HOST_B是在公网上则需分别在%WINDOWS%/system32/drivers/etc/hosts添加解析。