SQL SERVER【非域环境】镜像之搭建篇

2023-10-17 06:32
文章标签 sql 镜像 搭建 环境 server database 非域

本文主要是介绍SQL SERVER【非域环境】镜像之搭建篇，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

环境搭建


软件环境（操作系统：Windows Server 2008 R2 Standard SP1）：

数据库版本

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 

Feb 20 2014 20:04:26

Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

 

 设置共享文件夹D盘路径（D:\ShareFile）

 

共享文件夹ShareFile用于机器间相互传输文件，右健—共享—特定用户—添加(Everyone)—权限（读/写）

 

1、在主服务器（SerA）上执行:

1.1、搭建环境创建测试数据库MirrorDB

CREATE DATABASE MirrorDB
ON(
NAME = MirrorDB_DATA,
FILENAME = N'D:\Database\MirrorDB.mdf'
)
LOG ON(
NAME = MirrorDB_LOG,
FILENAME = N'D:\Database\MirrorDB.ldf'
)
ALTER DATABASE MirrorDB SET
RECOVERY FULL
GO


1.2、备份到共享文件夹

--  完全备份
BACKUP DATABASE MirrorDB
TO DISK = N'D:\ShareFile\MirrorDB.bak'
WITH FORMAT
go


 

2、切换到作镜像服务器(SerB)执行

2.1、初始化镜像主体数据库,把共享文件从网络路径(\\SerA\ShareFile\MirrorDB.bak)复制到本机路径(E:\BackDB\MirrorDB.bak)

 

RESTORE DATABASE MirrorDB
FROM DISK = N'E:\BackDB\MirrorDB.bak'
WITH REPLACE
, NORECOVERY
-- 镜像数据库文件要放在指定位置, 则启用下面的 Move 选项
, MOVE 'MirrorDB_DATA' TO N'E:\Database\MirrorDB.mdf'
, MOVE 'MirrorDB_LOG' TO N'E:\Database\MirrorDB.ldf'


3、切换到主服务器(SerA)执行

3.1、使用数据库主密钥加密证书

IF NOT EXISTS(  
SELECT * FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Roy@123'CREATE CERTIFICATE CT_Mirror_SerA
WITH
SUBJECT = N'certificate for database mirror',
START_DATE = '19990101',
EXPIRY_DATE = '99991231'
GO


3.2、备份证书（新添加的文件显示在其它服务显示不出来时把此文件共享权限设置为Everyone读写）

BACKUP CERTIFICATE CT_Mirror_SerA
TO FILE = 'D:\ShareFile\CT_Mirror_SerA.cer'


3.3 创建一个数据库镜像端点

CREATE ENDPOINT EDP_Mirror
STATE = STARTED 
AS TCP(
LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
LISTENER_IP = ALL)     -- 侦听的IP地址FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE CT_Mirror_SerA, -- 证书身份验证
ENCRYPTION = DISABLED,                       -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法
ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)


4、切换到作镜像服务器(SerB)执行(同步骤3相同)

4.1、使用数据库主密钥加密证书

IF NOT EXISTS(  
SELECT * FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Roy@123'CREATE CERTIFICATE CT_Mirror_SerB
WITH
SUBJECT = N'certificate for database mirror',
START_DATE = '19990101',
EXPIRY_DATE = '99991231'
GO


 

4.2、备份证书[把备份证书从(E:\BackDB\CT_Mirror_SerB.cer)复制到共享网络路径（\\SerA\ShareFile\CT_Mirror_SerB.cer）]

BACKUP CERTIFICATE CT_Mirror_SerB
TO FILE = 'E:\BackDB\CT_Mirror_SerB.cer'
4.3、创建数据库镜像端点
CREATE ENDPOINT EDP_Mirror
STATE = STARTED 
AS TCP(
LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
LISTENER_IP = ALL)     -- 侦听的IP地址FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE CT_Mirror_SerB, -- 证书身份验证
ENCRYPTION = DISABLED,                       -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法
ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)


GO

4.4、在共享文件夹里（\\SerA\ShareFile\CT_Mirror_SerA.cer'）复制到本机(E:\BackDB\CT_Mirror_SerA.cer),建立主体服务器上的证书,注：共享证书文件未显示时把文件权限改为Everyone读写即可

