Tag Archives: Replication

啟動交易式複寫,但不初始化

在發行端建立快照,但不用在訂閱端初始化

image

在 Distributor 的目錄下會有用來建訂閱端資料表、預存程序…等物件的 Schema,若要手動建訂閱端的資料表或預存程序,可以用 .sch 檔的內容

image

以 .sch 檔案的內容,手動到訂閱端建立物件,自己完成第一次同步資料的內容,例如透過 linked server、bcp、ssis 等…,然後設定訂閱但不初始化

image

不要初始化,完成後就可以開始同步。

若刪掉訂閱,顯然精靈刪不乾淨用來做交易式複寫的預存程序,會留下 *_ccs 的預存程序

image

若要重新再做訂閱,但依然不要初始化,也不要動訂閱端既有的資料表。只需要把 .sch 檔案內建 table 的部分刪掉,執行建預存程序的部分,然後重新設定不初始化的訂閱,即可開始複寫。

由於未初始化,有部分資料不一致會導致複寫錯誤,可以參照以下的做法解決:檢視 Transactional Replication 執行某句命令所觸發的錯誤

也可參考另一種作法:交易式複寫從備份初始化,而非快照集

廣告

針對 Distribution 系統資料庫的 "清除散發: distribution" 作業停止,導致 Distrubution 過大

FB 上有朋友問:

How to resolve when SQL Distribution Database is growing huge (+80g)

檢查DB 有二個Table data 很大
Table MSrepl_commands 已經有 277440950 筆
Table MSrepl_transactions    已經有 80854199  筆

在散發 sql server有個 JOB 清除散發: distribution(Distribution clean up: distribution) 會 CLEAR Distribution Database,可是被關了。如果把job start,replication job 會執行很久,好像卡住。如何補救?

 

我建議的測試如下,但不知是否解了他的問題

declare @i int=365*24,@j int  –若保留了一年,一年之內批次慢慢刪,若不只一年,這個值再加大
while @i>73  — 預設是保留三天
begin
    set @j=@i-24
    EXEC dbo.sp_MSdistribution_cleanup @min_distretention = @j, @max_distretention = @i
    print @i/24
    waitfor delay ’00:00:10′ –留空給系統 run 其他的 replication
    set @i-=24
end

 

數日後,後續…

FB 友:

dbo.sp_MSdistribution_cleanup job 是在2013/6/9 就未執行,817*24=19608 H

–我執行以下command 時 EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 19607, @max_distretention = 19608–只執行clear up 1 h 時間data .

用 SELECT * FROM master..sysprocesses where blocked>0 檢查發現有許多 replication spid 被blocked. 完成無法執行.

請問老師可有其他方法?

 

我:

是否可以用 Job 排程,在較不忙的時候啟動,並記下最後執行到的時間,下次到排程時再啟動

接續清接下來的時程

 

FB 友:

MSrepl_commands table 可以用手動直接delete ? 有什麼方法?

因為這db 是24 h work , 沒有空閒時間.

 

我:

直接刪系統資料資料表,我怕會亂掉

alter database distribution set read_committed_snapshot on

這或許可以解 Lock 很嚴重的狀況…

 

FB 友:

另外問題,如何得知MSrepl_commands 已經多少沒清除了.

set read_committed_snapshot on

如果完成sp_MSdistribution_cleanup

需要SET 回read_committed_snapshot OFF 嗎?

 

我:

這會耗 tempdb,且我不知道有沒有其他的影響

