读写分离配置详解（SQLServer）

读写分离配置详解（SQLServer）

一、前言

1. 背景

大部分场景中，DB操作80%是读，20%是写，对于时效性要求不高的数据，为了减少磁盘读和写的竞争，引入读写分离的概念，即在数据库上进行主从配置，一个主，多个从，实现主从同步，从而业务上实现读写分离。

读写分离在网站发展初期可以一定程度上缓解读写并发时产生锁的问题，将读写压力分担到多台服务器上。基本原理是让主数据库处理增、删、操作，而从数据库处理SELECT查询操作。随着系统的业务量不断增长，数据不断增多，数据库的IO操作压力会很大，读写分离也是数据库分库的一种方案。

(1). 主库：叫读写库，主要用来处理 增删改，特殊情况也可以查。

(2). 从库：叫只读库，主要用来查询数据。

2. 需要解决的问题

在业务上区分哪些业务是允许一定时间延迟的，可以接受数据同步的耗时。

3. 常见实现方式

复制模式、镜像传输、日志传输、和 Always On技术

二、SQLServer各种模式介绍

1. 复制模式

(1). 简介

复制模式也被称为发布-订阅模式，是由主服务器进行发布消息，备份服务器进行订阅，当主服务器数据发生变更时，就会发布消息，备份服务器读取消息进行同步更新，中间过程延迟比较短。

复制方式是以前很常见的一种主备，速度快，延迟小，可以支持部分同步等优点，但是也有一个很明显的缺点，因为是部分同步，如果是表修改，可以主动同步，但是如果是新增表、视图等操作，必须在发布属性中，将新加的表或者视图添加到同步配置中，否则对这个表做的任何操作都不会同步。

复制模式同步，要求数据库名称和主机名称必须一致，否则查找不到数据库主机；要求数据库不能使用端口，必须是可以通过ip直接访问的。

(2). 发布分为4种模式：

A.快照发布

发布服务器按'预定的时间间隔'向订阅服务器发送已发布数据的快照。

快照发布，就是将所有要发布的内容，做成一个镜像文件，然后一次性复制到订阅服务器，两次快照之间的更新不会实时同步,而是按照设置的'预定间隔'进行。这种方式占用带宽较多，因此比较适用内容不是很大，或者更新不需要很频繁的场景。

B.事务发布

在订阅服务器收到已发布数据的初始快照后，发布服务器将事务流式传输到订阅服务器。

事务发布，是在第一次设置好事务复制之后，所有发布的内容都会进行镜像快照，订阅服务器收到已发布数据的初始快照后，发布服务器将事务流式传输到订阅服务器。当主服务器数据发生变更时，会通过日志传递同步

给订阅服务器，数据近似于同步更新。

此方式会对主服务器性能造成很大影响（实时同步每次变更，而不是最终变更），适用于对数据及时性要求比较严格主备方案，但是目前已被微软提供的集群Always On所取代。

C.对等发布

对等发布支持多主复制。发布服务器将事务流式传输到拓扑中的所有对等方。所有对等节点可以读取和写入更改，且所有更改将传播到拓扑中的所有节点。

D.合并发布

合并发布是相当于两台都是主服务器，都可以对数据进行更新修改等操作，然后定时将发布服务器上的内容与订阅服务器上的内容进行合并，并根据配置保留相应内容，此种很少用。

(3).该模式的订阅分两种：

A.请求订阅：从数据库按照既定的周期来请求主数据库，将增量数据脚本获取回去执行，从而实现数据的同步。

B.推送订阅：主数据库数据有变更的时候，会将增量数据脚本主动发给各个从数据库(性能优于请求订阅模式，建议使用)。

2.镜像传输

数据库镜像传输，严格来说不是主从架构，而是主备架构，将两台数据库服务器通过一台中间监控服务器关联起来，两台服务器通过镜像文件，实时同步数据（有延迟，延迟很短）。当主服务器宕机之后，监控服务器自动切换到备份服务器上。

此方案优点是可以快速的切换主备方案，相比较Always on集群，可以不用共享磁盘即可实现，避免了数据库集群存储单点故障，导致整个集群崩溃。

缺点也很明显，无论是主备服务器，要实现同步操作，都是依赖于性能低的那一端，因此两台服务器都要是高性能的才可以保证同步的及时性；同时备份服务器只是备份和故障转移，不能提供从服务器的只读访问，

因此才说是主备服务器，而且是一对一，只能有一台备份服务器。

3. 日志传输

与镜像传输模式类似，是将主数据库日志备份，发送到从服务器上，然后从服务器还原日志，更新数据。

此方式优点在于从服务器可以有多台从服务器，而且当主服务器脚本操作异常后，只需要在日志同步之前，及时拦截日志传输，即可保留从服务器数据，减少灾难损失；此方式相较于“复制发布”模式，还有一个有点就是无论是新增表、视图等等，都会通过日志同步给从服务器，而复制模式不行

