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

环境：

• 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