SQL Server搭建主从同步实现读写分离

 

一、概念简介

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 企业版
操作系统要求	无限制	无限制	Windows 企业版 故障转移群集
限制	要求数据库必须是完整恢复模式	要求表必须有主键	要求数据库必须是完整恢复模式
同步粒度	数据库级	表级	数据库级
数据差异	取决于备份、复制、还原的作业设置	几秒	几秒
副本数量	无限制	无限制	4个
副本读取	间歇性，在还原时会中断查询。	正常	正常
自动故障转移	不支持	不支持	支持

事务复制没有Always On的要求那么高，只需要主从服务器能通过TCP进行通讯即可，主从服务器操作系统和SQL Server版本可以不完全一致（生产环境建议一致），同时，主从服务器也不需要加入域。

注：本文主从同步实现方式采用事务复制方式。

二、实战准备

2.1、网络环境

1）主从服务器最好在同一个局域网内，而且要互相ping得通，可以是不同网段。

2）以下是本文的测试环境：

	计算机名	IP地址	操作系统	数据库
主服务器	IT01	192.168.2.174	Windows 10	SQL Server 2016
从服务器	HW01	192.168.2.242	Windows Server 2012	SQL 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

 

七、操作积累

1、在订阅服务器的当前数据库中删除订阅。 （快照复制和事务复制 的删除）

exec sp_droppullsubscription 'all', 'all', 'all'

2、发布服务器的发布数据库上执行此存储的过程(删除对合并发布的订阅及其关联的合并代理)

EXEC sp_dropmergesubscription 'all', 'all', 'all'

3、删除发布服务器/订阅服务器数据库上的所有复制对象 删除本地发布或者本地订阅。

EXEC SP_REMOVEDBREPLICATION '发布后的数据库名称';

4、发布快照后添加新表如何不重新快照所有表(先设置allow_anonymous与immediate_sync为false,添加新表，生成快照，然后再开起来)

-- 查看该发布的相关信息(allow_anonymous表示是否允许对发布使用匿名订阅；immediate_sync表示是否在每次快照代理运行时创建或重新创建同步文件；)
sp_helppublication

-- 变更allow_anonymous/immediate_sync
EXEC sp_changepublication
@publication = 'PLL_ERP_Co_01_sync',
@property = 'allow_anonymous' ,
@value = 'false';
EXEC sp_changepublication
@publication = 'PLL_ERP_Co_01_sync',
@property = 'immediate_sync' ,
@value = 'false';

 

转载：https://www.cnblogs.com/atomy/p/15356584.html