CREATE CERTIFICATE CT_Mirror_SerA
FROM FILE = 'C:\CT_Mirror_SerA.cer'


4.5、建立登录

CREATE LOGIN LOGIN_Mirror_SerA
FROM CERTIFICATE CT_Mirror_SerA


4.6、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror

TO LOGIN_Mirror_SerA

 

5、切换主服务器（SerA）执行

5.1、用共享文件夹里（D:\ShareFile\CT_Mirror_SerB.cer),建立主体服务器上的证书

CREATE CERTIFICATE CT_Mirror_SerB
FROM FILE = 'D:\ShareFile\CT_Mirror_SerB.cer'


5.2、建立登录

CREATE LOGIN LOGIN_Mirror_SerB
FROM CERTIFICATE CT_Mirror_SerB


5.3、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror
TO LOGIN_Mirror_SerB


6、在镜像服务器上启用数据库镜像，此操作镜像服务器上执行

ALTER DATABASE MirrorDB SET
PARTNER = 'TCP://SerA:5022'


 

7、在主体服务器上启用数据库镜像(默认为高安全性模式,所以不用进行模式设置)

7.1、此操作主体服务器上执行

ALTER DATABASE MirrorDB SET
PARTNER = 'TCP://SerB:5022'


7.2、在SSMS连接SerB实例，查看数据库显示状态

7.3、在主体数据库(MirrorDB)—右健—任务—启动数据库镜像监视器，显示如图：


8、配置见证服务器，切换到作镜像服务器(SerCWitness)执行(同步骤3相同)

8.1、使用数据库主密钥加密证书

IF NOT EXISTS(  
SELECT * FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Roy@123'
--创建证书
CREATE CERTIFICATE CT_Mirror_SerCWitness
WITH
SUBJECT = N'certificate for database mirror',
START_DATE = '19990101',
EXPIRY_DATE = '99991231'


8.2、备份证书并复制到网络共享路径（\\SerA\ShareFile\CT_Mirror_SerCWitness.cer）

BACKUP CERTIFICATE CT_Mirror_SerCWitness
TO FILE = 'E:\DBBak\CT_Mirror_SerCWitness.cer'


8.3、数据库镜像端点

CREATE ENDPOINT EDP_Mirror
STATE = STARTED 
AS TCP(
LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
LISTENER_IP = ALL)     -- 侦听的IP地址FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE CT_Mirror_SerCWitness, -- 证书身份验证
ENCRYPTION = DISABLED,                             -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法
ROLE = ALL)                                        -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)
GO


8.4、建立主体服务器上的证书(主体服务器上备份的证书已经复制到 E:\DBBak\CT_Mirror_SerA.cer)

8.4.1、创建证书

CREATE CERTIFICATE CT_Mirror_SerA
FROM FILE = 'E:\DBBak\CT_Mirror_SerA.cer'


8.4.2、 建立登录

CREATE LOGIN LOGIN_Mirror_SerA
FROM CERTIFICATE CT_Mirror_SerA


8.4.3、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror
TO LOGIN_Mirror_SerA


8.5、完成镜像服务器上数据库镜像端点的传输安全模式配置

8.5.1、建立镜像服务器上的证书(假设镜像服务器上备份的证书已经复制到 E:\DBBak\CT_Mirror_SerB.cer)

CREATE CERTIFICATE CT_Mirror_SerB
FROM FILE = 'E:\DBBak\CT_Mirror_SerB.cer'


8.5.2、建立登录

CREATE LOGIN LOGIN_Mirror_SerB
FROM CERTIFICATE CT_Mirror_SerB


8.5.3、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror
TO LOGIN_Mirror_SerB


GO

 

9、切换镜像服务器(SerB)添加安全配置

9.1、建立见证服务器上的证书

CREATE CERTIFICATE CT_Mirror_SerCWitness
FROM FILE = 'E:\BackDB\CT_Mirror_SerCWitness.cer'


9.2、建立登录

CREATE LOGIN LOGIN_Mirror_SerCWitness
FROM CERTIFICATE CT_Mirror_SerCWitness


9.3、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror
TO LOGIN_Mirror_SerCWitness


 

10、切换主服务器(SerA)添加安全配置

10.1、建立见证服务器上的证书(见证服务器上备份的证书已经复制到 共享网络路径D:\ShareFile\CT_Mirror_SerCWitness.cer)

