基于证书方式配置数据库镜像（证书双机镜像+见证）

每天有进步！
经历了很多的项目，各式系统环境应用也见过不少，工作学习中整理了诸多学习笔记，也常用于生产环境，却总是疏于整理，希望以后得空时能整理一些，给各路朋友分享学习下；如有不足之处，还请指正。

.基于证书方式配置镜像脚本

2014年1月1日，16:01:27 SqlServer2008R2
2018年9月5日，15:47:06 SqlServer2012SP4
飘：QQ45958462

. 服务器环境介结

1.数据库：SqlServer2008+
2.网络环境：生产服务器，见证机(本地自己机器)
    主体：192.168.1.109,21433  WINDOWS-NFPG530
    镜像：192.168.1.211,59805  WINDOWS-978M3EJ
    见证：192.168.0.219,21433  DB3
3. 修改每台机的HOST文件，将计算机名和IP绑定
    C:\Windows\System32\Drivers\etc\hosts
4. 分别开启三台服务器5022的入站端口。
    netsh advfirewall firewall add rule name="5022" dir=in action=allow protocol=TCP localport=5022
    netsh advfirewall firewall add rule name="5022" dir=out action=allow protocol=TCP remoteport=5022       
5. 可以配置共享目录临时存储备份与证书文件以方便配置

. 数据库相关配置

1. 目标数据库的恢复模式必须为“完整”，具体操作：企业管理器->数据库右键->属性->选项->恢复模式选择“完整”->确定。
    ALTER DATABASE [test] SET RECOVERY FULL;
2. 远程连接设置(非必要)，具体操作：数据库服务实例->右键->方面->外围应用配置器->RemoteDACEnabled->True->确定。

重要.

1. 建议在域环境下配置镜像
2. 给每台服务器(主机、镜像机、见证机)加上FQDN，
    即设置同样的DNS后缀名，如：dev.com，设置后需要重启服务器。 
    （方法：计算机右键->属性->更改设置->更改->其他->DNS后缀） 。
3. 一台服务器只能有一个端点，即每台服务器只能承担主机、镜像、见证其中一个角色。    
4. 建议做见证服务器，不带自动故障转移的镜像在生产环境可没什么大用。

--基于证书方式配置镜像脚本
--一. 备份还原数据库
--1. 主机备份
USE master
GO
BACKUP DATABASE [test] TO DISK = N'\\WINDOWS-NFPG530\ReplData\backup\test_init.bak' WITH FORMAT, INIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
BACKUP LOG [test] TO DISK = N'\\WINDOWS-NFPG530\ReplData\backup\test_init.trn' WITH NOFORMAT, NOINIT, NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO 

--2. 镜像恢复；镜像库恢复后处于“正在还原”状态
USE master
GO
RESTORE DATABASE [test] FROM DISK = N'\\WINDOWS-NFPG530\ReplData\backup\test_init.bak' WITH FILE = 1,NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
RESTORE LOG [test] FROM DISK = N'\\WINDOWS-NFPG530\ReplData\backup\test_init.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO


--二. 创建证书
--1. --============================主体机上执行 ============================--
USE master
GO

--1.1 创建证书，并备份
IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)    
    OPEN MASTER KEY DECRYPTION BY PASSWORD='1234567890!@#';
ELSE
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='1234567890!@#';
GO

IF EXISTS(select * from sys.certificates WHERE name='HOST_A_cert')
    DROP CERTIFICATE HOST_A_cert;
GO

CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT=N'HOST_A_cert Certificate',START_DATE='20120405',EXPIRY_DATE='20990405';

BACKUP CERTIFICATE HOST_A_cert TO FILE=N'\\WINDOWS-NFPG530\ReplData\backup\HOST_A_cert.cer';
GO

--1.2 创建镜像端口
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Host')
    DROP ENDPOINT Endpoint_Host
GO
CREATE ENDPOINT Endpoint_Host
STATE = STARTED
AS TCP
(
    LISTENER_PORT=5022,
    LISTENER_IP=ALL
)
FOR DATABASE_MIRRORING
(
    AUTHENTICATION=CERTIFICATE HOST_A_cert,
    ENCRYPTION=REQUIRED ALGORITHM AES,
    ROLE=PARTNER
)
GO

--2. --============================镜像机上执行 ============================--
USE master
GO

--2.1 创建证书，并备份
IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)    
    OPEN MASTER KEY DECRYPTION BY PASSWORD='1234567890!@#-B';
ELSE
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='1234567890!@#-B';
GO

IF EXISTS(select * from sys.certificates WHERE name='HOST_B_cert')
    DROP CERTIFICATE HOST_B_cert;
GO
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT=N'HOST_B_cert Certificate',START_DATE='20120405',EXPIRY_DATE='20990405';

BACKUP CERTIFICATE HOST_B_cert TO FILE=N'\\WINDOWS-NFPG530\ReplData\backup\HOST_B_cert.cer';
GO

--2.2 创建镜像端口
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirror')
    DROP ENDPOINT Endpoint_Mirror