而相应的缺点就是通过日志备份传输，在还原，会有较大的时间延迟。而且无法自动转移故障，只能手动转移。

4. Always On技术

AlwaysOn是基于Windows的故障转移集群，集群技术是微软提供的，可用性最高的主备方案。它是将多台服务器通过一个共享的外部存储区域（SAN），连接成一个资源共享的服务器群体，数据库文件和实例，都存放并运行在该共享区域节点上，每台服务器相当于一个节点，共同访问共享的节点实例。服务器只有一个节点处于活动状态，当活动节点出现故障，会有其他节点主动启动，取代当前故障点，整个过程只需要几秒钟，用户无法感知。

集群有很多优点，是目前最高效的高可用技术，但是他也有很明显的缺点，所有的节点，都依赖于共享节点实例，如果共享节点出现故障，将会导致整个集群失去作用，且很难恢复。

三、发布与订阅模式

（1）实验环境说明

实验环境，两台在同一局域网（跨网段的在后面再详细说明）的PC机，这里PC1是作为分发服务器，PC2作为订阅服务器，PC2从PC1上获得数据

PC1：

数据库版本：SQL Server 2012

IP：192.168.1.197

Sqlserver端口：1433

计算机名:BPMS

用户名：SqlServer

PC2：

数据库版本：SQL Server 2012

IP：192.168.1.198

Sqlserver端口：1433

计算机名: WIN-J37F34U8TQS

用户名：SqlServer

（2）实验前准备

1.首先两个服务器要能ping通，则需要做以下操作

1.1开共享，在任务栏的计算机图标右键->打开网络和共享中心->更改高级共享设置，在高级共享里面开启共享

1.2防火墙开1433 端口

标题

1.3配置管理器设置TCP/IP启用

打开SQL Server 2012 配置管理器，网络配置->协议->TCP/IP开启

选中TCP/IP右键“属性”，在属性中把对应的ip 192.168.0.197的Enabled设置为“是”，端口号为默认的1433

 

1.4 Sql Server Browser 服务开启（如果在SSMS工具中找不到局域网内的其他数据库服务器，可以开启这个功能）

 

1.5两台机有相同的账户（用户名和密码要一致）

数据库添加windows账户访问，打开SSMS添加上面新建的windows账户SqlServer

1.6 数据库开启允许远程连接，在SSMS中开启数据库允许远程连接

2.测试两个服务器是否能ping通

就算能ping通，只能说明局域网内存在这台机，也不代表能实现同步，还要测试端口是否可用。

使用telnet命令测试端口是否可用 telnet 192.168.1.198 1433，出现这个窗口表示可用

（3）发布设置

已经在PC1新建了一个新的数据库Lx_Data1，Lx_Data1里面有一张表t_student, t_student插入了几条测试数据，把数据库Lx_Data1备份复制到PC2机器上还原。

配置PC1作为分发服务器

打开数据库的 复制->本地发布，右键新建发布

这里的发布数据库选择的是测试用的Lx_Data1，我们的实验就是要把PC1的Lx_Data1数据库同步到PC2的数据库Lx_Data1,下面步骤按照截图操作

 

直接下一步

 

（4）订阅设置

已经在PC2新建了一个新的数据库Lx_Data1，Lx_Data1里面也有一张表t_student, t_student空表，把PC1上面的数据库Lx_Data1备份复制到PC2机器上还原。

配置PC2作为订阅服务器

打开数据库的 复制->本地订阅，右键新建订阅，下面步骤按照截图操作

注：每次完成发布订阅操作后，会自动在SQLServer代理的作业中生成关于本次发布订阅操作的作业计划，每次会随着SQLServer代理的启动开始其执行计划。

（5）测试同步与订阅

PC1数据库Lx_Data1中t_student表中的数据如下：

PC2数据库Lx_Data1中t_student表中的数据如下：

选中发布服务器右键，启动复制监视器

选中订阅服务器右键，查看同步状态

查看同步后的订阅服务器数据库Lx_Data1中t_student表如下

 

再进行插入测试，插入3条数据

USE [Lx_Data1]

GO

INSERT INTO [dbo].[t_student]

           ([Id],[Name],[Age],[School],[Class],[Score],[Height])

     VALUES(9,'123',15,'ws','15',15.0,NUll)

INSERT INTO [dbo].[t_student]

    ([Id],[Name],[Age],[School],[Class],[Score],[Height])

VALUES(10,'1234',15,'ws','15',15.0,NUll)

INSERT INTO [dbo].[t_student]

           ([Id],[Name],[Age],[School],[Class],[Score],[Height])

VALUES(11,'1235',15,'ws','15',15.0,NUll)

GO

删除测试

DELETE FROM [dbo].[t_student] WHERE Id=10

GO