CREATE CERTIFICATE CT_Mirror_SerCWitness
FROM FILE = 'D:\ShareFile\CT_Mirror_SerCWitness.cer'


10.2、建立登录

CREATE LOGIN LOGIN_Mirror_SerCWitness
FROM CERTIFICATE CT_Mirror_SerCWitness


10.3、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror
TO LOGIN_Mirror_SerCWitness


10.4、主体服务器上执行

ALTER DATABASE MirrorDB SET
WITNESS = 'TCP://SerCWitness:5022'


10.5、查询数据库状态

以下脚本可以在主体服务器和镜像服务器上执行,执行结果为镜像的状态

SELECT 
mirroring_role_desc,           -- 数据库在镜像会话中当前的角色
mirroring_state_desc,          -- 镜像当前状态
mirroring_safety_level_desc,   -- 镜像运行模式
mirroring_witness_state_desc   -- 与见证服务器的连接情况
FROM sys.database_mirroring
WHERE database_id = DB_ID(N'MirrorDB')


 

11、测试环境

11.1、切换主服务器(SerA),创建表+新增数据

CREATE TABLE MirrorDB.dbo.Tab(
ID INT IDENTITY(1,1),Name NVARCHAR(50));
go
INSERT INTO  MirrorDB.dbo.Tab(Name)VALUES('Roy');
Go


 

11.2、主备互换

ALTER DATABASE [MirrorDB] SET PARTNER FAILOVER;
Go


－－在切换镜像服务器(SerB)显示为主体数据库查看数据

select * from MirrorDB.dbo.Tab


12、删除示例配置（证书和数据库的备份需要在操作系统的资源管理器中删除）

12.1、主体服务器(SerA)上执行的操作

-- 12.1.1. 停止镜像和删除主体数据库
USE master
GOALTER DATABASE MirrorDB SETPARTNER OFF
DROP DATABASE MirrorDB
GO-- 12.1.2. 删除镜像端点
DROP ENDPOINT EDP_Mirror
GO-- 12.1.3. 删除登录及证书
DROP LOGIN LOGIN_Mirror_SerB
DROP LOGIN LOGIN_Mirror_SerCWitness
DROP CERTIFICATE CT_Mirror_SerA
DROP CERTIFICATE CT_Mirror_SerB
DROP CERTIFICATE CT_Mirror_SerCWitness
GO

12.2、 镜像服务器(SerB)上执行的操作 

-- 12.2.1. 删除镜像数据库
USE master
GODROP DATABASE MirrorDB
GO-- 12.2.2. 删除镜像端点
DROP ENDPOINT EDP_Mirror
GO-- 12.2.3. 删除登录及证书
DROP LOGIN LOGIN_Mirror_SerA
DROP LOGIN LOGIN_Mirror_SerCWitness
DROP CERTIFICATE CT_Mirror_SerA
DROP CERTIFICATE CT_Mirror_SerB
DROP CERTIFICATE CT_Mirror_SerCWitness
GO

12.3、见证服务器(SerCWitness)上执行的操作 
-- 12.3.1. 删除端点
DROP ENDPOINT EDP_Mirror
GO-- 12.3.2. 删除登录及证书
DROP LOGIN LOGIN_Mirror_SerA
DROP LOGIN LOGIN_Mirror_SerB
DROP CERTIFICATE CT_Mirror_SerA
DROP CERTIFICATE CT_Mirror_SerB
DROP CERTIFICATE CT_Mirror_SerCWitness
GO


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

这篇关于SQL SERVER【非域环境】镜像之搭建篇的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/223585。

相关文章

Mysql 中的多表连接和连接类型详解

Mysql 中的多表连接和连接类型详解

《Mysql中的多表连接和连接类型详解》这篇文章详细介绍了MySQL中的多表连接及其各种类型,包括内连接、左连接、右连接、全外连接、自连接和交叉连接,通过这些连接方式,可以将分散在不同表中的相关数据... 目录什么是多表连接？1. 内连接（INNER JOIN）2. 左连接（LEFT JOIN 或 LEFT
阅读更多...
Mycat搭建分库分表方式

Mycat搭建分库分表方式

