sqlserver關于發布訂閱replication_subscription的知識點有哪些

本篇內容主要講解“sqlserver關于發布訂閱replication_subscription的知識點有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“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的知識點有哪些”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！