数据库镜像一般在域下做，如果没有在域中则需要进行证书认证
数据库镜像可以使用两种类型的传输安全性：Windows 身份验证或基于证书的身份验证，就证书验证的镜像实现做个学习笔记。
配置环境:
HOST_A : Windows Server 2003Standard Edition(SP2)+SQL Server 2005 Enterprise Edition(SP2 9.00.3042.00)
HOST_B : Windows Server 2003Standard 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 * fromsys.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 * fromsys.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添加解析。
