sqlserver关于发布订阅replication_subscription的知识点有哪些

本篇内容主要讲解“sqlserver关于发布订阅replication_subscription的知识点有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“sqlserver关于发布订阅replication_subscription的知识点有哪些”吧!

成都创新互联客户idc服务中心，提供四川移动机房托管、成都服务器、成都主机托管、成都双线服务器等业务的一站式服务。通过各地的服务中心，我们向成都用户提供优质廉价的产品以及开放、透明、稳定、高性价比的服务，资深网络工程师在机房提供7*24小时标准级技术保障。

事务复制的工作机制

事务复制是由 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的知识点有哪些”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！