《Mycat搭建分库分表方式》文章介绍了如何使用分库分表架构来解决单表数据量过大带来的性能和存储容量限制的问题,通过在一对主从复制节点上配置数据源,并使用分片算法将数据分配到不同的数据库表中,可以有效... 目录分库分表解决的问题分库分表架构添加数据验证结果 总结分库分表解决的问题单表数据量过大带来的性能
阅读更多...
Java汇编源码如何查看环境搭建

Java汇编源码如何查看环境搭建

《Java汇编源码如何查看环境搭建》：本文主要介绍如何在IntelliJIDEA开发环境中搭建字节码和汇编环境,以便更好地进行代码调优和JVM学习,首先,介绍了如何配置IntelliJIDEA以方... 目录一、简介二、在IDEA开发环境中搭建汇编环境2.1 在IDEA中搭建字节码查看环境2.1.1 搭建步
阅读更多...
mysql重置root密码的完整步骤(适用于5.7和8.0)

mysql重置root密码的完整步骤(适用于5.7和8.0)

《mysql重置root密码的完整步骤(适用于5.7和8.0)》：本文主要介绍mysql重置root密码的完整步骤,文中描述了如何停止MySQL服务、以管理员身份打开命令行、替换配置文件路径、修改... 目录第一步：先停止mysql服务，一定要停止！方式一：通过命令行关闭mysql服务方式二：通过服务项关闭
阅读更多...
SQL Server数据库磁盘满了的解决办法

SQL Server数据库磁盘满了的解决办法

《SQLServer数据库磁盘满了的解决办法》系统再正常运行,我还在操作中,突然发现接口报错,后续所有接口都报错了,一查日志发现说是数据库磁盘满了,所以本文记录了SQLServer数据库磁盘满了的解... 目录问题解决方法删除数据库日志设置数据库日志大小问题今http://www.chinasem.cn天发
阅读更多...
mysql主从及遇到的问题解决

mysql主从及遇到的问题解决

《mysql主从及遇到的问题解决》本文详细介绍了如何使用Docker配置MySQL主从复制,首先创建了两个文件夹并分别配置了`my.cnf`文件,通过执行脚本启动容器并配置好主从关系,文中还提到了一些... 目录mysql主从及遇到问题解决遇到的问题说明总结mysql主从及遇到问题解决1.基于mysql
阅读更多...
Python基于火山引擎豆包大模型搭建QQ机器人详细教程(2024年最新)

Python基于火山引擎豆包大模型搭建QQ机器人详细教程(2024年最新)

《Python基于火山引擎豆包大模型搭建QQ机器人详细教程(2024年最新)》：本文主要介绍Python基于火山引擎豆包大模型搭建QQ机器人详细的相关资料,包括开通模型、配置APIKEY鉴权和SD... 目录豆包大模型概述开通模型付费安装 SDK 环境配置 API KEY 鉴权Ark 模型接口Prompt
阅读更多...
在 VSCode 中配置 C++ 开发环境的详细教程

在 VSCode 中配置 C++ 开发环境的详细教程

《在VSCode中配置C++开发环境的详细教程》本文详细介绍了如何在VisualStudioCode（VSCode）中配置C++开发环境,包括安装必要的工具、配置编译器、设置调试环境等步骤,通... 目录如何在 VSCode 中配置 C++ 开发环境：详细教程1. 什么是 VSCode？2. 安装 VSCo
阅读更多...
MySQL的索引失效的原因实例及解决方案

MySQL的索引失效的原因实例及解决方案

《MySQL的索引失效的原因实例及解决方案》这篇文章主要讨论了MySQL索引失效的常见原因及其解决方案,它涵盖了数据类型不匹配、隐式转换、函数或表达式、范围查询、LIKE查询、OR条件、全表扫描、索引... 目录1. 数据类型不匹配2. 隐式转换3. 函数或表达式4. 范围查询之后的列5. like 查询6
阅读更多...
Linux下MySQL8.0.26安装教程

Linux下MySQL8.0.26安装教程

《Linux下MySQL8.0.26安装教程》文章详细介绍了如何在Linux系统上安装和配置MySQL,包括下载、解压、安装依赖、启动服务、获取默认密码、设置密码、支持远程登录以及创建表,感兴趣的朋友... 目录1.找到官网下载位置1.访问mysql存档2.下载社区版3.百度网盘中2.linux安装配置1.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2