共计 12978 个字符，预计需要花费 33 分钟才能阅读完成。
行业资讯
数据库
关系型数据库
sqlserver 关于发布订阅 replication_subscription 的知识点有哪些
本篇内容主要讲解“sqlserver 关于发布订阅 replication_subscription 的知识点有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“sqlserver 关于发布订阅 replication_subscription 的知识点有哪些”吧!
事务复制的工作机制
事务复制是由 SQL Server 快照代理、日志读取器代理和分发代理实现的。
快照代理准备快照文件（其中包含了已发布表和数据库对象的架构和数据），然后将这些文件存储在快照文件夹中，并在分发服务器中的分发数据库中记录同步作业。
日志读取器代理监视为事务复制配置的每个数据库的事务日志，并将标记为要复制的事务从事务日志复制到分发数据库中，分发数据库的作用相当于一个可靠的存储 - 转发队列。
分发代理将快照文件夹中的初始快照文件和分发数据库表中的事务复制到订阅服务器中。
在发布服务器中所做的增量更改根据分发代理的计划流向订阅服务器，分发代理可以连续运行以尽量减少滞后时间，也可以按预定的时间间隔运行。
因为日志读取器代理 (对应如下 3 的 TESTDB1-replicate2-2) 会把发布数据库的事务日志复制一份到分发数据库中，所以发布数据库并不需要在完整恢复模式下，如果日志读取器没能完成复制就发生发布数据库的事务日志又将要截断时，则发布数据库的事务日志状态 sys.databases.log_reuse_wait_desc 会显示为 Replication 以阻止发布数据库的事务日志发生截断
1、如果操作发布订阅的客户端 SSMS 版本比服务器端版本低，会报错，比如 service 是 sqlserver2016，ssms 使用 sqlserver2014 会报错
2、只建立分发时，会新增 7 个相关 job; 初次建立发布的同时建立分发，会新增 9 个相关 job
3、后面每新增一个发布名，发布服务器上会新增两个发布的 job 如下，前一个是不停的生成发布数据，该 job 不停运行，后一个是初始化发布数据(生成 unc 目录下的文件和文件)，运行一次就可以了
TESTDB1-replicate2-2
TESTDB1-replicate2-pub_replicate2-2
发布实例名 – 数据库名 – 发布名的序号
发布实例名 – 数据库名 – 发布名 – 发布名的序号
4、发布服务器 - 复制 - 本地发布 - 发布名 - 右键 - 属性 -snapshot，选择 put files in the following folder，可以把文件放到共享路径
5、订阅，可以在订阅服务器建立，也可以在发布服务器建立，发布服务器 - 复制 - 本地发布 - 发布名，右键选择 new subscriptions
6、后面每新增一个订阅，如果是推送订阅，主库增加一个 job，如果是请求订阅，从库增加一个 job
TESTDB1-replicate2-replicate2-TESTDB2-6(推送订阅，发布实例名 - 发布数据库名 - 发布名 - 订阅实例名 - 编号)
TESTDB1-replicate1-pub_replicate1-TESTDB2-replicate_01-CD7A365E-2DE7-47A3-B31E-70F785FA71F2(请求订阅)
7、发布 job 或订阅 job，都可以根据需要修改 scheduler
8、本地发布或本地订阅下面的订阅图标有小圈圈，表示在当前实例下，是对方主动而不是当前实例主动，订阅的 job 在对方那边
推送订阅，在主库发布下面的订阅图标没有蓝色小圈圈，在从库订阅下面的订阅图标有蓝色小圈圈
请求订阅，在主库发布下面的订阅图标有蓝色小圈圈，在从库订阅下面的订阅图标没有蓝色小圈圈
– 也适用于本地发布又是本地订阅的情况，比如本地一个库信息传输到本地另一个库，则本地发布或本地订阅下面都有订阅，如果是推送订阅，则发布下面的订阅图标没有蓝色小圈圈，订阅下面的订阅图标有蓝色小圈圈，如果是请求订阅，则发布下面的订阅图标有蓝色小圈圈，订阅下面的订阅图标没有蓝色小圈圈
9、订阅的删除，根据推送订阅和请求订阅的不同，有不同操作方式，统一的操作方法就是直接删除主库发布下面的订阅。
推送订阅的情况下，如果只在从库删除订阅，则主库的发布下面的订阅信息还在，主库上的订阅 job 也还在(但是信息不会同步到订阅库了)，还需要在主库再删除一遍
推送订阅的情况下，在主库删除订阅的话，主库上的订阅 job 也不在了，从库的订阅信息也自动删除了
请求订阅的情况下，在主库删除订阅的话，从库上的订阅 job 也不在了，从库的订阅信息也自动删除了
请求订阅的情况下，在从库删除订阅的话，从库上的订阅 job 不在了，主库上的发布下面的订阅信息也不在了
– 也适用于本地发布又是本地订阅的情况，比如本地一个库信息传输到本地另一个库，则本地发布或本地订阅下面都有订阅
10、发布的 job 在 msdb.dbo.sysjobs 正常记录，但是这些 job 运行时在 sysprocesses.program_name 都是显示 Microsoft SQL Server，不显示为具体的 job 名称，如下语句查询 job，不适用于订阅复制
select * from msdb.dbo.sysjobs where name= jobname
select a.program_name,a.* from master..sysprocesses a where a.program_name like %0D1CE57E8AC5%
11、订阅的 job 在 msdb.dbo.sysjobs 正常记录，但是这些 job 运行时在 sysprocesses.program_name 都是显示为空
12、删除订阅数据库时，出现如下，解决方法是把订阅的 job 停掉再把订阅数据库 offline，再删除
Cannot drop the database‘XXX’because it is being used for replication
13、删除发布，如果发布下面有订阅，则删除发布的时候，订阅就失效了
请求订阅的情况下，从库的本地订阅下面还有订阅和 job，但是失效了，还需要手工删除一下订阅，此时会自动删除 job
推送订阅的情况下，主库的发布和 job 都删除了，从库的本地订阅下面还有订阅，但是失效了，还需要手工删除一下
– 也适用于本地发布又是本地订阅的情况，比如本地一个库信息传输到本地另一个库，则本地发布或本地订阅下面都有订阅
14、订阅库，可以执行 DML，执行 delete 不会有什么后遗症，执行 update 或 insert 的话，如果后面收到发布库传过来的数据，可能会产生冲突
15、发布订阅相关的存储过程
EXEC distribution.dbo.sp_replmonitorhelppublisher – 发布库上执行，检查发布服务器上的本地发布的情况
16、发布建立好后，发布服务器上有一个 linked sever 指向分发服务器，名称一般为 repl_distributor，disable publishing and distribution 后，该 linked server 会自动删除
订阅建立后(不管是请求订阅还是推送订阅)，会在发布服务器上自动建立一个 linked server 指向订阅服务器。删除发布或 disable publishing and distribution，该 linked server 都不会自动删除
17、如果订阅的 job 的 schedules 没有明确指定，只是 start automaticaly when SQL Server Agent starts，那么一旦把这个 job 停止，后面不会再同步了，在 Replication Monitor 里面的 Subscription Watch List 看到这个订阅的 status 就是 not running
18、推送订阅：到发布服务器下面的本地发布 - 发布名称 - 订阅名，右键选择 view Synchronization Status 可以看到订阅状态
请求订阅：到订阅服务器下面的本地订阅 - 订阅名，右键订阅，选择 view Synchronization Status 可以看到订阅状态，并可以看到订阅 job 的运行情况
19、复制 - 本地订阅 - 订阅名，右键选择 view Synchronization Status 查看到状态是 No replicated transactions are available，则到发布端，复制 - 本地发布 - 发布名，右键发布，选择如下两者，查看 job 状态是否 start 运行中，如果是，再 count(*)主从表数据是否一致，如果一致，说明此时确实没有 DML 事务产生新的数据
View Snapshot Agent Status 查看快照代理状态，对应的 job 其实是 实例名 - 发布数据库名 - 发布名称 - 发布序号，一般只运行一次，生成存放在 unc 中的初始化数据
View Log Reader Agent Status 查看日志读取器代理状态，对应的 job 其实是 实例名 - 发布数据库名 - 发布序号，一般一直运行
20、所谓的分发服务，就是创建一个分发数据库默认是 distribution，并创建 snapshot 目录，如果没有创建分发，初次建立发布的时候，会自动建立分发服务，第二次建立发布的时候，会使用原来的分发服务。创建发布时，必须要先有分发，要不发布的数据存放在哪呢? 分发就是存放这些要发布的数据的地方
21、分发服务器是发布服务器与订阅服务器之间的桥梁，起着存储区的作用，负责复制与一个或多个发布服务器相关联的特定数据。每个发布服务器都与分发服务器上的单个数据库 (称作分发数据库) 相关联。分发数据库从发布服务器获得要发布的数据后将存储复制状态数据和有关发布的元数据，并且在某些情况下为从发布服务器向订阅服务器移动的数据起着排队的作用。在大多数情况下，一个数据库服务器实例充当发布服务器和分发服务器两个角色。当发布服务器和分发服务器在同一个数据库实例中时，称为“本地分发服务器”。当发布服务器和分发服务器按各自的数据库服务器实例配置时，把分发服务器称为“远程分发服务器”
22、没有建立过分发时，右键 replication 时，有 configure distribution，选择 configure distribution 表示只配置分发，不做其他动作，在此过程中如果在 Publishers 页面勾选了 publisher 和 distribution database，则右键 replication 时，没有了 configure distribution，取而代之的是 publisher properties、distributor properties、disable publishing and distribution; 如果在 Publishers 页面没有勾选 publisher 和 distribution database，则右键 replication 时，没有了 configure distribution，取而代之的是 Distributor Properties、Disabled Publishing and Distribution Wizard。其实右键 replication 选择 configure distribution 时勾选了 publisher 和 distribution database，就是选择哪些发布服务器可以使用这个分发服务器(enable servers to use this distributor when they become publishers)，其中 distribution database 没得选，就是分发的数据库，默认是 distribution
23、右键 Replicattion 选择 Disabled Publishing and Distribution Wizard，禁用订阅发布，所有的订阅发布信息都丢失了包括 system databases 里的 distribution 数据库也包括 unc 目录下的所有目录和文件，所以执行之前需要对订阅发布信息进行备份或截图
24、要初始化订阅，即重新把发布端的数据推送到订阅端，可以选择推送到这个发布的单个订阅（发布端 – 发布名称 – 订阅名称 – 右键 –reinitialize），也可以选择推送到这个发布的所有订阅（发布端 – 发布名称 – 右键 –reinitialize all subscriptions）。再在发布服务器 - 复制 - 本地发布 - 发布名，右键选择 View Snapshot Agent Status 选择 start，或运行 job 实例名 - 发布数据库名 - 发布名称 - 发布序号
25、在订阅端的表里手工先 insert 一条语句，后面发布端同步一条一样数据过来的时候，订阅端会报错
Violation of PRIMARY KEY constraint PK_XX . Cannot insert duplicate key in object dbo.TXX . The duplicate key value is (33583).
26、bug 问题：右键 Replicattion 选择 Disabled Publishing and Distribution Wizard 后，最后一步会报错，但是确实把发布和分发都删除了
27、导出订阅复制的脚本的方法：右键 Replication 选择 Generate Scripts(导出脚本里面有注释，在发布端执行还是订阅端执行)
以上会导出当前服务器下的分发、发布、订阅，如果在 Generate Scripts 跳出的界面勾选了 distributor properties 则会导出分发；勾选了 publications in the following data sources 则会导出发布；如果勾选了 subscriptions in the following data sources 则会导出订阅。
28、监控发布订阅是否有异常，在发布端执行以下 5 条语句即可
select * from [distribution].[dbo].[MSlogreader_history] WHERE error_id != 0 AND [time] = DATEADD(HOUR, -1, GETDATE())
select * from [distribution].[dbo].[MSdistribution_history] WHERE error_id != 0 AND [time] = DATEADD(HOUR, -1, GETDATE())
select * from [distribution].[dbo].[MSsnapshot_history] WHERE error_id != 0 AND [time] = DATEADD(HOUR, -1, GETDATE())
select * from [distribution].[dbo].MSrepl_errors order by 2 desc
select * from msdb.dbo.sysreplicationalerts order by 7 desc
29、查询某个发布 XX，发布的数据库对象的 2 种方法
29.1、发布数据库上执行(数据来源这三张表，distribution.dbo.MSpublications 视图查询发布名、
distribution.dbo.MSarticles 视图查询发布的对象比如表、sysarticlecolumns 视图查询发布的表发布了哪些字段)
select a.article,a.source_object,a.destination_object,b.colid from
(select article,article_id,source_object,destination_object
from [distribution].[dbo].MSarticles where publication_id in
(select publication_id from
[distribution].[dbo].MSpublications where publication= XX
)
) a
inner join
(select * from dbo.sysarticlecolumns) b
on a.article_id=b.artid order by a.article
29.2、订阅数据库上执行
select distinct article from MSreplication_objects where publication= XX
30、某个发布 XX 丢失了，但是请求订阅还在的处理方法
订阅端执行如下，找到发布的数据库对象，再在发布端创建发布，再重建订阅，方法 2 可行
select distinct article from MSreplication_objects where publication= XX
如下方法不行
把发布数据库恢复到丢失以前看能不能找回发布信息, 发现发布数据库执行如下报错
select * from sysarticlecolumns
Invalid object name sysarticlecolumns .
31、发布订阅需要新增一张表时，只需要右键发布名称选择 articles 项目在里面勾选需要新增的表即可，再右键发布名称 –view snapshot agent status–start 即可，在发布端的日志读取 job(右键发布名称 –view log reader agent status)正常运行的情况下，订阅 job(右键订阅名称 –view sysnchronization status)也正常运行的情况，订阅数据库上可以马上看到新增的表
32、SSMS 图形界面，新建发布到第四步 articles 项目时或已经存在的发布右键选择 articles 项目时，界面很慢一直无法正常显示数据库对象比如表视图存储过程等，说明有堵塞，SSMS 点开一些界面其实就是一个 select 的动作，找到堵塞源杀掉后，就可以正常显示了
33、实例 –replication–Local Subscriptions– 订阅名称 – 右键 –View Synchronization Status 报错：An error occurred connecting to server XX .SQL Server repliaction requires the actual server name to nake a connection to the server.
原因：可能修改了计算机名，SSMS 连接的是新的计算机名，但是数据库没有修改 Servername，导致这个报错，SSMS 连接使用老的计算机名或修改数据库修改 Servername 为新的计算机名即可
34、A 服务器 ADB1 库的数据做了 replication 到 B 服务器的 ADB1 数据库，A 服务器和 A1 服务器搭建了 AG，并把 ADB1 加入了 AG，但是 A1 上的 AG 对应的数据库 ADB1 不正常，右键 A 服务器 ADB1 对应的发布名称 –View Log Reader Agent Status 发现 ADB1 的发布报错：Replicated transaction are waiting for next log backup or for mirror partner to catch up(复制的事务正等待下一次日志备份或等待镜像伙伴更新)
解决方法
方法 1、确保 A1 的 AG 中 ADB1 正常(首选方法)
方法 2、在 A 服务器上把该数据库 ADB1 从 AG 中移除
方法 3、在 A 服务器上执行
DBCC TRACEON (1448, -1) – 不希望 replication 受到 alwayson 其他 node 的影响
原因：
我们知道 always on 的辅助副本 secondary 是获取了 primary 的日志信息而进行的 redo 动作实现了数据库的同步工作。当 secondary 异常宕机后，为了保证 secondary 起来时能够继续上次读取日志的地方做 redo 动作，db 的 transaction log 就不会进行备份，而一直增大，最坏的情况导致磁盘爆满，无法使用。如当前 01 为 primary，02 为 secondary，为了避免 failover 后，02 切换为主副本，而此节点却没有获取到和 01 一样新的 replication 信息，导致异常。所以所有的 always on node 都要知道同步的情况，否则不继续进行同步。即所有 node 都获取 replication 信息后，才允许 replication 继续进行
35、SSMS 对 DB1 创建发布时报错：this database is not enabled for publication 不允许此数据库用于发布。(Microsoft SQL Server，错误: 14013)
手工执行对 DB1 创建发布
USE master
EXEC sp_replicationdboption @dbname = DB1 ,@optname = publish ,@value = true
GO
报错
链接服务器 repl_distributor 的 OLE DB 访问接口 SQLNCLI 返回了消息 Login timeout expired。
链接服务器 repl_distributor 的 OLE DB 访问接口 SQLNCLI 返回了消息 An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.。
该实例是 Enterprise 企业版，不是 Express 开发版，可以创建发布，所以不是版本问题
原因：发现存在一个名为 repl_distributor 的 linked server，且无法删除该 linked server，查询该 linked server 发现它已经用于发布, 即 master.sys.servers.is_distributor=1
解决方法：USE master;
EXEC sp_serveroption repl_distributor , dist , false
36、错误号：18483
could not connect to server XX because distributor_admin is not defined as a remote login at the server
未能连接到 XX 服务器，因为 distributor_admin 未在该服务器上定义为远程登录
Could not connect to server XX because YY is not defined as a remote server.
未能连接到 XX 服务器，因为 YY 没有定义为远程服务器
原因：hostname 和 servername 不一致导致，hostname 是 XX，但是 servername 是 YY，这时候不能通过图形界面来完成创建分发服务器的操作
解决方法：使用图形界面向导，最后选择不配置分发，而是生成脚本，使用脚本的代码，修改脚本中代码，把 XX 修改为 YY 即可
37、replication 到不同的 schema，比如 A 库的 dbo.table1 可以复制到 B 库的 repl.table1, 不过无法使用图形界面来操作，需要使用脚本来操作，当然也可以使用图形界面来操作但是最后不点确定，而是从图形界面导出脚本，再修改脚本中的 schema 信息，如下案例把 dbo 这个 schema 的表 base 复制到 repl 这个 schema 下:
use [PatternRecDB]
exec sp_addarticle @publication = N PatternRecDB_Base , @article = N Base , @source_owner = N dbo , @source_object = N Base , @type = N logbased , @description = N , @creation_script = N , @pre_creation_cmd = N truncate , @schema_option = 0x000000000803509F, @identityrangemanagementoption = N manual , @destination_table = N Base , @destination_owner = N repl , @status = 24, @vertical_partition = N false , @ins_cmd = N CALL [sp_MSins_dboBase] , @del_cmd = N CALL [sp_MSdel_dboBase] , @upd_cmd = N SCALL [sp_MSupd_dboBase]
GO
38、修改数据库名报错 can not rename the database name because it is published or it is a distribution database，解决方法:sp_removedbreplication @dbname=XXX
39、修改 servername 报错 There are still remote logins or linked logins for the server DBMASTER
解决思路
39.1、查看哪些用户远程连接了 DBMASTER 服务器
sp_helpremotelogin DBMASTER
server local_user_name remote_user_name options
DBMASTER distributor_admin distributor_admin
39.2、删除远程连接，继续出现的错误表示该服务器是订阅复制的发布服务器
exec sp_dropserver DBMASTER , @droplogins = droplogins
继续报错 Cannot drop server DBMASTER because it is used as a Publisher in replication.
39.3、删除订阅复制的发布信息
sp_dropdistpublisher @publisher = DBMASTER
继续报错 Cannot drop the local distribution Publisher because there are Subscribers defined.
39.4、删除订阅信息
sp_dropdistributor
继续报错 Could not drop the Distributor DBMASTER . This Distributor has associated distribution databases.
39.5、最后删除订阅信息再重新把 DBMASTER 修改为 DBMASTERNEW，成功
EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
exec sp_dropserver DBMASTER , @droplogins = droplogins
sp_addserver DBMASTERNEW , local
建立分发
复制 - 右键 - 配置分发
1、选择分发服务器
2、选择 snapshot 目录
3、创建分发数据库(数据库名称默认为 distribution、数据库文件目录、数据库日志名称)
4、选择哪些发布服务器可以使用这个分发服务器，enable servers to use this distributor when they become publishers，其中 distribution database 没得选，就是分发的数据库，默认是 distribution
– 如果上面第四步勾选了 publisher 和 distribution database，右键 Replicattion 有 Publisher Properties、Distributor Properties、Disabled Publishing and Distribution Wizard。如果不勾选，右键 Replicattion 则没有 Publisher Properties，有 Distributor Properties、Disabled Publishing and Distribution Wizard
分发建立好后，有如下 job
1、Agent history clean up: distribution
2、Distribution clean up: distribution
3、Expired subscription clean up
4、Monitor and sync replication agent jobs
5、Reinitialize subscriptions having data validation failures
6、Replication agents checkup
7、Replication monitoring refresher for distribution.
建立发布
复制 - 本地发布 - 右键 - 新建发布
建立发布 1：还没有分发时，建立发布的同时建立分发(虽然没有了上面 建立分发 的第 3、4 步，但是也默认创建了数据库 distribution)
右键 local publications(本地发布)– 选择 new publication(新的发布)
1、选择分发服务器
2、选择 snapshot 目录
3、选择要发布的数据库
4、选择发布类型
5、选择要发布的对象，比如表、视图、存储过程
6、选择 snapshot 代理，snapshot 是立即创建还是定期创建，snapshot 代理的安全性是使用用户名密码还是使用 sqlserver agent 服务，snapshot 代理怎么连接发布服务器，是 OS 域帐户还是 DB 帐户
7、创建发布名称
发布建立好后，有如下 job
1、Agent history clean up: distribution
2、Distribution clean up: distribution
3、Expired subscription clean up
4、Monitor and sync replication agent jobs
5、Reinitialize subscriptions having data validation failures
6、Replication agents checkup
7、Replication monitoring refresher for distribution.
8、WONCNTESTDB1-replicate1-1
9、WONCNTESTDB1-replicate1-pub_replicate1-1
建立发布 2：已经建立了分发时，只建立发布
1、选择要发布的数据库
2、选择发布类型
3、选择要发布的对象，比如表、视图、存储过程
4、选择 snapshot 代理，snapshot 是立即创建还是定期创建，snapshot 代理的安全性是使用用户名密码还是使用 sqlserver agent 服务，snapshot 代理怎么连接发布服务器，是 OS 域帐户还是 DB 帐户
5、创建发布名称
发布建立好后，有如下 job
1、WONCNTESTDB1-replicate1-1
2、WONCNTESTDB1-replicate1-pub_replicate1-1
建立订阅
1、选择发布服务器，选择发布
2、选择推送订阅还是请求订阅
3、选择订阅服务器，选择订阅数据库
4、选择订阅代理服务器，分发代理使用怎么连接, 是 OS 域帐户还是 DB 帐户, 分发服务器使用怎么连接, 是 OS 域帐户还是 DB 帐户，订阅服务器使用怎么连接, 是 OS 域帐户还是 DB 帐户
5、订阅同步是持续性还是按需求
6、是否马上初始化订阅对象
订阅建立好后，有如下 job，此 job 名称的前半段和订阅名称一样即 WONCNTESTDB1-replicate1-pub_replicate1
WONCNTESTDB1-replicate1-pub_replicate1-WONCNTESTDB2-replicate_01-CD7A365E-2DE7-47A3-B31E-70F785FA71F2
到此，相信大家对“sqlserver 关于发布订阅 replication_subscription 的知识点有哪些”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