MSrepl_commands 已經多少沒清除了,這我不確定,看起來真正刪除資料的是這個 sp_MSdelete_publisherdb_trans 預存程序內的以下語法:

    WHILE 1 = 1
    BEGIN
        if @has_immediate_sync = 0
            DELETE TOP(2000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands with (INDEX(ucMSrepl_commands)) where
                publisher_database_id = @publisher_database_id and
                xact_seqno <= @max_xact_seqno and
                (type & ~@snapshot_bit) not in (@directory_type, @alt_directory_type) and
                (type & ~@replpost_bit) <> @scriptexec_type
                OPTION (MAXDOP 1)
        else
            — Use nolock hint on subscription table to avoid deadlock
            — with snapshot agent.
            DELETE TOP(2000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands with (INDEX(ucMSrepl_commands)) where
                publisher_database_id = @publisher_database_id and
                xact_seqno <= @max_xact_seqno and
                — do not delete directory, alt directory or script exec commands. they are deleted
                — above. We have to do this because we use a (nolock) hint and we have to make sure we
                — don’t delete dir commands when the file has not been cleaned up in the code above. It’s
                — ok to delete snap commands that are out of retention and perform lazy delete of dir
                (type & ~@snapshot_bit) not in (@directory_type, @alt_directory_type) and
                (type & ~@replpost_bit) <> @scriptexec_type and
                (
                    — Select the row if it is older than max retention.
                    xact_seqno <= @max_immediate_sync_seqno or
                    — Select the snap cmd if it is not for immediate_sync article
                    — We know the command is for immediate_sync publication if
                    — the snapshot tran include articles that has virtual
                    — subscritptions. (use subscritpion table to avoid join with
                    — article and publication table). We skip sync tokens because
                    — they are never pointed to by subscriptions…
                    (
                        (type & @snapshot_bit) <> 0 and
                        (type & ~@snapshot_bit) not in (@syncinit, @syncdone) and
                        not exists (select * from MSsubscriptions s with (nolock) where
                            s.publisher_database_id = @publisher_database_id and
                            s.article_id = MSrepl_commands.article_id and
                            s.subscriber_id < 0)
                    )
                )
                OPTION (MAXDOP 1)

        select @row_count = @@rowcount
        — Update output parameter
        select @num_commands = @num_commands + @row_count
   
        IF @row_count < 2000 — passed the result set.  We’re done
            BREAK
    END

以及它所呼叫的 sp_MSdelete_dodelete

CREATE PROCEDURE sp_MSdelete_dodelete
    @publisher_database_id int,
    @max_xact_seqno varbinary(16),
    @last_xact_seqno varbinary(16),
    @last_log_xact_seqno varbinary(16),
    @has_immediate_sync bit = 1
WITH RECOMPILE
as
begin
        declare @second_largest_log_xact_seqno varbinary(16)
        set @second_largest_log_xact_seqno = 0x0

        if @last_log_xact_seqno is not NULL
        begin
            –get the second largest xact_seqno among log entries
            select @second_largest_log_xact_seqno = max(xact_seqno)
            from MSrepl_transactions
            where publisher_database_id = @publisher_database_id
                and xact_id <> 0x0
                and xact_seqno < @last_log_xact_seqno

            if @second_largest_log_xact_seqno is NULL or substring(@second_largest_log_xact_seqno, 1, 10) <> substring(@last_log_xact_seqno, 1, 10)
            begin
                set @second_largest_log_xact_seqno = 0x0
            end
        end

       
        if @has_immediate_sync = 0
            delete TOP(5000) MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions with (INDEX(ucMSrepl_transactions)) where
                publisher_database_id = @publisher_database_id and
                xact_seqno <= @max_xact_seqno and
                xact_seqno <> @last_xact_seqno and
                xact_seqno <> @last_log_xact_seqno and
                xact_seqno <> @second_largest_log_xact_seqno –ensure at least two log entries are left, when there existed more than two log entries
                OPTION (MAXDOP 1)
        else
            delete TOP(5000) MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions with (INDEX(ucMSrepl_transactions)) where
                publisher_database_id = @publisher_database_id and
                xact_seqno <= @max_xact_seqno and
                xact_seqno <> @last_xact_seqno and
                xact_seqno <> @last_log_xact_seqno and 
                xact_seqno <> @second_largest_log_xact_seqno and –ensure at least two log entries are left, when there existed more than two log entries
                — use nolock to avoid deadlock
                not exists (select * from MSrepl_commands c with (nolock) where
                    c.publisher_database_id = @publisher_database_id and
                    c.xact_seqno = MSrepl_transactions.xact_seqno and
                    c.xact_seqno <= @max_xact_seqno)
            OPTION (MAXDOP 1)
end

啟動交易紀錄 Replication 後,希望大批次動作不要透過 replication 傳遞

避免大量呼叫 sp 傳遞差異紀錄,可以先停掉 log reader/distributor job,執行批次動作,透過 sp_repldone、sp_repflush 清掉命令,在訂閱端執行相同大批次,然後再重新啟動 replication,複寫後許的動作。

說明可以參照這篇 blog

http://blogs.msdn.com/b/chrissk/archive/2010/01/03/using-sp-repldone-to-mark-all-pending-transactions-as-having-been-replicated.aspx

以下是我提供的範例

create database db
create database db2
go
use db
go
create table t(pk int identity primary key,
c2 varchar(10),
c3 datetime2(3) default(sysdatetime()))
go

use master
exec sp_adddistributor @distributor = N’SQL2014′, @password = N"
GO
exec sp_adddistributiondb @database = N’distribution’, @data_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data’, @log_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data’, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

–建立交易式發行
use [distribution]
if (not exists (select * from sysobjects where name = ‘UIProperties’ and type = ‘U ‘))
    create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty(‘SnapshotFolder’, ‘user’, ‘dbo’, ‘table’, ‘UIProperties’, null, null)))
    EXEC sp_updateextendedproperty N’SnapshotFolder’, N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData’, ‘user’, dbo, ‘table’, ‘UIProperties’
