MSSQL Server2008 R2 数据库镜像配置方案

一、服务器信息

主机名称为：HOST_A，IP地址为：192.168.10.110
备机名称为：HOST_B，IP地址为：192.168.10.111

1、两台服务器都添加防火墙入站规则：端口1433、5022

2、SQL Server网络配置中：TCP/IP = 已启用

 

MSSQL Server 实现镜像配置有两种方式，一种是通过域实现，一种是通过证书。

以下选取通过证书的方式实现。

 

二、证书配置

1、创建证书

-- 主机执行
USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'sqlpwd@2024'; 
CREATE CERTIFICATE HOST_110_cert WITH SUBJECT = 'HOST_110 certificate', 
START_DATE = '2024-10-01', 
EXPIRY_DATE = '2099-12-31'; 

-- 备机执行：
USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'sqlpwd@2024'; 
CREATE CERTIFICATE HOST_111_cert WITH SUBJECT = 'HOST_111 certificate', 
START_DATE = '2024-10-01', 
EXPIRY_DATE = '2099-12-31'; 

 

附：删除命令：

-- 删除主密钥
USE master;
DROP MASTER KEY

-- 删除证书
USE master;
DROP CERTIFICATE HOST_110_cert

 

2、创建连接端点

-- 主机执行： 
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE HOST_110_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL); 

-- 备机执行：
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE HOST_111_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);

 

附：删除命令

-- 查看已经存在端点:
SELECT * FROM  sys.database_mirroring_endpoints

-- 删除端点
DROP ENDPOINT [端点名]

 

3、备份证书和互换证书

-- 主机执行：
BACKUP CERTIFICATE HOST_110_cert TO FILE = 'C:\MSSQLCert\HOST_110_cert.cer'; 

-- 备机执行：
BACKUP CERTIFICATE HOST_111_cert TO FILE = 'C:\MSSQLCert\HOST_111_cert.cer';

然后将主机的证书复制到备机，将备机的证书复制到主机。

 

4、创建登录名和用户

-- 主机执行：
CREATE LOGIN HOST_111_login WITH PASSWORD = 'sqlpwd@2024'; 
CREATE USER HOST_111_user FOR LOGIN HOST_111_login; 
CREATE CERTIFICATE HOST_111_cert AUTHORIZATION HOST_111_user FROM FILE = 'C:\MSSQLCert\HOST_111_cert.cer'; 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_111_login]; 

-- 备机执行：
CREATE LOGIN HOST_110_login WITH PASSWORD = 'sqlpwd@2024'; 
CREATE USER HOST_110_user FOR LOGIN HOST_110_login; 
CREATE CERTIFICATE HOST_110_cert AUTHORIZATION HOST_110_user FROM FILE = 'C:\MSSQLCert\HOST_110_cert.cer'; 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_110_login]; 

至此，准备工作完成！

 

三、同步数据库，及创建镜像

1、备份数据库

1.1 先将数据库恢复模式设置为完整

你的数据库 - 右键 - 属性 - 选项

 

1.2 备份数据库，分两次（1/2）备份数据

 

1.3 （2/2）备份事务日志

 

2、将备份好的数据库拷贝到备机进行还原

2.1 还原也分两次，（1/2）还原数据库

 

 2.2 （2/2）还原事务日志

 

还原完成后如下图所示：显示 正在还原... 的标识

 

3、为主/备机添加镜像配置

-- 在【备机】中执行如下语句：（先执行，谁先执行谁就是备机）
ALTER DATABASE [数据库名称] SET PARTNER = 'TCP://192.168.10.110:5022';
-- [数据库名称] 替换成你自己的数据库名

-- 在【主机】执行如下语句：
ALTER DATABASE [数据库名称] SET PARTNER = 'TCP://192.168.10.111:5022'; 

 

4、检查配置结果

4.1 主机

 

4.2 备机

 

四、接下来说说主备切换和宕机操作

有三种情形

1、情形1：最简单的：主备切换

-- 在上机上执行（主备切换） 
USE master;
ALTER DATABASE [数据库名称] SET PARTNER FAILOVER;

执行完命令后，主机和备机的角色就会互换。

 

2、情形2：主机宕机，在备机上强行切换

-- 在备机上执行（备机立即转换为主体服务器，并且将镜像挂起。）
USE master;
ALTER DATABASE [数据库名称] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

当主机开机重启后

-- 在备机上执行（恢复镜像）
USE master;
ALTER DATABASE [数据库名称] SET PARTNER RESUME;

此时，主机变为备机，备机变为主机。（角色互换）

如果想要恢复角色，请参考：情形1（主备切换）

 

3、情形3：主机宕机，暂时启不来了，但是业务还要继续

-- 在备机上执行（备机立即转换为主体服务器，并且将镜像挂起。）
USE master;
ALTER DATABASE [数据库名称] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

--在备机上执行（取消镜像）
ALTER DATABASE [数据库名称] SET PARTNER OFF;

此时备机成为一个独立的、正常的数据库。

 

后来，主机重启后，数据库会显示：主体，已同步

-- 将数据库还原为正常
RESTORE DATABASE [数据库名称] WITH RECOVERY;

此时发现，已经无法进行镜像同步了。

只能，重新备份、还原数据库，重新配置镜像了（重新步骤三）。