GO
CREATE ENDPOINT Endpoint_Mirror
STATE = STARTED
AS TCP
(
    LISTENER_PORT=5022,
    LISTENER_IP=ALL
)
FOR DATABASE_MIRRORING
(
    AUTHENTICATION=CERTIFICATE HOST_B_cert,
    ENCRYPTION=REQUIRED ALGORITHM AES,
    ROLE=PARTNER
)
GO

--3. --============================见证机上执行 ============================--
USE master
GO

--3.1 创建证书，并备份
IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)    
    OPEN MASTER KEY DECRYPTION BY PASSWORD='1234567890!@#';
ELSE
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='1234567890!@#';
GO

IF EXISTS(select * from sys.certificates WHERE name='HOST_C_cert')
    DROP CERTIFICATE HOST_C_cert;
GO

CREATE CERTIFICATE HOST_C_cert
    WITH SUBJECT=N'HOST_C_cert Certificate',START_DATE='20120405',EXPIRY_DATE='20990405';

BACKUP CERTIFICATE HOST_C_cert TO FILE=N'\\WINDOWS-NFPG530\ReplData\backup\HOST_C_cert.cer';
GO

--3.2 创建镜像端口
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Witness')
    DROP ENDPOINT Endpoint_Witness
GO
CREATE ENDPOINT Endpoint_Witness
STATE = STARTED
AS TCP
(
    LISTENER_PORT=5022,
    LISTENER_IP=ALL
)
FOR DATABASE_MIRRORING
(
    AUTHENTICATION=CERTIFICATE HOST_C_cert,
    ENCRYPTION=REQUIRED ALGORITHM AES,
    ROLE=WITNESS
)
GO


--三.创建登录用户
-- 把上面三个步骤中备份的证书COPY到每台机，确保每台机都有此三个证书。

--1. --============================主机上执行 ============================--
USE master
GO

--1.1 为镜像机访问主机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Mirror WITH PASSWORD=N'1234567890!@#';
CREATE USER User_For_Mirror FOR LOGIN Login_For_Mirror;
CREATE CERTIFICATE Cert_For_Mirror AUTHORIZATION User_For_Mirror FROM FILE=N'\\WINDOWS-NFPG530\ReplData\backup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Host TO Login_For_Mirror;
GO

--1.2 为见证机访问主机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Witness WITH PASSWORD=N'1234567890!@#';
CREATE USER User_For_Witness FOR LOGIN Login_For_Witness;
CREATE CERTIFICATE Cert_For_Witness AUTHORIZATION User_For_Witness FROM FILE=N'\\WINDOWS-NFPG530\ReplData\backup\HOST_C_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Host TO Login_For_Witness;
GO

--2. --============================镜像机上执行 ============================--
USE master
GO

--2.1 为主机访问镜像机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Host WITH PASSWORD=N'1234567890!@#';
CREATE USER User_For_Host FOR LOGIN Login_For_Host;
CREATE CERTIFICATE Cert_For_Host AUTHORIZATION User_For_Host FROM FILE =N'\\WINDOWS-NFPG530\ReplData\backup\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirror TO Login_For_Host;
GO

--2.2 为见证机访问镜像机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Witness WITH PASSWORD=N'1234567890!@#';
CREATE USER User_For_Witness FOR LOGIN Login_For_Witness;
CREATE CERTIFICATE Cert_For_Witness AUTHORIZATION User_For_Witness FROM FILE =N'\\WINDOWS-NFPG530\ReplData\backup\HOST_C_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirror TO Login_For_Witness;
GO

--3. --============================见证机上执行 ============================--
USE master
GO

--3.1 为主机访问见证机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Host WITH PASSWORD=N'1234567890!@#';
CREATE USER User_For_Host FOR LOGIN Login_For_Host;
CREATE CERTIFICATE Cert_For_Host AUTHORIZATION User_For_Host FROM FILE=N'\\WINDOWS-NFPG530\ReplData\backup\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Witness TO Login_For_Host;
GO

--3.2 为镜像机访问见证机的镜像端口而创建登录和用户，并授予连接权限
CREATE LOGIN Login_For_Mirror WITH PASSWORD=N'1234567890!@#';
CREATE USER User_For_Mirror FOR LOGIN Login_For_Mirror;
CREATE CERTIFICATE Cert_For_Mirror AUTHORIZATION User_For_Mirror FROM FILE=N'\\WINDOWS-NFPG530\ReplData\backup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Witness TO Login_For_Mirror;
GO


--四.配置镜像
--1.镜像机上执行： 建立 主机 合作
ALTER DATABASE [test] SET PARTNER =N'TCP://WINDOWS-NFPG530:5022'; 

--2.主机上执行
--2.1 设置镜像伙伴关系
ALTER DATABASE [test] SET PARTNER=N'TCP://WINDOWS-978M3EJ:5022';

--2.2 设置见证服务器
ALTER DATABASE [test] SET WITNESS=N'TCP://DB3:5022';


/*
--1. 配置成功后，
    主体库状态：“主体,正在同步”，如无同步内容则显示“主体,已同步”，
    镜机库状态：“镜像,已同步/正在还原”
--2. 如果镜像创建或同步失败，可通过企业管理器通过配置界面重新配置（数据库->右键->任务->镜像->配置安全性，可参考此文：http://liulike.blog.51cto.com/1355103/339183）。
*/