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复杂SQL之多表联查/子查询详细介绍(最新整理)

MySQL复杂SQL之多表联查/子查询详细介绍(最新整理)

《MySQL复杂SQL之多表联查/子查询详细介绍(最新整理)》掌握多表联查(INNERJOIN,LEFTJOIN,RIGHTJOIN,FULLJOIN)和子查询（标量、列、行、表子查询、相关/非相关、... 目录第一部分：多表联查 (JOIN Operations)1. 连接的类型 (JOIN Types)
阅读更多...
MySQL版本问题导致项目无法启动问题的解决方案

MySQL版本问题导致项目无法启动问题的解决方案

《MySQL版本问题导致项目无法启动问题的解决方案》本文记录了一次因MySQL版本不一致导致项目启动失败的经历,详细解析了连接错误的原因,并提供了两种解决方案：调整连接字符串禁用SSL或统一MySQL... 目录本地项目启动报错报错原因：解决方案第一个：第二种：容器启动mysql的坑两种修改时区的方法：本地
阅读更多...
CnPlugin是PL/SQL Developer工具插件使用教程

CnPlugin是PL/SQL Developer工具插件使用教程

《CnPlugin是PL/SQLDeveloper工具插件使用教程》：本文主要介绍CnPlugin是PL/SQLDeveloper工具插件使用教程,具有很好的参考价值,希望对大家有所帮助,如有错... 目录PL/SQL Developer工具插件使用安装拷贝文件配置总结PL/SQL Developer工具插
阅读更多...
MyBatis设计SQL返回布尔值(Boolean)的常见方法

MyBatis设计SQL返回布尔值(Boolean)的常见方法

《MyBatis设计SQL返回布尔值(Boolean)的常见方法》这篇文章主要为大家详细介绍了MyBatis设计SQL返回布尔值(Boolean)的几种常见方法,文中的示例代码讲解详细,感兴趣的小伙伴... 目录方案一：使用COUNT查询存在性（推荐）方案二：条件表达式直接返回布尔方案三：存在性检查（EXI
阅读更多...
Python远程控制MySQL的完整指南

Python远程控制MySQL的完整指南

《Python远程控制MySQL的完整指南》MySQL是最流行的关系型数据库之一,Python通过多种方式可以与MySQL进行交互,下面小编就为大家详细介绍一下Python操作MySQL的常用方法和最... 目录1. 准备工作2. 连接mysql数据库使用mysql-connector使用PyMySQL3.
阅读更多...
Linux中修改Apache HTTP Server(httpd)默认端口的完整指南

Linux中修改Apache HTTP Server(httpd)默认端口的完整指南

《Linux中修改ApacheHTTPServer(httpd)默认端口的完整指南》ApacheHTTPServer（简称httpd）是Linux系统中最常用的Web服务器之一,本文将详细介绍如何... 目录一、修改 httpd 默认端口的步骤1. 查找 httpd 配置文件路径2. 编辑配置文件3. 保存
阅读更多...
Mysql的主从同步/复制的原理分析

Mysql的主从同步/复制的原理分析

《Mysql的主从同步/复制的原理分析》：本文主要介绍Mysql的主从同步/复制的原理分析,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录为什么要主从同步？mysql主从同步架构有哪些？Mysql主从复制的原理/整体流程级联复制架构为什么好？Mysql主从复制注意
阅读更多...
python获取cmd环境变量值的实现代码

python获取cmd环境变量值的实现代码

《python获取cmd环境变量值的实现代码》：本文主要介绍在Python中获取命令行（cmd）环境变量的值,可以使用标准库中的os模块,需要的朋友可以参考下... 前言全局说明在执行py过程中，总要使用到系统环境变量一、说明1.1 环境:Windows 11 家庭版 24H2 26100.4061
阅读更多...
如何搭建并配置HTTPD文件服务及访问权限控制

如何搭建并配置HTTPD文件服务及访问权限控制

《如何搭建并配置HTTPD文件服务及访问权限控制》：本文主要介绍如何搭建并配置HTTPD文件服务及访问权限控制的问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、安装HTTPD服务二、HTTPD服务目录结构三、配置修改四、服务启动五、基于用户访问权限控制六、
阅读更多...
如何解决Druid线程池Cause:java.sql.SQLRecoverableException:IO错误:Socket read timed out的问题

如何解决Druid线程池Cause:java.sql.SQLRecoverableException:IO错误:Socket read timed out的问题

《如何解决Druid线程池Cause:java.sql.SQLRecoverableException:IO错误:Socketreadtimedout的问题》：本文主要介绍解决Druid线程... 目录异常信息触发场景找到版本发布更新的说明从版本更新信息可以看到该默认逻辑已经去除总结异常信息触发场景复
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2