SQL Server发布订阅配置及实例讲解

互联网 2022/8/7 19:01:12

环境:vmware workstation prop 15.1 window server 2019 datacenter sqlserver2014 x64前言:什么是发布和订阅? 发布和订阅属于sqlserver的复制技术,引用官网的解释:复制是一组技术,它将数据和数据库对象从一个数据库复制和分发到另一个数据库,然后在数据库之间进行…

环境:

  • vmware workstation prop 15.1
  • window server 2019 datacenter
  • sqlserver2014 x64

前言:

  • 什么是发布和订阅?
    发布和订阅属于sqlserver的复制技术,引用官网的解释:

    复制是一组技术,它将数据和数据库对象从一个数据库复制和分发到另一个数据库,然后在数据库之间进行同步以保持一致性。
    官网说明:https://docs.microsoft.com/zh-cn/sql/relational-databases/replication/sql-server-replication?view=sql-server-ver15

  • 发布订阅可以做什么?
    正如上面所说的,发布订阅可以让你在不同的数据库中交换数据。那么具体的应用场景呢?下面我总结了几个:

    • 数据库读写分离:
      通过对一个主库的进行“事务复制”,我们可以将每次主库的更新都同步到只读库上,从而达到读写分离的效果,进而提高系统性能。
    • 数据库高可用方案:能将主库的更改实时同步到其他库中
    • 与其他系统进行数据对接:
      在与第三方公司做对接时,可以直接将数据库中的指定表/指定列,甚至指定行的数据同步更新到对方的数据库里面,这样就减少了开发量。
    • 总库和分库之间交换数据:
      本人遇到过一个需求:在一个erp系统中,总部和加盟商各自有一个数据库,总部给加盟商派发货物后,程序会自动将总部的相关商品档案数据提取到加盟商的库中,对于那些未派发的货物不要提取,并且对于已经提取的货物还要跟随总部的更新(比如说:总部变更商品的颜色名称,加盟商也要跟着变),另外总部删除的数据,加盟商不要同步删除。,这个时候我们也可以通过发布订阅实现,只不过设置复杂一点(除了设置仅复制update语句之外,还要配置分发服务器处理数据不一致的情况)。
  • 怎么做发布订阅?
    在sqlserver管理端,找到复制下面的发布和订阅,跟着向导新建即可。下面我将用几个实例演示发布订阅的功能!

实验服务器配置:

  • 服务器1:
    虚拟机名称:Windows Server 2019_1
    计算机名:WIN-SERVER1
    ip:192.168.3.39
    操作系统:window server 2019 datacenter
    数据库:sqlsever2014 x64
    数据库sa密码:123456
  • 服务器2:
    虚拟机名称:Windows Server 2019_2
    计算机名:WIN-SERVER2
    ip:192.168.3.40
    操作系统:window server 2019 datacenter
    数据库:sqlsever2014 x64
    数据库sa密码:123456
  • 服务器3:
    虚拟机名称:Windows Server 2019_3
    计算机名:WIN-SERVER3
    ip:192.168.3.41
    操作系统:window server 2019 datacenter
    数据库:sqlsever2014 x64
    数据库sa密码:123456

实验一、使用发布订阅实现读写分离

需求:
在这个读写分离下,我们要求有一个主数据库,两个只读数据库。对于主数据库的更新,只读数据库要在尽可能少延迟的情况下同步更改(基于事务发布的延迟也就1-5秒)。

步骤1.1 准备数据

服务器1上面准备好数据库test以及一张表person,一个视图v_person: 在这里插入图片描述
服务器2和服务器3上新建一个test数据库就可以了。

步骤1.2 准备主数据库服务器环境

首先要确保sqlsever代理是启动的(默认安装后是禁用的):
在这里插入图片描述

步骤1.3 在主数据库上新建发布:

在sqlserver中的本地发布右键–>新建发布即可弹出新建发布向导:
在这里插入图片描述

注意:
这里有个小插曲,因为我是先安装了sqlserver后,又改了计算机的名,所以新建发布的时候弹出一个错误:
在这里插入图片描述
遇到,这个错误不要慌,只需要将当前运行的sqlserver实例名改成当前的即可。
参照:[解决方案]sql server复制需要有实际的服务器名称才能连接到服务器

点击下一步:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
点击完成后就开始新建,可能是我服务器环境的问题,下面弹出了一个错误提示,但这个不影响,我们直接关闭掉就行。
在这里插入图片描述

注意:从提示上看,是sqlserver代理服务没有设置为自动启动,那么我们手动将它设置自动启动即可!在这里插入图片描述

到这里,一个发布就创建好了,我们可以监视以下:
在这里插入图片描述

步骤 1.4 新建订阅

直接在主服务器上的发布上右键->新建订阅:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
点击“添加Sql server订阅服务器”,将我们的服务器2和服务器3分别添加进来:
在这里插入图片描述

注意:这里还是有个小插曲,原因也是安装sqlserver后又修改了计算机名,那么处理方法也是和上面一样的,这里不多说。

添加好订阅服务器后,如下图所示:
在这里插入图片描述
点击下一步后,我们设置分发代理安全性:
在这里插入图片描述
设置完成后,如下所示:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

步骤 1.5 测试同步效果

