1. 代码100分首页
  2. 代码基础

SQL Server搭建主从同步实现读写分离[通俗易懂]

小小码 • • 代码基础 • 阅读 130
一键激活最新全家桶

SQL Server搭建主从同步实现读写分离[通俗易懂]一、概念简介 1.1、基本概念 1）读写分离概念：是把对数据库的读操作和写操作分离开。在一定程度上，读写分离可以缓解读写操作并发时产生锁的问题。 2）读写分离原理：是让主数据库处理事务性增、删、改操作

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

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 FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQL”->对着”repldata”文件夹”右键”->点击”属性”。

SQL Server搭建主从同步实现读写分离[通俗易懂]

6）选择”共享”页签->点击”共享”。

SQL Server搭建主从同步实现读写分离[通俗易懂]

7）添加”Everyone”用户。

SQL Server搭建主从同步实现读写分离[通俗易懂]

8）授予”Everyone”用户”读取/写入”权限->点击”共享”。

SQL Server搭建主从同步实现读写分离[通俗易懂]

9）共享成功后，在快照文件夹中输入网络路径”\IT01ReplData”，点击”下一步”。

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）点击”快照”->取消勾选”将文件放入默认文件夹”，勾选”将文件放入下列文件夹”，并录入网络地址”\IT01ReplData”->点击”确定”。

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 epldata”->点击”确定”。

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

 

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5775.html

赞 (0)
小小码小小码
0 0
上一篇 2023-04-25 13:30
下一篇 2023-04-25

相关推荐

  • mysql远程登录设置_思科ssh配置 代码基础

    mysql远程登录设置_思科ssh配置

    mysql远程登录设置_思科ssh配置配置远程登录MySQL 一个小白，记录每次遇到的坑。 环境是Docker+MySQL 步骤 在服务器端开启远程访问 首先进入mysql数据库，然后输入下面两个命令: grant all privile

    小小码 小小码
    2023-03-05
    146
  • Python编程从入门到精通 代码基础

    Python编程从入门到精通

    Python编程从入门到精通Python编程从入门到精通是针对Python编程入门者的一个教程。该教程将帮助大家全面学习Python编程基础，并且掌握各种高级技术，包括函数式编程，面向对象编程等。通过本教程，你将学会使用Python开发实用工具，并能够掌握以Python为基础的一些常见编程框架、库和工具。同时，本教程还提供了许多实用的示例和实践项目，以便开发者可以快速掌握Python编程。

    admin admin
    2024-05-26
    66
  • python中的线程和进程（java线程和python线程） 代码基础

    python中的线程和进程（java线程和python线程）

    python中的线程和进程（java线程和python线程）什么是线程、进程?

    admin admin
    2024-06-16
    52
  • MySQL数据库：基本操作及增删改查语句「建议收藏」 代码基础

    MySQL数据库：基本操作及增删改查语句「建议收藏」

    MySQL数据库：基本操作及增删改查语句「建议收藏」基本语法&&操作语句 create(创建) alter(更新) drop(删除) 一次性删除一个表中所有的数据 包括日志 truncate table 表名; 选中或者使用该数据库 说明接下来的操作都

    小小码 小小码
    2022-12-21
    131
  • 如何处理慢sql_方法论原则通常包括 代码基础

    如何处理慢sql_方法论原则通常包括

    如何处理慢sql_方法论原则通常包括本文介绍了笔者实际工作中慢SQL治理的方法论，1、发现：如何发现慢SQL。2、定位：如何定位到慢SQL写在哪。3、分析：遇到慢SQL时的分析思路。4、解决：慢SQL的解决思路。

    小小码 小小码
    2023-05-10
    142
  • python读取cifar（python读取cif） 代码基础

    python读取cifar（python读取cif）

    python读取cifar（python读取cif）直接用open打开后read读取 A =open(‘test.bmp’,’rb’)B=open(‘test2.bmp’,’w’)c = A.read()B.write(c)A.close()B.close()

    admin admin
    2023-11-02
    137
  • 如何升级Python 代码基础

    如何升级Python

    如何升级PythonPython是一门非常流行的编程语言。每当新版本的Python发布时，都会有一些新特性和修复了一些问题，因此及时升级Python版本是非常重要的。下面是关于如何升级Python版本的方法：

    admin admin
    2024-08-30
    21
  • 安装whl的完整步骤 代码基础

    安装whl的完整步骤

    安装whl的完整步骤Python是一门非常流行的编程语言，得益于其广泛的使用和社区支持，Python能够快速获得各类开发库和工具的支持。其中，应用最为广泛的是Python的第三方库，比如NumPy、Pandas、TensorFlow等。Python的第三方库提供了非常多的功能，比如图像处理、机器学习等领域，而且大多数都是开源的，充满活力。而这些第三方库的安装需要使用whl文件。

    admin admin
    2024-06-01
    57

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注