else
    EXEC sp_addextendedproperty N’SnapshotFolder’, N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData’, ‘user’, dbo, ‘table’, ‘UIProperties’
GO

exec sp_adddistpublisher @publisher = N’SQL2014′, @distribution_db = N’distribution’, @security_mode = 1, @working_directory = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData’, @trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’
GO

use [db]
exec sp_replicationdboption @dbname = N’db’, @optname = N’publish’, @value = N’true’
GO
— 正在加入交易式發行集
use [db]
exec sp_addpublication @publication = N’testReplDone’, @description = N’來自發行者 “SQL2014″ 的資料庫 “db" 交易式發行集。’, @sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’, @allow_pull = N’true’, @allow_anonymous = N’true’, @enabled_for_internet = N’false’, @snapshot_in_defaultfolder = N’true’, @compress_snapshot = N’false’, @ftp_port = 21, @allow_subscription_copy = N’false’, @add_to_active_directory = N’false’, @repl_freq = N’continuous’, @status = N’active’, @independent_agent = N’true’, @immediate_sync = N’true’, @allow_sync_tran = N’false’, @allow_queued_tran = N’false’, @allow_dts = N’false’, @replicate_ddl = 1, @allow_initialize_from_backup = N’false’, @enabled_for_p2p = N’false’, @enabled_for_het_sub = N’false’
GO

exec sp_addpublication_snapshot @publication = N’testReplDone’, @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [db]
exec sp_addarticle @publication = N’testReplDone’, @article = N’t’, @source_owner = N’dbo’, @source_object = N’t’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509F, @identityrangemanagementoption = N’manual’, @destination_table = N’t’, @destination_owner = N’dbo’, @vertical_partition = N’false’, @ins_cmd = N’CALL sp_MSins_dbot’, @del_cmd = N’CALL sp_MSdel_dbot’, @upd_cmd = N’SCALL sp_MSupd_dbot’
GO

–建立訂閱

use [db]
exec sp_addsubscription @publication = N’testReplDone’, @subscriber = N’SQL2014′, @destination_db = N’db2′, @subscription_type = N’Push’, @sync_type = N’automatic’, @article = N’all’, @update_mode = N’read only’, @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N’testReplDone’, @subscriber = N’SQL2014′, @subscriber_db = N’db2′, @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20150824, @active_end_date = 99991231, @enabled_for_syncmgr = N’False’, @dts_package_location = N’Distributor’
GO

–建立初始化 snapshot
exec msdb.dbo.sp_start_job ‘SQL2014-db-testReplDone-1’

–確認 repl 成功
insert db.dbo.t(c2) values(‘a’)

select * from db2.dbo.t

–停掉 log reader
exec msdb.dbo.sp_stop_job ‘SQL2014-db-1’

–停掉 distributor
exec msdb.dbo.sp_stop_job ‘SQL2014-db-testReplDone-SQL2014-3’

–insert publisher
insert t(c2) values(‘b’),(‘c’),(‘d’)

–  看有多少 repl 命令沒傳出去
exec  sp_replshowcmds
image

–tx log 記載著交易紀錄中還有多少 tx 沒複寫出去
DBCC OPENTRAN

–同時清空 repl 命令跟 tx log
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

–自行 run sp_repldone 後要執行 sp_replflush
exec sp_replflush

–可以再查 sp_replshowcmds 和 dbcc opentran,會發現沒有要複寫的指令,tx log 中也沒有記載某段 tx lsn 尚未 replicate

