sqlserver-实现读写分离，主从复制的具体步骤

文章目录

• sqlserver实现读写分离，主从复制的具体步骤
  • 读写分离概述
  • 读写分离的好处
  • 适用读写分离场景
  • 读写分离实现方式
  • 主从复制
  • SQLserver读写分离方案对比
  • 具体步骤
    • 发布订阅的方式

sqlserver实现读写分离，主从复制的具体步骤

前期是简单的介绍，内容来源于其他作者的文章。后期更新具体步骤，一定特别的详细。

读写分离概述

读写分离从字面意思就可以理解，就是把对数据库的读操作和写操作分离开。

读写分离在网站发展初期可以一定程度上缓解读写并发时产生锁的问题，将读写压力分担到多台服务器上，通常用于读远大于写的场景。
读写分离的基本原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。

数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。单表的数据量限制，当单表数据量到一定条数之后数据库性能会显著下降。

数据多了之后，对数据库的读、写就会很多。分库减少单台数据库的压力。
以oracle为例，主库负责写数据、读数据。读库仅负责读数据。

每次有写库操作，同步更新cache，每次读取先读cache在读DB。

写库就一个，读库可以有多个，采用dataguard来负责主库和多个读库的数据同步。

读写分离的好处

1.数据是网站的生命，读写分离通过主从备份数据，保证了系统的冗余，保护了珍贵的数据。
2.提高了系统性能，一定程度提高了数据库负载能力。

适用读写分离场景

1.网站初期想要缓解数据负载最简单可行的方案。
2.服务器面对的是读远大于写的场景，并且业务能够允许时间上一些延迟。

读写分离实现方式

目前读写分离方案网上找了几个并做了对比。
1.mycat 基于阿里的cobar改版的（比较稳定，论坛活跃）
2.atlas 360开发的 网友说不是很稳定 （已经很久没更新）
3.mysql-proxy mysql自带 （不是很稳定）
4.oneproxy 比较稳定 性能也很好 （需要付费）
5.amoeba 好像还行，有一些公司使用 （但是很久没有更新了）

主从复制

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；
主数据库一般是实时的业务数据库，从数据库的作用和使用场合一般有几个：
一是作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作；
二是可在从数据库作备份、数据统计等工作，这样不影响主数据库的性能；
mysql支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。
mysql复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。
因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器接收主服务器已经记录的二进制日志来保存更新。
当一个从服务器连接主服务器时，它通知主服务器从日志中读取最后一次成功更新的位置。
从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。
从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新

SQLserver读写分离方案对比

读写分离方案	实时同步	副本数据是否直接可读	副本数	最小粒度	副本建立索引	环境	缺点
镜像	是	否（需要开启快照，只读）	1	库	否	域/非域(使用证书)	在高安全模式下对主库性能有一定影响
log shipping	否	是（只读）	N	库	否	UNC方式可访问	副本库在做resotre时会断开已连接用户连接/可能影响常规日志备份
发布订阅	是	是（读写，但写可能会产生数据不一致）	N	表（查询)	是	域/非域	在主库上有大量DML操作时，对分发服务器会有一定影响，且订阅数据库可能有数据同步延迟
always on	是	是（只读）	4(sql 2012) 8(sql 2014)	库	否	域	非域环境无法使用

具体步骤

闲暇时间，再来完善。

发布订阅的方式

作者：zhang6107563
原文：https://blog.csdn.net/zhang6107563/article/details/82894361
内容：
设置两台电脑固定局域网ip

sqlserver配置管理器-sqlserver网络配置-tcp/ip 启用 并设置ip3的ip地址为本机的局域网固定ip，所有端口设置成1433 并启用
设置ipAll的端口为1433 重启sqlserver服务

防火墙需要打开1433端口

至此 数据库可以通过局域网链接

sqlserver配置管理器-sqlserver网络配置-named pipes设置为启用
启动sqlserverBrowser服务:
我的电脑 右键 - 管理 - 服务与应用程序
找到SQL Server Browser 右键 属性
将禁用改为自动|手动-应用-启动

通过sql:
SELECT @@SERVERNAME,SERVERPROPERTY(‘SERVERNAME’)
可以获取到本机的服务器名\实例名
使用获取到的服务器名\实例名连接sqlserver

主服务器：创建发布
复制-本地发布-一路下一步-选择要发布的数据库-合并发布-全选-下一步*2-立即创建快照-更改计划，设置每1天运行一次 间隔10-60分钟，00.00-23.59执行。-代理全部选择第二个-下一步-设置发布名-完成
从服务器：设置订阅
发布服务器：查找sqlserver发布服务器 连接到主服务器（用机器名连接）选择要订阅的发布（不出意外的话就一个）-选择在分发服务器上运行所有代理
-订阅数据库直接新建（提前建好也可以）-代理全部选择第二个-代理计划为连续运行-初始化 立即-下一步-完成

 

SQL Server AlwaysOn读写分离配置

pursuer.chen

标签：MSSQL/只读路由

概述  

Alwayson相对于数据库镜像最大的优势就是可读副本，带来可读副本的同时还添加了一个新的功能就是配置只读路由实现读写分离；当然这里的读写分离稍微夸张了一点，只能称之为半读写分离吧！看接下来的文章就知道为什么称之为半读写分离。

数据库：SQLServer2014

db01:192.168.1.22

db02:192.168.1.23

db03:192.168.1.24

监听ip:192.168.1.25

配置可用性组

可用性副本概念

