SqlServer实现主从复制读写分离操作说明

SQL Server 实现主从复制与读写分离操作说明

案卷号

日期

2020/05/21

SQL Server 实现主从复制

与读写分离操作说明

作 者：

完成日期： 2020/05/21

签 收 人：

签收日期：

编撰情况记录：

序号

1

2

编撰人

杨胜灵

杨胜灵

操作日期 操作内容

2020/05/18

前半部分

2020/05/21

完成整理

SQL Server 实现主从复制与读写分离操作说明

目录：

1数据库主从复制及读写分离概述 ................................................................................................ 3

2实施步骤操作说明 ........................................................................................................................ 3

2.1数据复制组件安装配置 ..................................................................................................... 3

2.2准备工作............................................................................................................................. 6

2.3配置出版服务器（分发服务器） ..................................................................................... 6

2.4测试数据库信息 ............................................................................................................... 15

2.5配置订阅服务器 ............................................................................................................... 15

2.6数据同步测试 ................................................................................................................... 20

SQL Server 实现主从复制与读写分离操作说明

SQL Server实现主从复制读写分离操作说明

整理编写：杨胜灵

实验日期：2020/05/17（本地）、2020/05/20（公网）

文档日期：2020/05/18~2020/05/21

1数据库主从复制及读写分离概述

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主从复制也可以理解为数据同步。

主数据库一般是实时的业务数据库。处理事务性增加、修改、删除操作，还可以包括实时性强的查询操作。

从数据库的作用和使用场景如下：

1) 作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作；

2) 可在从数据库作备份、数据查询等工作，这样不影响主数据库的性能；

3) 用于数据分析，辅助丛书数据库与主数据库一模一样，数据分析不影响主数据库的使用。

实现读写分离的好处：

1) 提高了系统性能，硬件资源充足的情况下，将会成倍提高数据库负载能力。

2) 是缓解数据负载最简单可行的方案；无需重构数据库表结构、修改程序代码，节省时间成本、人力成本。

3) 读写分离通过主从备份数据，保证了系统的冗余，避免了卡顿情况下引起的数据丢失问题。

4) 一定程度上解决复杂查询统计所导致的系统性能瓶颈。

5) 极大的增强了数据安全。

2实施步骤及操作说明

2.1数据复制组件安装配置

配置数据库同步前，请确保每台数据库系统环境一致，未安装复制组件时，请安装配置SQL Server 复制组件。如果已经配置安装复制组件，可略过此节。

1,从安装介质启动SQL Server 安装中心：

SQL Server 实现主从复制与读写分离操作说明

如上图，点击“”启动SQL Server 安装中心：

2,选择“全新SQL Server独立安装或向现有安装添加功能”

如上图所示，点击“安装”,选择“全新SQL Server独立安装或向现有安装添加功能”,出现“安装类型”窗体:

SQL Server 实现主从复制与读写分离操作说明

3,安装“SQL Server 复制”组件

如上图，选择在现有实例中添加功能，选择“SQL Server 复制”组件，进行安装，直到安装成功：

SQL Server 实现主从复制与读写分离操作说明

2.2准备工作

配置分发服务器前，如果是正在运行的业务系统，请提出配置申请,并取得客户的支持和允许。

确保主服务器、从服务器环境一致；同时，暂停业务系统

备份数据库，同时将数据库备份包还原到从服务器。

2.3配置出版服务器（分发服务器）

在数据库管理工具中，点选“本地发布”,新建发布。

创建发布时，请确保SQL Server代理服务启动。

SQL Server 实现主从复制与读写分离操作说明

选择“新建发布”右键菜单，打开“新建发布向导”：

点击“下一步”选择分发服务器。默认选择当前运行的主数据库为“出版发布服务器”。

点击“下一步”后，如果未设置“SQL Server代理”服务自动启动，则出现如下图所示界面：

SQL Server 实现主从复制与读写分离操作说明

点击“下一步”继续配置。

选择快照文件夹，最好放到磁盘间较大的且不是系统盘的磁盘分区上。

点击“下一步”继续配置。

选择“发布数据库”，选择业务系统当前使用的数据库名称。

SQL Server 实现主从复制与读写分离操作说明

点击“下一步”继续配置。

选择“发布类型”,此处根据业务需要，选择“事务发布”（如下图）。

发布类型介绍：

1）快照发布

快照发布指在某一时刻给出版数据库中的出版数据照相，然后将数据复制到订阅者服务

SQL Server 实现主从复制与读写分离操作说明

器。快照复制实现较为简单，其所复制的只是某一时刻数据库的瞬间数据，快照复制是将整个出版物传送给订阅者，就是在某一时刻将出版数据进行一次“照相”，生成一个描述出版数据库中数据的当前状态的一个文件，然后在相应的时间将其复制到订阅的数据库上，快照复制并不是不停的监视出版数据库中发生的变化情况，它是对出版数据库进行一次扫描，把所有出版数据中的数据从源数据库送至目标数据库，而不仅仅是变化的数据。如果数据量很大，那么要复制的数据就很多。因此对网络资源要求很高，不仅要有较快的传输速度，而且要保证传输的可靠性。快照复制是最为简单的一种复制类型，能够在出版者和订阅者之间保证数据的一致性。快照复制通常使用在以下场合：在一定时间内出现大量的更改的操作，但数据总量不大，变化周期较长。