–到目的端做大批次動作
set identity_insert db2.dbo.t on
insert db2.dbo.t(pk,c2) values(2,’b’),(3,’c’),(4,’d’)
set identity_insert db2.dbo.t off

select * from db.dbo.t
select * from db2.dbo.t

–開始正常複寫
— log reader
exec msdb.dbo.sp_start_job ‘SQL2014-db-1’

— distributor
exec msdb.dbo.sp_start_job ‘SQL2014-db-testReplDone-SQL2014-3’

–複寫依然正常
delete db.dbo.t where pk=3

select * from db2.dbo.t

交易式複寫:替已經有訂閱的發行集加發行項的 Script

今天有朋友問:新增了資料表發行項,而希望既有的訂閱者只需要加入該資料表,而不是重新完整初始化

可參閱我以前的 blog

交易式複寫:替已經有訂閱的發行集加發行項

感謝小妙提供她的 T-SQL Script

–卸除
SET ANSI_PADDING ON
GO
use [db]
declare @publicationName sysname=N’dbRep_SchemaName’
declare @articleName sysname=N’tblDept’

–卸除對於發行者的特定發行項、發行集或訂閱集的訂閱
exec sp_dropsubscription @publication=@publicationName, @article=@articleName, @subscriber=N’all’

–從快照式或交易式發行集中卸除發行項
exec sp_droparticle @publication=@publicationName, @article =@articleName, @force_invalidate_snapshot = 1
GO

–加入
use [db]
declare @publicationName sysname=N’dbRep_SchemaName’
declare @articleName sysname=N’tblDept’
declare @tableName sysname=N’tblDept’
declare @schemaName sysname=N’SchemaName’
declare @subscriberName sysname=N’SQLInstance’
declare @destinationDbName sysname=N’db’
–建立一個發行項,將它加入發行集中
exec sp_addarticle @publication = @publicationName, @article = @articleName,
@source_owner = @schemaName, @source_object = @tableName , @type = N’logbased’,
@description = null, @creation_script = null, @pre_creation_cmd = N’drop’,
@schema_option = 0x0000004008037CDF, @force_invalidate_snapshot = 1,
@identityrangemanagementoption = N’manual’,
@destination_table = @tableName, @destination_owner = @schemaName,
@vertical_partition = N’false’

EXEC sp_addsubscription
@publication = @publicationName,
@article = @articleName,
@subscriber = @subscriberName,
@destination_db = @destinationDbName,
@reserved= N’Internal’

–用來啟動產生發行集初始快照集的快照集代理程式作業
exec sys.sp_startpublication_snapshot @publication =@publicationName

不同時區對 Merge Replication 的影響

由於 Merge Replication 會對每筆記錄變更後,在 MSmerge_contents 系統資料表的 Generation 欄位記載著該筆記錄是在第幾次同步後更新,是同步前的舊值加一,若有衝突,大家彼此比 Generation,就知是否為最新的更新,還是太久沒上來同步的舊記錄。因此不憑更動的時間,也就沒有 UTC 時區對時的需求。你可以透過以下的語法觀察:

SELECT TOP 1000 *   FROM [dbo].[MSmerge_contents]

image

我實際透過兩台 VM 設定不同的時區模擬測試,也的確沒有問題。

image

參照 http://msdn.microsoft.com/en-gb/library/aa256294(SQL.80).aspx

SQL Server then adds several system tables to the database to support data tracking, efficient synchronization, and conflict detection, resolution and reporting. For every changed or created row, the table MSmerge_contents contains the generation in which the most recent modification occurred. It also contains the version of the row as a whole and every attribute of the row. MSmerge_tombstone stores DELETEs to the data within a publication. These tables use the rowguid column to join to the publishing table.

The generation column in these tables acts as a logical clock indicating when a row was last updated at a given site. Actual datetime values are not used for marking when changes occur, or deciding conflicts, and there is no dependence on synchronized clocks between sites. This makes the conflict detection and resolution algorithms more resilient to time zone differences and differences between physical clocks on multiple servers. At a given site, the generation numbers correspond to the order in which changes were performed by the Merge Agent or by a user at that site.

MSmerge_genhistory and MSmerge_replinfo allow SQL Server to determine the generations that need to be sent with each merge.

 

