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

一、概念简介

1.1、基本概念

1)读写分离概念:是把对数据库的读操作和写操作分离开。在一定程度上,读写分离可以缓解读写操作并发时产生锁的问题。

2)读写分离原理:是让主数据库处理事务性增、删、改操作(INSERT、DELETE、UPDATE),而从数据库处理查询操作(SELECT)。

1.2、技术简介

SQL Server 提供了三种技术来实现读写分离,分别是:日志传送、事务复制、Always On。以下是三种技术的比较:

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

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

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

二、实战准备

2.1、网络环境

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

2)以下是本文的测试环境:

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

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

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

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 服务:

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

2.3、同步账号

主从服务器都需要建立一个账号及密码都相同的本地管理员用户如 sync:

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

2.4、SQL Server 代理

主从服务器都需要启动 SQL Server 代理,另外登录账号都设为同步账号如 sync:

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

 

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 的”复制”右键->点击”配置分发”。
SQL Server 搭建主从同步实现读写分离

2)点击”下一步”。
SQL Server 搭建主从同步实现读写分离

3)默认选择,点击”下一步”。
SQL Server 搭建主从同步实现读写分离

4)快照文件夹应使用网络路径,因此要先设置文件夹共享。
SQL Server 搭建主从同步实现读写分离

5)打开”D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL”->对着”repldata”文件夹”右键”->点击”属性”。
SQL Server 搭建主从同步实现读写分离

6)选择”共享”页签->点击”共享”。
SQL Server 搭建主从同步实现读写分离

7)添加”Everyone”用户。
SQL Server 搭建主从同步实现读写分离

8)授予”Everyone”用户”读取/写入”权限->点击”共享”。
SQL Server 搭建主从同步实现读写分离

9)共享成功后,在快照文件夹中输入网络路径”\\IT01\ReplData”,点击”下一步”。
SQL Server 搭建主从同步实现读写分离

10)默认选择,点击”下一步”。
SQL Server 搭建主从同步实现读写分离

11)默认选择,点击”下一步”。
SQL Server 搭建主从同步实现读写分离

12)默认选择,点击”下一步”。
SQL Server 搭建主从同步实现读写分离

13)点击”完成”。
SQL Server 搭建主从同步实现读写分离

14)完成后,点击”关闭”即可。
SQL Server 搭建主从同步实现读写分离

3.2、发布配置

1)在 IT01 主服务器上,点击 SQL Server 的”复制”->对着”本地发布”右键->点击”新建发布”。

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

2)点击”下一步”。

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

3)选择要发布的数据库如”AdventureWorks”->点击”下一步”。

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

4)选择”事务发布”->点击”下一步”。

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

5)选择要发布的对象如”表”(也可以选择某个具体表)->点击”下一步”。

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

6)默认选择,点击”下一步”。

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

7)勾选”立即创建快照并使快照保持可用状态,以初始化订阅”->点击”下一步”。

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

8)点击”安全设置”。

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

9)由于本测试环境为非域环境,因此只能选择”在 SQL Server 代理服务账号下运行”。同时,录入 SQL Server 登录名及密码,点击”确定”。

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

10)点击”下一步”。

SQL Server 搭建主从同步实现读写分离11)默认选择,点击”下一步”。

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

12)起个发布名称,点击”完成”。SQL Server 搭建主从同步实现读写分离

13)执行成功后,点击”关闭”即可。

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

14)对着发布名称”右键”->点击”属性”。

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

15)点击”快照”->取消勾选”将文件放入默认文件夹”,勾选”将文件放入下列文件夹”,并录入网络地址”\\IT01\ReplData”->点击”确定”。

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

3.3、订阅配置

1)在 HW01 从服务器上打开运行->输入”\\IT01″。SQL Server 搭建主从同步实现读写分离

2)确保能正常访问主服务上的共享文件夹”repldata”。SQL Server 搭建主从同步实现读写分离

3)对着数据库”右键”->选择”新建数据库”。SQL Server 搭建主从同步实现读写分离

 

4)输入数据库名如”AdventureWorks”->点击”确定”。SQL Server 搭建主从同步实现读写分离

 

5)打开”复制”,对着本地订阅”右键”->点击”新建订阅”。SQL Server 搭建主从同步实现读写分离

 

6)默认选择,点击”下一步”。SQL Server 搭建主从同步实现读写分离

7)在下拉框中选择”查找 SQL Server 发布服务器…”。SQL Server 搭建主从同步实现读写分离

8)输入主服务器名称及身份验证,同时勾选”记住密码”,最后点击”连接”。SQL Server 搭建主从同步实现读写分离

9)默认选择,点击”下一步”。

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

10)选择”在其订阅服务器上运行每个代理(请求订阅)”->点击”下一步”。SQL Server 搭建主从同步实现读写分离

11)选择订阅数据库”AdventureWorks”->点击”下一步”。SQL Server 搭建主从同步实现读写分离

12)点击”…” 。SQL Server 搭建主从同步实现读写分离

13)选择”在 SQL Server 代理服务账户下运行”->输入连接到分发服务器的登录账号及密码->点击”确定”。SQL Server 搭建主从同步实现读写分离

14)点击”下一步”。SQL Server 搭建主从同步实现读写分离

15)选择”连续运行”->点击”下一步”。SQL Server 搭建主从同步实现读写分离

16)初始化时间选择”立即”->点击”下一步”。SQL Server 搭建主从同步实现读写分离

17)默认选择,点击”下一步”。SQL Server 搭建主从同步实现读写分离

18)点击完成。SQL Server 搭建主从同步实现读写分离

19)创建成功后,点击”关闭”。SQL Server 搭建主从同步实现读写分离

20)对着订阅名称”右键”->点击”属性”。SQL Server 搭建主从同步实现读写分离

22)在快照项中,快照位置选择”备份文件夹”->快照文件夹输入”\\IT01\repldata”->点击”确定”。SQL Server 搭建主从同步实现读写分离

四、异常检查

1)对着发布名称”右键”->点击”启动复制监视器”。SQL Server 搭建主从同步实现读写分离

2)记录行”右键”,可以”停止代理”再”启动代理”,这样就可以发现执行过程中的报错。也可以点击”查看详细信息”,查看执行的过程日志等。SQL Server 搭建主从同步实现读写分离

五、新增项目内容

1)假如有新的表或其它新的项目内容需要同步,可以对着发布名称”右键”->点击”属性”。SQL Server 搭建主从同步实现读写分离

2)选择”项目”->勾选新增的表等项目内容->点击”确定”。SQL Server 搭建主从同步实现读写分离

3)对着发布名称”右键”->点击”查看快照代理状态”。SQL Server 搭建主从同步实现读写分离

4)点击”启动”。SQL Server 搭建主从同步实现读写分离

六、删除发布服务器上的主从复制

1)先删除发布服务器上的订阅和发布。

2)执行以下命令,删除 distribution 分发数据库。

USE master
GO
EXEC sp_dropdistributiondb @database=N'distribution'
GO
EXEC sp_dropdistributor @no_checks=1,@ignore_distributor=1
GO
© 版权声明

☆ END ☆
喜欢就点个赞吧
点赞0 分享
图片正在生成中,请稍后...