SQL Server 2008 R2 通过证书配置数据库镜像

SQL Server 2008 R2数据库镜像功能可以使数据库进行实时备份，当主体数据库出现问题后，可以迅速切换到备用数据库，提高了数据库的可用性。 一、工作原理 数据库镜像维护一个数据库的两个副本， 这两个副本必须驻留在不同的 SQL Server 数据库引擎 服务器实例上 。 通常，这些服务器实例驻留在不同位置的计算机上。 启动数据库上的数据库镜像操作时，在这些服务器实例之间形成一种关系，称为“数据库镜像会话”。 其中一个服务器实例使数据库服务于客户端（ “ 主体服务器 ” ）， 另一个服务器实例则根据镜像会话的配置和状态，充当热备用或温备用服务器（ “ 镜像服务器 ” ）。 同步数据库镜像会话时，数据库镜像提供热备用服务器，可支持在已提交事务不丢失数据的情况下进行快速故障转移。 未同步会话时，镜像服务器通常用作热备用服务器（可能造成数据丢失）。 在“数据库镜像会话”中，主体服务器和镜像服务器作为“伙伴”进行通信和协作。 两个伙伴在会话中扮演互补的角色：“主体角色”和“镜像角色”。 在任何给定的时间，都是一个伙伴扮演主体角色，另一个伙伴扮演镜像角色。 每个伙伴拥有其当前角色。 拥有主体角色的伙伴称为“主体服务器”，其数据库副本为当前的主体数据库。 拥有镜像角色的伙伴称为“镜像服务器”，其数据库副本为当前的镜像数据库。 如果数据库镜像部署在生产环境中，则主体数据库即为“生产数据库”。 数据库镜像涉及尽快将对主体数据库执行的每项插入、更新和删除操作“重做”到镜像数据库中 。 重做通过将活动事务日志记录的流发送到镜像服务器来完成，这会尽快将日志记录按顺序应用到镜像数据库中。 与逻辑级别执行的复制不同，数据库镜像在物理日志记录级别执行。 从 SQL Server 2008 开始，在事务日志记录的流发送到镜像服务器之前，主体服务器会先将其压缩。 在所有镜像会话中都会进行这种日志压缩。 更多参考：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms189852(v=sql.110) 二、实战配置 1.环境准备 （1）两台装有数据库实例的服务器。如： 主体服务器：192.168.102.5 （主体） 镜像服务器：192.168.102.20 （镜像） （2）两台机器创建相同的 登录账号和密码，并将其加入到管理员权限  （解决 错误1418），如：dbmirror （3）修改SQL Server (MSSQLSERVER)服务，将上一步添加的用户dbmirror作为登录帐户。（解决 错误1418） （4）防火墙开放对应端口，如：数据库使用的1433，镜像使用的5022，确保两台机器能够ping通。 将上述（2）-（4）步骤，在主体服务器和镜像服务器上做相同配置。 2.数据库操作 （1）将要做镜像的数据库，恢复模式设置为"完整"。 USE [master] GO ALTER DATABASE [Tsys] SET RECOVERY FULL WITH NO_WAIT GO –请将语句中绿色部分替换为您想做镜像的数据库名称。 （2）主体服务器：备份数据库，在主体服务器上操作数据库备份， 需要做“完整”、“事务日志”两种备份 。 备份文件选同一个，分别做“完整”、“事务日志”备份。 （3）镜像服务器：还原数据库 还原时，选择“不对数据库执行任何操作，不回滚未提交的事务。”，包括"完整"和"事务日志"。 （4）配置证书和端点 1.主服务器

--1.创建数据库主密钥
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '@1q2w3e4r';
/* 删除主密钥
USE master;
DROP MASTER KEY
*/
--2.创建证书并使用主密钥加密
USE master;
GO
CREATE CERTIFICATE Host_db05_Cert
WITH SUBJECT = 'db05_certificate',
EXPIRY_DATE = '2055-1-1';
/*删除证书
USE master;
DROP CERTIFICATE Host_db05_Cert
*/
--3.创建端点:
--创建端点之前，先查找下看是否已经存在端点:
SELECT *
FROM sys.database_mirroring_endpoints;
--如果需要删除端点
--DROP ENDPOINT 镜像;
IF NOT EXISTS ( SELECT 1
FROM sys.database_mirroring_endpoints )
BEGIN
CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =
CERTIFICATE Host_db05_Cert, ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL );
END;
--4.备份证书
--在D盘创建Cert目录
BACKUP CERTIFICATE Host_db05_Cert
TO FILE = 'D:\Cert\Host_db05_Cert.cer';

2.镜像服务器

--1.创建数据库主密钥
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '@1q2w3e4r';
/* 删除主密钥
USE master;
DROP MASTER KEY
*/
--2.创建证书并使用主密钥加密
USE master;
GO
CREATE CERTIFICATE Host_db20_Cert
WITH SUBJECT = 'db20_certificate',
EXPIRY_DATE = '2055-1-1';
/*删除证书
USE master;
DROP CERTIFICATE Host_db20_Cert
*/
--3.创建端点:
--创建端点之前，先查找下看是否已经存在端点:
SELECT *
FROM sys.database_mirroring_endpoints;
--如果需要删除端点
--DROP ENDPOINT 镜像;
IF NOT EXISTS ( SELECT 1
FROM sys.database_mirroring_endpoints )
BEGIN
CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =
CERTIFICATE Host_db20_Cert, ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL );
END;
--4.备份证书
--在D盘创建Cert目录
BACKUP CERTIFICATE Host_db20_Cert
TO FILE = 'D:\Cert\Host_db20_Cert.cer';