但若要以時間因素判斷衝突解決,可能會有問題,因為既有的 COM 衝突解決模組都並未支援 DatetimeOffset 類型的資料欄位,參照:http://msdn.microsoft.com/en-us/library/ms152573.aspx

若使用 DatetimeOffset 欄位當作判斷條件:

image

會有以下的錯誤:

image

因為衝突解決取決於你自行維護的欄位,所以,或許在更新用來評斷的欄位時,全部要換算成一特定地點的時間。

雖然 Merge 機制本身不會被跨時區干擾,但你的資料時間內容會錯,可能需要採 DatetimeOffset 資料類型儲存時間資訊

SQL 2000 的 Replication 要用電腦名稱設,避免 IP

古代的事…都忘了 😦

Subscription DB 因故毀掉,但從備份回復的資料庫內容較舊

Distribution Agent 會自動解決這個狀況,從 Profiler 可以截取到許多自我檢測、重起的動作

實驗如下:

select * from northwind.dbo.t2

backup database d to disk=’c:tempd.bak’ with init

insert Northwind.dbo.t2(c1,c2) values(1234,1234)
insert Northwind.dbo.t2(c1,c2) values(5678,5678)

–確定訂閱端已經帶入新資料
select * from d.dbo.t2

–砍掉存取訂閱端資料庫的連接
select * from sysprocesses where dbid=DB_ID(‘d’)

–這導致 Distributeion Agent 在下一次要傳遞紀錄給訂閱端時
–因無法連接到目標端,而自動重起 Distribution Agent
kill 65

–故意模擬災難後,從備分回到從前
–但 Distribution 還保有交易資料
restore database d from disk=’c:tempd.bak’
with replace

select * from d.dbo.t2
–故意發生一個動作,要 Distribution Agent 傳遞
delete Northwind.dbo.t2 where c1=1234

–Distribution Agent 會自動檢測,而重新將某個 LSN 之後的語法重新到目的端執行
–所以一段時間後,自動會趨於一致
select * from d.dbo.t2

delete Northwind.dbo.t2 where c1=5678

透過 Profiler 可以錄到許多 Distribution Agent 重新初始化的動作,在此僅截取複寫發現錯誤後,先到 Subscription 端找尋其最後執行的交易,而後從 Distribution 系統資料庫取出該交易後的指令,重新傳到訂閱端同步:

image

可以在訂閱端資料庫測試 Profiler 錄下的查詢語法:

exec sp_executesql N’select hashid = case datalength(transaction_timestamp) when 16
then isnull(substring(transaction_timestamp, 16, 1), 0) else 0 end, transaction_timestamp,
subscription_guid from MSreplication_subscriptions
where UPPER(publisher) = UPPER(@P1) and publisher_db = @P2 and publication= @P3
and subscription_type = 0 ‘,N’@P1 nvarchar(10),@P2 nvarchar(9),@P3 nvarchar(4)’,N’P714P4USER’,N’Northwind’,N’test’

可以知道訂閱端最後執行的交易:

image

看來,回復訂閱端是最為容易的…

Publication DB 因故毀掉,但從備份回復的資料庫內容較舊

先前做了Distribution DB 因故毀掉,但從備份回復的資料庫內容較舊之處理。

測試 Publication DB 因故毀掉,從備份回復的資料庫內容較舊的狀況,找不到好的解法…

從複寫監視器看到的錯誤訊息如下:

image

除了重新建立發行集外(這不是一切重來嗎?),還有其他更好的解法嗎?

交易式複寫從備份初始化,而非快照集

這 2005 就提供了,其優缺點可以參照 Books Online http://msdn.microsoft.com/en-us/library/ms151705(SQL.90).aspx

–要先設定發行集的"訂閱選項"中,允許從備份檔案初始化為 True

image

接著可執行以下的語法

BACKUP DATABASE Northwind TO DISK=’C:tempnwind.bak’ WITH INIT
GO

RESTORE FILELISTONLY FROM DISK=’C:tempnwind.bak’
GO

RESTORE DATABASE nwindSubscribe FROM DISK=’C:tempnwind.bak’
WITH MOVE ‘northwind’ TO ‘C:tempnwind.mdf’,
MOVE ‘Northwind_log’ TO ‘C:tempnwind_log.ldf’
GO