2）事务发布

快照发布是将整个数据集发送给订阅服务器，由于体积大而造成复制周期较长，会形成复制滞后问题。那么事务复制使用事务日志来生成将复制到订阅服务器的事务，因为它只复制事务也就是变化，所以滞后也比快照复制低得多。

3）合并发布

合并发布是为移动用户设计的，可以在发布服务器或是订阅服务器处执行修改，在合并代理运行时，这些修改将同步，多用于发布服务器与订阅服务都修改数据的情况下。工作原理如下：在要复制的每个表上实现触发器，并使用包含GUID列唯一标识要复制的表中的每一行。对其中的任何一个表进行修改时，都会将更改记录到一个数据表中，在合并代理运行时，它收集数据表中的GUID，这些GUID指出了在发布服务器和订阅服务器处修改过的行。对于只在发布服务器或是订阅端修改的数据则直接进行相应操作，如INSERT，UPDATE，DELETE，如果双方都有GUID则按照用户指定的方式解决冲突，默认发布服务器优先。

3）对等发布

数据库集群中使用，此处不做介绍。

点击“下一步”继续配置。

选择发布数据库中的表、视图等对象，此处选择数据库中所有表即可。

如果数据表中存在没有主键的数据表，是不支持数据同步的。请首先创建主键后再开始配置数据同步操作。

SQL Server 实现主从复制与读写分离操作说明

点击“下一步”继续配置。

筛选功能支持数据的筛选，此处略过，不添加筛选条件。

点击“下一步”继续配置。

选择理解创建快照并使快照保持可用状态。以初始化订阅。

SQL Server 实现主从复制与读写分离操作说明

点击“下一步”继续配置。

代理安全性中，点击“安全配置”按钮，快照代理安全性配置中，请输入当前操作系统账号和密码。

点击“下一步”继续配置。

SQL Server 实现主从复制与读写分离操作说明

到此为止，分发服务器配置完毕，在本地发布下，可看到刚刚配置发布项:

点击改发布配置项，可设置快照保持路径等属性，如下图所示。

SQL Server 实现主从复制与读写分离操作说明

启动代理界面示意图：

代理状态启动成功示意图：

SQL Server 实现主从复制与读写分离操作说明

2.4测试数据库信息

[TestA]为主数据库，分发数据库。

[TestB]为从数据库，订阅数据库。

如下图所示：

2.5配置订阅服务器

在数据库管理工具中，右键选择“本地订阅”菜单，选择发布订阅功能，弹出“新建订阅向导”，如下图所示：

SQL Server 实现主从复制与读写分离操作说明

点击“下一步”继续配置。

选择发布服务器(本地TestA)：

点击“下一步”继续配置。

请求订阅：由订阅服务器来决定什么时候去更新数据。

推送订阅：由发布数据库决定什么时候向订阅服务发生更新数据。

为了节省主服务器资源，我们选择请求订阅方式，“在其订阅服务器上运行每个代理”：

SQL Server 实现主从复制与读写分离操作说明

点击“下一步”继续配置。

选择订阅数据库[TestB]：

SQL Server 实现主从复制与读写分离操作说明

点击“下一步”继续配置。

同步机会，选择默认“连续运行”：]

选择“下一步”完成订阅配置。

SQL Server 实现主从复制与读写分离操作说明

点击“完成”按钮继续。

等待创建订阅成功后，关闭窗体，完成配置。

同一服务器完成主从复制的配置项如下如所示：

SQL Server 实现主从复制与读写分离操作说明

2.6数据同步测试

1，插入测试数据

TestA插入数据脚本：

INSERT INTO [tb_User]([UserName],[UserPwd],[CreateTime]) VALUES('张三','abcdfg',GetDate())

INSERT INTO [tb_User]([UserName],[UserPwd],[CreateTime]) VALUES('李四','abcdfg',GetDate())

INSERT INTO [tb_User]([UserName],[UserPwd],[CreateTime]) VALUES('王五','abcdfg',GetDate())

打开数据库TestB ,查询数据复制成功。

2测试update操作

SQL Server 实现主从复制与读写分离操作说明

数据库TestA更新了LoginCount值。

打开数据库TestB看到数据更新：

3删除操作测试

删除掉TestA数据库一张表的最好一条数据。

SQL Server 实现主从复制与读写分离操作说明

打开数据库TestB可以看到，从数据库ID为6的记录已经删除

至此，主从数据库数据同步测试成功。

特别注意：

由于采用事务发布的复制策略，从数据库服务器不能进行增加、删除、修改操作，只是读取统计。

实际应用时，将数据分析统计部分的数据库连接信息变更为订阅数据库服务器的连接信息即可。