将主服务器(192.168.102.5)D盘Cert目录下的Host_db05_Cert证书复制到镜像服务器(192.168.102.20)的对应的位置， 同时将镜像服务器(192.168.102.20)上的Host_db20_Cert证书复制到主服务器(192.168.102.5)上 对应的位置 。 （5）创建镜像登录用户 1.主体服务器 （都是针对master数据库操作）

--5.创建一个登录给镜像服务器使用
CREATE LOGIN Db20_Login WITH PASSWORD = '@1q2w3e4r';
--6.创建一个用户以映射到上面创建的登录中
USE master;
CREATE USER Db20_User FOR LOGIN Db20_Login;
--7.使用证书进行授权
CREATE CERTIFICATE Host_db20_Cert
AUTHORIZATION Db20_User
FROM FILE = 'D:\Cert\Host_db20_Cert.cer';
--8.对登录进行连接端点的授权
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Db20_Login];

2.镜像服务器 （都是针对master数据库操作）

--5.创建一个登录给主体服务器使用
CREATE LOGIN Db05_Login WITH PASSWORD = '@1q2w3e4r';
--6.创建一个用户以映射到上面创建的登录中
USE master;
CREATE USER Db05_User FOR LOGIN Db05_Login;
--7.使用证书进行授权
CREATE CERTIFICATE Host_db05_Cert
AUTHORIZATION Db05_User
FROM FILE = 'D:\Cert\Host_db05_Cert.cer';
--8.对登录进行连接端点的授权
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Db05_Login];

3.配置镜像 先在 镜像服务器(192.168.102.20)中执行 ALTER DATABASE Tsys SET PARTNER = ‘TCP://192.168.102.5:5022’; GO 然后 再在主体服务器(192.168.102.5)中执行 ALTER DATABASE Tsys SET PARTNER = ‘TCP://192.168.102.20:5022’; GO 注意执行顺序。 4.完成配置 3.备用脚本

--（1）在高安全模式下：在主体执行
USE master;
ALTER DATABASE Tsys SET PARTNER FAILOVER;--主备切换
--（2）在高性能模式下，需要先切换到高安全模式下再执行切换
USE master;
ALTER DATABASE Tsys SET PARTNER SAFETY FULL;--高安全模式切换
ALTER DATABASE Tsys SET PARTNER FAILOVER;--主备切换
--（3）在主体宕机的情况下在镜像机进行强制切换：
USE master;
ALTER DATABASE Tsys SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
--当主体重新开机后，在主体机器上执行
USE master;
ALTER DATABASE Tsys SET PARTNER RESUME;
--此时原来的主体成为了镜像机，而镜像机成为了主体。再到镜像机机器上执行
ALTER DATABASE Tsys SET PARTNER FAILOVER;
--（4）切换镜像在高性能模式下(慎用，可能会丢失数据)
USE master;
ALTER DATABASE Tsys SET PARTNER SAFETY OFF;
--（5）关闭数据库镜像
ALTER DATABASE Tsys SET PARTNER OFF;

4.其他事项 在配置过程中，被"错误1418"纠结了很久，可以通过SQL日志来进行排查。如： 三、链接字符串 对于到镜像数据库的初始连接，客户端必须提供一个至少提供服务器实例名称的连接字符串。 这个必需的服务器名称应标识当前主体服务器实例，并称为“初始伙伴名称”。 另外，连接字符串还可以提供另一个服务器实例的名称，此名称应标识当前镜像服务器实例，以便在首次连接尝试期间初始伙伴不可用的情况下使用。 第二个名称称为“故障转移伙伴名称”。连接字符串还必须提供数据库名称。 这是数据访问接口启用故障转移尝试所必需的。 接收连接字符串后，数据访问接口将初始伙伴名称和故障转移伙伴名称（如果提供）存储在客户端易失内存的缓存中（对于托管代码，缓存的作用域限定为应用程序域）。 缓存后，数据访问接口将从不对初始伙伴名称进行更新。 客户端提供故障转移伙伴名称时，数据访问接口还暂时存储此故障转移伙伴名称，以防出现访问接口无法使用初始伙伴名称进行连接的情况。 数据库镜像会话无法避免与客户端相关的服务器访问问题，例如，客户端计算机出现网络通信问题时。 到镜像数据库的连接尝试也可能会因为各种与数据访问接口无关的原因而失败；例如，连接尝试可能会因为下列情况而失败：主体服务器实例处于不活动状态（如同数据库进行故障转移时发生的情况）或者网络错误。 例如，为了使用 TCP/IP 显式连接到 Partner_A 或 Partner_B 上的 AdventureWorks 数据库，使用 ODBC 驱动程序的客户端应用程序可能会提供以下连接字符串： “Server=Partner_A; Failover_Partner =Partner_B; Database=AdventureWorks; Network=dbmssocn” 另外，客户端还可以使用 IP 地址和端口号标识初始伙伴 Partner_A；例如，如果 IP 地址为 250.65.43.21，端口号为 4734，则连接字符串将为： “Server=250.65.43.21,4734; Failover_Partner =Partner_B; Database=AdventureWorks; Network=dbmssocn” 参考：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms175484(v=sql.110) 参考：http://blog.51cto.com/fengwan/1865070