合在非域环境下搭建MSSQL 2016高可用之镜像传输

2022年1月3日

0 375 1

Tags： MSSQL 高可用镜像传输

👉 本文共约1503个字，系统预计阅读时间或需6分钟。

架构
数据库初始化
主库备份
备库还原
修改hosts文件
共享目录
配置证书
配置数据库镜像
打开数据库镜像监视器
故障转移测试
总结

搭建MSSQL 2008R2高可用之发布订阅（数据库复制）：https://www.dbaup.com/dajianmssql-2008r2gaokeyongzhifabudingyueshujukufuzhi.html
搭建MSSQL 2008R2高可用之日志传输（Log Shipping）：https://www.dbaup.com/dajianmssql-2008r2gaokeyongzhirizhichuanshulog-shipping.html

架构

（1）数据库：SQL Server 1016

（2）操作系统：Windows Server 1012 R2 DataCenter 64位（数据中心版）

操作系统：都是Windows Server 1012 R2 DataCenter 64位

服务器包括3台，非域环境：

主体节点：192.168.0.101
镜像节点：192.168.0.102
见证服务器：192.168.0.103

其中，101、102和103都需要提前安装好SQL Server 1016数据库！

数据库初始化

主库备份

create database lhrdb2;
ALTER DATABASE [lhrdb2] SET RECOVERY FULL;
backup database lhrdb2 TO DISK= N'd:\bk\lhrdb2.bak'   with format,stats=5,compression;
backup log  lhrdb2 TO DISK= N'd:\bk\lhrdb2_log.bak'   with format,stats=5,compression;

create database lhrdb2;

ALTER DATABASE [lhrdb2] SET RECOVERY FULL;

backup database lhrdb2 TO DISK= N'd:\bk\lhrdb2.bak' with format,stats=5,compression;

backup log lhrdb2 TO DISK= N'd:\bk\lhrdb2_log.bak' with format,stats=5,compression;

备库还原

将主库的备份文件传输到备库的目录中：

RESTORE DATABASE [lhrdb2] FROM DISK = N'd:\bk\lhrdb2.bak'  with NORECOVERY,stats=5;
RESTORE LOG lhrdb2 FROM DISK= N'd:\bk\lhrdb2_log.bak' WITH NORECOVERY;

1 2	RESTORE DATABASE [lhrdb2] FROM DISK = N'd:\bk\lhrdb2.bak' with NORECOVERY,stats=5; RESTORE LOG lhrdb2 FROM DISK= N'd:\bk\lhrdb2_log.bak' WITH NORECOVERY;

注意保持数据库为正在还原状态（norecovery）！！！

修改hosts文件

C:\Windows\System32\drivers\etc\

192.168.0.101  mssql101 mssql101.lhr.com
192.168.0.102  mssql102 mssql102.lhr.com
192.168.0.103  mssql103 mssql103.lhr.com

192.168.0.101 mssql101 mssql101.lhr.com

192.168.0.102 mssql102 mssql102.lhr.com

192.168.0.103 mssql103 mssql103.lhr.com

共享目录

将192.168.0.101的D盘的bk目录共享出来，添加everyone权限：

配置证书

-- 节点1
USE master  
GO

-- 4. 创建数据库主密钥
-- （数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥）
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456';  
GO

-- 5. 创建将用于加密可用性组端点的证书
CREATE CERTIFICATE MSSQL101_cert 
WITH SUBJECT = 'server192.168.0.101 certificate for Availability Group' 
GO

-- 6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证
CREATE ENDPOINT Endpoint_AvailabilityGroup
AUTHORIZATION [sa]
STATE = STARTED 
AS TCP 
(
   LISTENER_PORT = 5022, LISTENER_IP = ALL
)  
FOR DATABASE_MIRRORING 
(
   AUTHENTICATION = CERTIFICATE MSSQL101_cert, 
   ENCRYPTION = REQUIRED ALGORITHM AES,
   ROLE = ALL
);  
GO

-- 7. 主副本：将证书导出到文件
BACKUP CERTIFICATE MSSQL101_cert 
TO FILE = '\\192.168.0.101\bk\MSSQL101_cert.cer';  
GO

-- 节点2
USE master  
GO

-- 4. 创建数据库主密钥
-- （数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥）
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456';  
GO

-- 5. 创建将用于加密可用性组端点的证书
CREATE CERTIFICATE MSSQL102_cert 
WITH SUBJECT = 'server192.168.0.102 certificate for Availability Group' 
GO

-- 6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证
CREATE ENDPOINT Endpoint_AvailabilityGroup
AUTHORIZATION [sa] 
STATE = STARTED 
AS TCP 
(
   LISTENER_PORT = 5022, LISTENER_IP = ALL
)  
FOR DATABASE_MIRRORING 
(
   AUTHENTICATION = CERTIFICATE MSSQL102_cert, 
   ENCRYPTION = REQUIRED ALGORITHM AES,
   ROLE = ALL
);  
GO

-- 7. 主副本：将证书导出到文件
BACKUP CERTIFICATE MSSQL102_cert 
TO FILE = '\\192.168.0.101\bk\MSSQL102_cert.cer';  
GO

-- 节点3
USE master  
GO

-- 4. 创建数据库主密钥
-- （数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥）
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456';  
GO

-- 5. 创建将用于加密可用性组端点的证书
CREATE CERTIFICATE MSSQL103_cert 
WITH SUBJECT = 'server192.168.0.103 certificate for Availability Group' 
GO

