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

本文源自：https://blog.csdn.net/u010476739/article/details/107443420

环境：

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的数据：

实验二、复杂需求的发布和订阅
在最开始讲应用场景的时候，我提到了一个复杂的需求，那么这里就来动手实现它。首先，明确下需求： 

一个总部的数据库和一个加盟商数据库。
总部里有一些数据（表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语句（注意，这个被更新的数据，并不存在于加盟商中）：

 

此时，我们再去查看加盟商的数据库，很显然数据是没有任何变化的，图我也不贴了。
那么，这里为什么要做这一步操作呢？还记的上面专门配置”数据不一致时继续执行么“，如果你没有做这个配置的话，此时订阅已经时停掉了！！！
下面，我们做一个有效果的更新：
更新总部的数据：

此时再来看看加盟商的数据情况：

 

 

到此，实验就完成了，可以看到，发布和订阅还是相当灵活的。
————————————————
版权声明：本文为CSDN博主「jackletter」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u010476739/article/details/107443420