SQLServer 搭建主从同步实现读写分离
⼀、概念简介
1.1、基本概念
1）读写分离概念：是把对数据库的读操作和写操作分离开。在⼀定程度上，读写分离可以缓解读写操作并发时产⽣锁的问题。2）读写分离原理：是让主数据库处理事务性增、删、改操作（INSERT、DELETE、UPDATE），⽽从数据库处理查询操作（SELECT ）。
1.2、技术简介
SQL Server提供了三种技术来实现读写分离，分别是：⽇志传送、事务复制、Always On。以下是三种技术的⽐较：⽇志传送事务复制Always On
原理
通过SQL Server Agent调度作业进⾏⽇志
的备份、复制、还原实现同步由复制代理同步发布数据上的
增删改操作到订阅服务器
主数据库的事务⽇志记录发送并
运⽤到每个辅助数据库
版本功能⽀持
SQL Server 2000 企业版
SQL Server 2005 及以后标准版、企业版
标准版、企业版SQL Server 2012 企业版
操作系统要求⽆限制⽆限制
sqlserver备份表语句
Windows 企业版
故障转移集
限制要求数据库必须是完整恢复模式要求表必须有主键要求数据库必须是完整恢复模式
同步粒度数据库级表级数据库级
数据差异取决于备份、复制、还原的作业设置⼏秒⼏秒
副本数量⽆限制⽆限制4个
副本读取间歇性，在还原时会中断查询。正常正常
⾃动故障转移不⽀持不⽀持⽀持
事务复制没有Always On的要求那么⾼，只需要主从服务器能通过TCP进⾏通讯即可，主从服务器操作系统和SQL Server版本可以不完全⼀致（⽣产环境建议⼀致），同时，主从服务器也不需要加⼊域。
注：本⽂主从同步实现⽅式采⽤事务复制⽅式。
⼆、实战准备
2.1、⽹络环境
1）主从服务器最好在同⼀个局域⽹内，⽽且要互相ping得通，可以是不同⽹段。
2）以下是本⽂的测试环境：
计算机名IP地址操作系统数据库
主服务器IT01192.168.2.174Windows 10SQL Server 2016
从服务器HW01192.168.2.242Windows Server 2012SQL Server 2016
2.2、数据库主机名
1）SQL Server数据库实例主机名需与本地服务器名称⼀致：
--本地服务器名称
SELECT@@SERVERNAME
--数据库实例主机名
SELECT SERVERPROPERTY('ServerName')
2）若出现SQL Server数据库实例主机名与本地服务器名称不⼀致的情况，可通过以下语句来更改：
IF (SERVERPROPERTY('SERVERNAME')<>@@SERVERNAME)
BEGIN
DECLARE@SERVER SYSNAME
SET@SERVER=@@SERVERNAME
EXEC SP_DROPSERVER @SERVER=@SERVER
SET@SERVER=CAST(SERVERPROPERTY('SERVERNAME') AS SYSNAME)
EXEC SP_ADDSERVER @SERVER=@SERVER,@LOCAL='LOCAL'
END
更改完成后请重启SQL Server服务：
2.3、同步账号
主从服务器都需要建⽴⼀个账号及密码都相同的本地管理员⽤户如sync：
2.4、SQL Server 代理
主从服务器都需要启动SQL Server代理，另外登录账号都设为同步账号如sync：
2.5、同步说明
1）以数据库AdventureWorks为例。
2）在AdventureWorks上执⾏以下SQL语句，否则后续会出现【进程⽆法在“IT01”上执⾏“sp_replcmds”】报错。
sys.sp_changedbowner 'sa'
3）主从搭建，实际是发布->分发->订阅的过程。本⽂发布与分发使⽤的是同⼀台服务器IT01。
2.6、同步规则
1）新增的表⼀定要有主键，否则不能进⾏同步。
2）从库上⼀定不能有任何的数据修改，这个原则⼀定要遵守。
三、实战操作
3.1、分发配置
1）在IT01主服务器上，对着SQL Server的"复制"右键->点击"配置分发"。
2）点击"下⼀步"。
3）默认选择，点击"下⼀步"。
4）快照⽂件夹应使⽤⽹络路径，因此要先设置⽂件夹共享。
5）打开"D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL"->对着"repldata"⽂件夹"右键"->点击"属性"。6）选择"共享"页签->点击"共享"。
7）添加"Everyone"⽤户。
8）授予"Everyone"⽤户"读取/写⼊"权限->点击"共享"。
9）共享成功后，在快照⽂件夹中输⼊⽹络路径"\\IT01\ReplData"，点击"下⼀步"。
10）默认选择，点击"下⼀步"。
11）默认选择，点击"下⼀步"。
12）默认选择，点击"下⼀步"。
13）点击"完成"。
14）完成后，点击"关闭"即可。
3.2、发布配置
1）在IT01主服务器上，点击SQL Server的"复制"->对着"本地发布"右键->点击"新建发布"。
2）点击"下⼀步"。
3）选择要发布的数据库如"AdventureWorks"->点击"下⼀步"。
4）选择"事务发布"->点击"下⼀步"。
5）选择要发布的对象如"表"（也可以选择某个具体表）->点击"下⼀步"。
6）默认选择，点击"下⼀步"。
7）勾选"⽴即创建快照并使快照保持可⽤状态，以初始化订阅"->点击"下⼀步"。
8）点击"安全设置"。
9）由于本测试环境为⾮域环境，因此只能选择"在 SQL Server 代理服务账号下运⾏"。同时，录⼊SQL Server登录名及密码，点击"确定"。10）点击"下⼀步"。
11）默认选择，点击"下⼀步"。
12）起个发布名称，点击"完成"。
13）执⾏成功后，点击"关闭"即可。
14）对着发布名称"右键"->点击"属性"。
15）点击"快照"->取消勾选"将⽂件放⼊默认⽂件夹"，勾选"将⽂件放⼊下列⽂件夹"，并录⼊⽹络地址"\\IT01\ReplData"->点击"确定"。
3.3、订阅配置
1）在HW01从服务器上打开运⾏->输⼊"\\IT01"。
2）确保能正常访问主服务上的共享⽂件夹"repldata"。
3）对着数据库"右键"->选择"新建数据库"。
4）输⼊数据库名如"AdventureWorks"->点击"确定"。
5）打开"复制"，对着本地订阅"右键"->点击"新建订阅"。
6）默认选择，点击"下⼀步"。
7）在下拉框中选择"查 SQL Server 发布服务器..."。
8）输⼊主服务器名称及⾝份验证，同时勾选"记住密码"，最后点击"连接"。
9）默认选择，点击"下⼀步"。
10）选择"在其订阅服务器上运⾏每个代理(请求订阅)"->点击"下⼀步"。
11）选择订阅数据库"AdventureWorks"->点击"下⼀步"。
12）点击"..." 。
13）选择"在 SQL Server 代理服务账户下运⾏"->输⼊连接到分发服务器的登录账号及密码->点击"确定"。
14）点击"下⼀步"。
15）选择"连续运⾏"->点击"下⼀步"。
16）初始化时间选择"⽴即"->点击"下⼀步"。
17)默认选择，点击"下⼀步"。
18)点击完成。
19）创建成功后，点击"关闭"。
20）对着订阅名称"右键"->点击"属性"。
22）在快照项中，快照位置选择"备份⽂件夹"->快照⽂件夹输⼊"\\IT01\repldata"->点击"确定"。
四、异常检查
1）对着发布名称"右键"->点击"启动复制监视器"。
2）记录⾏"右键"，可以"停⽌代理"再"启动代理"，这样就可以发现执⾏过程中的报错。也可以点击"查看详细信息"，查看执⾏的过程⽇志等。
五、新增项⽬内容
1）假如有新的表或其它新的项⽬内容需要同步，可以对着发布名称"右键"->点击"属性"。
2）选择"项⽬"->勾选新增的表等项⽬内容->点击"确定"。
3）对着发布名称"右键"->点击"查看快照代理状态"。
4）点击"启动"。
六、删除发布服务器上的主从复制
1）先删除发布服务器上的订阅和发布。
2）执⾏以下命令，删除distribution分发数据库。
USE master
GO
EXEC sp_dropdistributiondb @database=N'distribution'
GO
EXEC sp_dropdistributor @no_checks=1,@ignore_distributor=1
GO

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。