辅助角色支持的连接访问类型

1.无连接
不允许任何用户连接。 辅助数据库不可用于读访问。 这是辅助角色中的默认行为。

2.仅读意向连接
辅助数据库仅接受ApplicationIntent=ReadOnly 的连接,其它的连接方式无法连接。

3.允许任何只读连接
辅助数据库全部可用于读访问连接。 此选项允许较低版本的客户端进行连接。

主角色支持的连接访问类型

1.允许所有连接
主数据库同时允许读写连接和只读连接。 这是主角色的默认行为。

2.仅允许读/写连接
允许ApplicationIntent=ReadWrite或未设置连接条件的连接。 不允许 ApplicationIntent=ReadOnly的连接。 仅允许读写连接可帮助防止客户错误地将读意向工作负荷连接到主副本。

配置语句

---查询可用性副本信息
SELECT * FROM master.sys.availability_replicas

---建立read指针 - 在当前的primary上为每个副本建立副本对于的tcp连接
ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db01' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db01.ag.com:1433'))

ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db02' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db02.ag.com:1433'))

ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db03' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db03.ag.com:1433'))


----为每个可能的primary role配置对应的只读路由副本
--list列表有优先级关系，排在前面的具有更高的优先级,当db02正常时只读路由只能到db02，如果db02故障了只读路由才能路由到DB03
ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db01' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('db02','db03')));

ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db02' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('db01','db03')));

--查询优先级关系
SELECT  ar.replica_server_name ,
        rl.routing_priority ,
        ( SELECT    ar2.replica_server_name
          FROM      sys.availability_read_only_routing_lists rl2
                    JOIN sys.availability_replicas AS ar2 ON rl2.read_only_replica_id = ar2.replica_id
          WHERE     rl.replica_id = rl2.replica_id
                    AND rl.routing_priority = rl2.routing_priority
                    AND rl.read_only_replica_id = rl2.read_only_replica_id
        ) AS 'read_only_replica_server_name'
FROM    sys.availability_read_only_routing_lists rl
        JOIN sys.availability_replicas AS ar ON rl.replica_id = ar.replica_id

注意：这里只是针对可能成为主副本的角色进行配置，这里没有给db03配置只读路由列表，原因是不想将主副本切换到DB03上面来，配置越多的主副本意味着你后面要做越多的事情包括备份、作业等。

到此只读路由已配置完成，不要忘记在每个alwayson副本上创建登入用户。

登入方式

C#连接字符串

server=侦听IP;database=;uid=;pwd=;ApplicationIntent=ReadOnly

ssms：其它连接参数

---仅意向读连接
ApplicationIntent=ReadOnly
---读写连接
ApplicationIntent=ReadWrite

配置hosts 

--配置使用监听ip进行连接
192.168.1.22    db01.ag.com 
192.168.1.23    db02.ag.com
192.168.1.24    db03.ag.com
--配置使用hostname进行连接
192.168.1.22    db01
192.168.1.23    db02
192.168.1.24    db03

注意：这一步只是在没有加入域的客户端进行配置，如果非域的客户端没有配置hosts无法使用监听IP和hostname进行连接，数据库服务器端不需要配置此项！！！

连接测试

1.ReadOnly

可以看到使用ApplicationIntent=ReadOnly连接属性正确的连接到了只读副本DB02上。ApplicationIntent=ReadWrite同理。

20170714补充

SQLServer2016支持多个只读副本负载分担只读操作,只读路由列表修改如下：

ALTER AVAILABILITY GROUP [Alwayson21]
MODIFY REPLICA ON
N'HD21DB01' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB02','HD21DB03','HD21DB04'),'HD21DB01')));

ALTER AVAILABILITY GROUP [Alwayson21]
MODIFY REPLICA ON
N'HD21DB02' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB01','HD21DB03','HD21DB04'),'HD21DB02')));

当HD21DB01作为主节点时，HD21DB02,HD21DB03,HD21DB04平均分摊读的压力，当HD21DB02,HD21DB03,HD21DB04都无法访问时读连接访问HD21DB01；演示如下：

概述  

从上面我们可以看到只读路由的读写分离是通过连接属性ApplicationIntent=ReadOnly\ReadWrite使得连接是连向主副本还是辅助副本，这意味着需要在应用端配置多个连接串手动的配置代码是走写还是只读。这也就是为什么一开始我说这是半读写分离的原因。还有一个缺陷就是虽然配置了两个只读副本，但是每次只有优先级高的那个只读副本能提供只读连接，只有当优先级高的那个只读副本故障了才能路由到下一个只读副本。这也就意味着当前只有2个副本在提供读写操作，多个只读副本之间不能做到同时提供读操作的负载均衡。

 

注意： 域服务器宕机了也不影响使用SQLServer身份验证连接副本或者监听器，Windows身份验证会受域服务器的影响。所以只要不故障切换AD宕机了也不影响AlwaysOn群集的连接。这个功能减少了AlwaysON对AD的依赖，同时也减少建双域控的成本。

 

 

搭建和加入域参考：http://www.cnblogs.com/chenmh/p/4444168.html

搭建故障转移群集参考：http://www.cnblogs.com/chenmh/p/4479304.html

Alwayson搭建参考：http://www.cnblogs.com/chenmh/p/4484176.html

Alwayson配置两个节点加共享文件夹仲裁见证：http://www.cnblogs.com/chenmh/p/7156719.html

Alwayson概念总结参考：http://www.cnblogs.com/chenmh/p/6972007.html