上面几步完成后,我们可以去服务器2和服务器3观察同步的结果了。
先来看下服务器2的:
在这里插入图片描述
服务器3的数据是一样的,这里先不看。
此时我们对服务器1的数据进行增删改,如下:
在这里插入图片描述
此时,我们观察服务器3的数据:
在这里插入图片描述

实验二、复杂需求的发布和订阅

在最开始讲应用场景的时候,我提到了一个复杂的需求,那么这里就来动手实现它。首先,明确下需求:

  1. 一个总部的数据库和一个加盟商数据库。
  2. 总部里有一些数据(表person)需要同步到加盟商里。但是,在数据同步的时候,需要保证以下几个条件:
    a) 表person中的某一列不需要同步
    b) 对于已经同步到加盟商的数据,如果总部删除了,那么加盟商是不能跟着删除的
    c) 对于总部新增的数据,不需要同步到加盟商那里,当需要同步时由程序负责拷贝数据
    d) 对于已经同步的数据,如果总部改变了某列的值,那么加盟商的数据也需要跟着改变

我们明确上面的需求后,就开始做实验了,在做实验之前,让我们把总部和加盟商的虚拟机都恢复到sqlserver刚安装好的状态:

步骤2.1 准备环境和数据

总部环境:
虚拟机名:Windows Server 2019_1
操作系统:Window server 2019 DataCenter
计算机名:WIN-SERVER1
IP:192.168.3.39
数据库:Sqlserver 2014 x64 / sa:123456

加盟商环境:
虚拟机名:Windows Server 2019_2
操作系统:Window server 2019 DataCenter
计算机名:WIN-SERVER2
IP:192.168.3.40
数据库:Sqlserver 2014 x64 / sa:123456

数据:
总部新建test数据库,并准备数据:
在这里插入图片描述
加盟商新建数据库test,并创建好person表结构:
在这里插入图片描述

步骤 2.2 在总部服务器上配置发布

先启动总部服务器上的sqlserver代理,然后将“C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData“文件夹的权限设置成everyone最大。
然后,按照下面的图,一步一步向下配置:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
点击下一步后,到了下面图示,这里是重点:
在这里插入图片描述
下一步:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
到这里,总部的发布就创建好了。

步骤2.3 修改分发服务器的配置,使之兼容数据不一致的情况

为什么会有这一步骤呢?因为加盟商只需要同步总部的update语句就行了,但是总部的update语句同步过来后,加盟商这边不一定有这行数据,如果我们没有在这里做配置的话,服务器就会认为发布订阅除了问题,进而把整个订阅停掉,所以这里一定要配置数据兼容不一致的情况。配置方式如下:
在这里插入图片描述
在这里插入图片描述

步骤2.4 新建订阅

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
上面填好后,如下图:
在这里插入图片描述
在这里插入图片描述
上面填好后,如下图:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
到了这里,订阅也创建好了。

步骤2.5 测试同步效果

首先,我们来检查上面配置完成后,加盟商这边的情况:
在这里插入图片描述
下面我们将加盟商的addr列补全并清空所有数据:
在这里插入图片描述

现在我们来正式测试同步的情况~

步骤 2.5.1 测试总部的insert是否同步到加盟商

首先,在总部中插入一条数据:
在这里插入图片描述
此时观察,加盟商:
在这里插入图片描述
上图说明:总部的insert语句并没有同步到加盟商那边。

步骤 2.5.2 测试总部的delete是否同步到加盟商

首先,我们在加盟商那边插入一条数据(注意,插入的数据id和总部的已有数据重复):
在这里插入图片描述
然后,总部执行删除的命令:
在这里插入图片描述
此时,我们来观察以下加盟商的数据:
在这里插入图片描述
可以看到,加盟商这边,数据并没有被删除,所以delete语句并没有同步到加盟商

步骤 2.5.3 测试总部的update是否同步到加盟商

首先,在加盟商那边插入数据:
在这里插入图片描述
我们第一次总部执行一个update语句(注意,这个被更新的数据,并不存在于加盟商中):
在这里插入图片描述
此时,我们再去查看加盟商的数据库,很显然数据是没有任何变化的,图我也不贴了。
那么,这里为什么要做这一步操作呢?还记的上面专门配置”数据不一致时继续执行么“,如果你没有做这个配置的话,此时订阅已经时停掉了!!!
下面,我们做一个有效果的更新:
更新总部的数据:
在这里插入图片描述

此时再来看看加盟商的数据情况:
在这里插入图片描述

到此,实验就完成了,可以看到,发布和订阅还是相当灵活的。

转载自:https://blog.csdn.net/u010476739/article/details/107443420

随时随地学软件编程-关注百度小程序和微信小程序
关于找一找教程网

本站文章仅代表作者观点,不代表本站立场,所有文章非营利性免费分享。
本站提供了软件编程、网站开发技术、服务器运维、人工智能等等IT技术文章,希望广大程序员努力学习,让我们用科技改变世界。
[SQL Server发布订阅配置及实例讲解]http://www.zyiz.net/tech/detail-340374.html

赞(0)
关注微信小程序
程序员编程王-随时随地学编程

扫描二维码或查找【程序员编程王】

可以随时随地学编程啦!

技术文章导航 更多>