USE Northwind
–增加對交易式發行集的 push 訂閱
–會同時增加作業
/*
作業 ‘BYRONSUB-Northwind-Customers-BYRONSUB-4’ 已成功啟動。
警告: distribution 代理程式作業已經由隱含方式建立,將於 SQL Server Agent 服務帳戶中執行。
*/
EXEC sp_addsubscription
  @publication = ‘Customers’,
  @subscriber = ‘Byronsub’,
  @destination_db = ‘nwindSubscribe’,
  @subscription_type = N’push’,
  @sync_type=’initialize with backup’,
  @backupdevicetype=’disk’,
  @backupdevicename=’C:tempnwind.bak’

Distribution DB 因故毀掉,但回復後內容較舊

以下做個實驗:

–建立個版本較舊的 Distribution DB Backup
BACKUP DATABASE Distribution TO DISK=’C:tempDistribution.bak’  WITH INIT

–停掉執行散發的 job,讓散發先不做
EXEC msdb.dbo.sp_stop_job @job_id=N’4b36094a-87bf-4223-964f-e065c97845bc’

INSERT Northwind.dbo.t2 VALUES(100,100)

–確認需要散發代理程式執行的命令已經在 Distribution DB
select a.agent_id,
a.UndelivCmdsInDistDB,
a.DelivCmdsInDistDB,
b.name,
b.publisher_db,
b.publication
from distribution.dbo.MSdistribution_status a
join distribution.dbo.MSdistribution_agents b
on a.agent_id=b.id
where
b.publication=’test’ and
b.name=
‘P714P4USER-Northwind-test-P714P4USER-14’

image

–為 Restore 做準備,刪掉所有連接在 Distribution DB 的連接
SELECT * FROM sys.sysprocesses WHERE dbid=DB_ID(‘Distribution’)
KILL 52

–讓 Disbribution DB 的版本較舊
RESTORE DATABASE Distribution FROM  DISK=’C:tempDistribution.bak’

–將發生 Publisher 已經標示某個 LSN 在 Distribution DB 已經寫入的命令,
–但 Distribution DB 卻紀錄沒有該筆的不一致行為
— 參考 http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&ProdVer=09.00&EvtSrc=MSSQLServer&EvtID=18768

image
–放棄既有的交易紀錄標示
–若執行失敗,錯誤訊息如下,要停掉 Log Reader Agent,也就是停掉 Agent service 對應的 Job
–訊息 18752,層級 16,狀態 2,程序 sp_repldone,行 1
–每次只有一個記錄讀取器代理程式或記錄檔相關程序 (sp_repldone、sp_replcmds 與 sp_replshowcmds) 可以連接到資料庫。如果您執行了記錄檔相關程序,請卸除用以執行程序的連接,或在啟動記錄讀取器代理程式之前在該連接執行 sp_replflush,或執行另一個記錄檔相關程序。
USE Northwind
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

–清掉原 DB 的 transaction log
backup log northwind to disk=’c:tempnwind.trn’ 

–做完後會一直有兩個 Log Reader
/*
該處理無法執行 ‘sp_repldone/sp_replcounters’ (在 ‘P714P4USER’)。 (來源: MSSQL_REPL,錯誤號碼: MSSQL_REPL20011)
取得說明: http://help/MSSQL_REPL20011
每次只有一個記錄讀取器代理程式或記錄檔相關程序 (sp_repldone、sp_replcmds 與 sp_replshowcmds) 可以連接到資料庫。如果您執行了記錄檔相關程序,請卸除用以執行程序的連接,或在啟動記錄讀取器代理程式之前在該連接執行 sp_replflush,或執行另一個記錄檔相關程序。 (來源: MSSQLServer,錯誤號碼: 18752)
取得說明: http://help/18752
*/
–要停掉執行本語法的連接,或
–重起 Agent Service/SQL Server,Replication 得以繼續進行,但資料不一致

–依然透過前一篇 Blog 的指令,求兩個資料表的一致
–tablediff -sourceserver . -sourcedatabase northwind -sourcetable t2 -sourceschema dbo -destinationserver . -destinationdatabase d -destinationtable t2 -destinationschema dbo -f C:temptabledifferencepeer.sql -o C:temptablediffoutput.txt

–確認 Replication 可以正常執行
DELETE Northwind.dbo.t2 WHERE c1=101
SELECT * FROM d.dbo.t2