-- 6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证
CREATE ENDPOINT Endpoint_AvailabilityGroup
AUTHORIZATION [sa] 
STATE = STARTED 
AS TCP 
(
   LISTENER_PORT = 5022, LISTENER_IP = ALL
)  
FOR DATABASE_MIRRORING 
(
   AUTHENTICATION = CERTIFICATE MSSQL103_cert, 
   ENCRYPTION = REQUIRED ALGORITHM AES,
   ROLE = ALL
);  
GO

-- 7. 主副本：将证书导出到文件
BACKUP CERTIFICATE MSSQL103_cert 
TO FILE = '\\192.168.0.101\bk\MSSQL103_cert.cer';  
GO

-- 保证每个节点都有3个节点的证书（在每个节点都执行）
CREATE CERTIFICATE MSSQL101_cert
FROM FILE = '\\192.168.0.101\bk\MSSQL101_cert.cer';  
GO

CREATE CERTIFICATE MSSQL102_cert
FROM FILE = '\\192.168.0.101\bk\MSSQL102_cert.cer';  
GO

CREATE CERTIFICATE MSSQL103_cert
FROM FILE = '\\192.168.0.101\bk\MSSQL103_cert.cer';  
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

-- 节点1

USE master

-- 4. 创建数据库主密钥

-- （数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥）

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456';

-- 5. 创建将用于加密可用性组端点的证书

CREATE CERTIFICATE MSSQL101_cert

WITH SUBJECT = 'server192.168.0.101 certificate for Availability Group'

-- 6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证

CREATE ENDPOINT Endpoint_AvailabilityGroup

AUTHORIZATION [sa]

STATE = STARTED

AS TCP

(

LISTENER_PORT = 5022, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE MSSQL101_cert,

ENCRYPTION = REQUIRED ALGORITHM AES,

ROLE = ALL

);

-- 7. 主副本：将证书导出到文件

BACKUP CERTIFICATE MSSQL101_cert

TO FILE = '\\192.168.0.101\bk\MSSQL101_cert.cer';

-- 节点2

USE master

-- 4. 创建数据库主密钥

-- （数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥）

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456';

-- 5. 创建将用于加密可用性组端点的证书

CREATE CERTIFICATE MSSQL102_cert

WITH SUBJECT = 'server192.168.0.102 certificate for Availability Group'

-- 6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证

CREATE ENDPOINT Endpoint_AvailabilityGroup

AUTHORIZATION [sa]

STATE = STARTED

AS TCP

(

LISTENER_PORT = 5022, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE MSSQL102_cert,

ENCRYPTION = REQUIRED ALGORITHM AES,

ROLE = ALL

);

-- 7. 主副本：将证书导出到文件

BACKUP CERTIFICATE MSSQL102_cert

TO FILE = '\\192.168.0.101\bk\MSSQL102_cert.cer';

-- 节点3

USE master

-- 4. 创建数据库主密钥

-- （数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥）

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456';

-- 5. 创建将用于加密可用性组端点的证书

CREATE CERTIFICATE MSSQL103_cert

WITH SUBJECT = 'server192.168.0.103 certificate for Availability Group'

-- 6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证

CREATE ENDPOINT Endpoint_AvailabilityGroup

AUTHORIZATION [sa]

STATE = STARTED

AS TCP

(

LISTENER_PORT = 5022, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE MSSQL103_cert,

ENCRYPTION = REQUIRED ALGORITHM AES,

ROLE = ALL

);

-- 7. 主副本：将证书导出到文件

BACKUP CERTIFICATE MSSQL103_cert

TO FILE = '\\192.168.0.101\bk\MSSQL103_cert.cer';

-- 保证每个节点都有3个节点的证书（在每个节点都执行）

CREATE CERTIFICATE MSSQL101_cert

FROM FILE = '\\192.168.0.101\bk\MSSQL101_cert.cer';

CREATE CERTIFICATE MSSQL102_cert

FROM FILE = '\\192.168.0.101\bk\MSSQL102_cert.cer';

CREATE CERTIFICATE MSSQL103_cert

FROM FILE = '\\192.168.0.101\bk\MSSQL103_cert.cer';

配置数据库镜像

在镜像节点192.168.0.102，输入命令如下：

ALTER DATABASE lhrdb2 SET PARTNER = N'TCP://MSSQL101.lhr.com:5022';

1	ALTER DATABASE lhrdb2 SET PARTNER = N'TCP://MSSQL101.lhr.com:5022';

在主体节点：

ALTER DATABASE lhrdb2 SET PARTNER = N'TCP://MSSQL102.lhr.com:5022';

-- 添加见证服务
ALTER DATABASE lhrdb2 SET WITNESS = N'TCP://MSSQL103.lhr.com:5022';

ALTER DATABASE lhrdb2 SET PARTNER = N'TCP://MSSQL102.lhr.com:5022';

-- 添加见证服务

ALTER DATABASE lhrdb2 SET WITNESS = N'TCP://MSSQL103.lhr.com:5022';

执行完成后，镜像正常启动！

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(1)

标签： MSSQL 高可用镜像传输

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

合在非域环境下搭建MSSQL 2016高可用之镜像传输

架构

数据库初始化

主库备份

备库还原

修改hosts文件

共享目录

配置证书

配置数据库镜像

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 在非域环境下搭建MSSQL 2016高可用之镜像传输

架构

数据库初始化

主库备份

备库还原

修改hosts文件

共享目录

配置证书

配置数据库镜像

相关文章

您可能还喜欢...

MSSQL导入示例数据库AdventureWorks报错 出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。

MSSQL报错“仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'pis_t'中的标识列指定显式值”

MSSQL示例数据库安装使用

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

合在非域环境下搭建MSSQL 2016高可用之镜像传输

MSSQL导入示例数据库AdventureWorks报错出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。

发表回复取消回复