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。

相关文章

Python使用国内镜像加速pip安装的方法讲解

Python使用国内镜像加速pip安装的方法讲解

《Python使用国内镜像加速pip安装的方法讲解》在Python开发中,pip是一个非常重要的工具,用于安装和管理Python的第三方库,然而,在国内使用pip安装依赖时,往往会因为网络问题而导致速... 目录一、pip 工具简介1. 什么是 pip？2. 什么是 -i 参数？二、国内镜像源的选择三、如何
阅读更多...
Springboot中分析SQL性能的两种方式详解

Springboot中分析SQL性能的两种方式详解

《Springboot中分析SQL性能的两种方式详解》文章介绍了SQL性能分析的两种方式：MyBatis-Plus性能分析插件和p6spy框架,MyBatis-Plus插件配置简单,适用于开发和测试环... 目录SQL性能分析的两种方式:功能介绍实现方式:实现步骤:SQL性能分析的两种方式:功能介绍记录
阅读更多...
使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

《使用sql-research-assistant进行SQL数据库研究的实战指南(代码实现演示)》本文介绍了sql-research-assistant工具,该工具基于LangChain框架,集... 目录技术背景介绍核心原理解析代码实现演示安装和配置项目集成LangSmith 配置（可选）启动服务应用场景
阅读更多...
oracle DBMS_SQL.PARSE的使用方法和示例

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包，它允许动态地构建和执行
阅读更多...
SQL 中多表查询的常见连接方式详解

SQL 中多表查询的常见连接方式详解

《SQL中多表查询的常见连接方式详解》本文介绍SQL中多表查询的常见连接方式,包括内连接（INNERJOIN）、左连接（LEFTJOIN）、右连接（RIGHTJOIN）、全外连接（FULLOUTER... 目录一、连接类型图表（ASCII 形式）二、前置代码（创建示例表）三、连接方式代码示例1. 内连接（I
阅读更多...
在MySQL执行UPDATE语句时遇到的错误1175的解决方案

在MySQL执行UPDATE语句时遇到的错误1175的解决方案

《在MySQL执行UPDATE语句时遇到的错误1175的解决方案》MySQL安全更新模式（SafeUpdateMode）限制了UPDATE和DELETE操作,要求使用WHERE子句时必须基于主键或索引... mysql 中遇到的 Error Code: 1175 是由于启用了 安全更新模式（Safe Upd
阅读更多...
docker如何删除悬空镜像

docker如何删除悬空镜像

《docker如何删除悬空镜像》文章介绍了如何使用Docker命令删除悬空镜像,以提高服务器空间利用率,通过使用dockerimage命令结合filter和awk工具,可以过滤出没有Tag的镜像,并将... 目录docChina编程ker删除悬空镜像前言悬空镜像docker官方提供的方式自定义方式总结docker
阅读更多...
轻松上手MYSQL之JSON函数实现高效数据查询与操作

轻松上手MYSQL之JSON函数实现高效数据查询与操作

《轻松上手MYSQL之JSON函数实现高效数据查询与操作》：本文主要介绍轻松上手MYSQL之JSON函数实现高效数据查询与操作的相关资料,MySQL提供了多个JSON函数,用于处理和查询JSON数... 目录一、jsON_EXTRACT 提取指定数据二、JSON_UNQUOTE 取消双引号三、JSON_KE
阅读更多...
MySql死锁怎么排查的方法实现

MySql死锁怎么排查的方法实现

《MySql死锁怎么排查的方法实现》本文主要介绍了MySql死锁怎么排查的方法实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧... 目录前言一、死锁排查方法1. 查看死锁日志方法 1：启用死锁日志输出方法 2：检查 mysql 错误
阅读更多...
MySQL数据库函数之JSON_EXTRACT示例代码

MySQL数据库函数之JSON_EXTRACT示例代码

《MySQL数据库函数之JSON_EXTRACT示例代码》：本文主要介绍MySQL数据库函数之JSON_EXTRACT的相关资料,JSON_EXTRACT()函数用于从JSON文档中提取值,支持对... 目录前言基本语法路径表达式示例示例 1: 提取简单值示例 2: 提取嵌套值示例 3: 提取数组中的值注意
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2