在發行端建立快照，但不用在訂閱端初始化
在 Distributor 的目錄下會有用來建訂閱端資料表、預存程序…等物件的 Schema，若要手動建訂閱端的資料表或預存程序，可以用 .sch 檔的內容
以 .sch 檔案的內容，手動到訂閱端建立物件，自己完成第一次同步資料的內容，例如透過 linked server、bcp、ssis 等…，然後設定訂閱但不初始化
不要初始化，完成後就可以開始同步。
若刪掉訂閱，顯然精靈刪不乾淨用來做交易式複寫的預存程序，會留下 *_ccs 的預存程序
若要重新再做訂閱，但依然不要初始化，也不要動訂閱端既有的資料表。只需要把 .sch 檔案內建 table 的部分刪掉，執行建預存程序的部分，然後重新設定不初始化的訂閱，即可開始複寫。
由於未初始化，有部分資料不一致會導致複寫錯誤，可以參照以下的做法解決：檢視 Transactional Replication 執行某句命令所觸發的錯誤
也可參考另一種作法：交易式複寫從備份初始化，而非快照集
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
避免大量呼叫 sp 傳遞差異紀錄，可以先停掉 log reader/distributor job，執行批次動作，透過 sp_repldone、sp_repflush 清掉命令，在訂閱端執行相同大批次，然後再重新啟動 replication，複寫後許的動作。
說明可以參照這篇 blog
以下是我提供的範例
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 
–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
由於 Merge Replication 會對每筆記錄變更後，在 MSmerge_contents 系統資料表的 Generation 欄位記載著該筆記錄是在第幾次同步後更新，是同步前的舊值加一，若有衝突，大家彼此比 Generation，就知是否為最新的更新，還是太久沒上來同步的舊記錄。因此不憑更動的時間，也就沒有 UTC 時區對時的需求。你可以透過以下的語法觀察：
SELECT TOP 1000 * FROM [dbo].[MSmerge_contents]
我實際透過兩台 VM 設定不同的時區模擬測試，也的確沒有問題。
參照 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 欄位當作判斷條件：
會有以下的錯誤：
因為衝突解決取決於你自行維護的欄位，所以，或許在更新用來評斷的欄位時，全部要換算成一特定地點的時間。
雖然 Merge 機制本身不會被跨時區干擾，但你的資料時間內容會錯，可能需要採 DatetimeOffset 資料類型儲存時間資訊
SQL Server 線上說明：
獨立代理程式即服務一個訂閱的代理程式。共用的代理程式會服務多個訂閱；如果使用相同共用代理程式的多個訂閱需要同步，依預設，它們會在佇列中等候，該共用代理程式會一次服務其中之一。使用獨立代理程式會降低延遲，因為代理程式會在訂閱需要同步時就緒。合併式複寫通常使用獨立代理程式，依預設，交易式複寫會使用在「新增發行集精靈」中建立的發行集之獨立代理程式 (在舊版 SQL Server 中，依預設，交易式複寫則使用共用代理程式)。
sp_replicationdboption
independent_agent 是 nvarchar(5)，預設值是 FALSE。如果是 true，就表示這個發行集有獨立的散發代理程式。如果是 false，發行集會使用共用散發代理程式，每一組發行者資料庫/訂閱者資料庫都有單一共用代理程式。
所以，所以不同 Subscriber DB 就會不同的 Agent 了 😦
測試：
use [Northwind]
exec sp_replicationdboption @dbname = N’Northwind’, @optname = N’publish’, @value = N’true’
GO
–決定是否要採用獨立的散發代理程式
declare @independent nvarchar(5)=N’false’
— 正在加入交易式發行集
–如果將發行集的 ‘immediate_sync’ 屬性設定為 True，也必須將 ‘independent_agent’ 屬性設定為 True。發行集的 ‘allow_anonymous’ 屬性和 ‘immediate_sync’ 屬性相依。
use [Northwind]
exec sp_addpublication @publication = N’NwindCustomers’,
@description = N’來自發行者 “NBI7″ 的資料庫 “Northwind" 交易式發行集。’,
@sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’, @allow_pull = 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 = @independent, @immediate_sync = @independent, @allow_anonymous = @independent,
@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’
exec sp_addpublication_snapshot @publication = N’NwindCustomers’, @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
exec sp_addarticle @publication = N’NwindCustomers’, @article = N’Customers’, @source_owner = N’dbo’,
@source_object = N’Customers’, @type = N’logbased’, @description = null, @creation_script = null,
@pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509F, @identityrangemanagementoption = N’manual’,
@destination_table = N’Customers’, @destination_owner = N’dbo’, @vertical_partition = N’false’,
@ins_cmd = N’CALL sp_MSins_dboCustomers’, @del_cmd = N’CALL sp_MSdel_dboCustomers’, @upd_cmd = N’SCALL sp_MSupd_dboCustomers’
— 正在加入交易式發行集
exec sp_addpublication @publication = N’NwindCategory’, @description = N’來自發行者 “NBI7″ 的資料庫 “Northwind" 交易式發行集。’,
@sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’, @allow_pull = 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 = @independent, @immediate_sync = @independent, @allow_anonymous = @independent,
@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’NwindCategory’, @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 [Northwind]
exec sp_addarticle @publication = N’NwindCategory’, @article = N’Categories’, @source_owner = N’dbo’, @source_object = N’Categories’,
@type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509F,
@identityrangemanagementoption = N’manual’, @destination_table = N’Categories’, @destination_owner = N’dbo’, @vertical_partition = N’false’,
@ins_cmd = N’CALL sp_MSins_dboCategories’, @del_cmd = N’CALL sp_MSdel_dboCategories’, @upd_cmd = N’SCALL sp_MSupd_dboCategories’
GO
–加入訂閱
use [Northwind]
exec sp_addsubscription @publication = N’NwindCustomers’, @subscriber = N’NBI7′, @destination_db = N’test’,
@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’NwindCustomers’, @subscriber = N’NBI7′, @subscriber_db = N’test’,
@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 = 20110531, @active_end_date = 99991231,
@enabled_for_syncmgr = N’False’, @dts_package_location = N’Distributor’
exec sp_addsubscription @publication = N’NwindCategory’, @subscriber = N’NBI7′, @destination_db = N’test’,
@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’NwindCategory’, @subscriber = N’NBI7′, @subscriber_db = N’test’,
@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 = 20110531, @active_end_date = 99991231,
@enabled_for_syncmgr = N’False’, @dts_package_location = N’Distributor’
/* 清除物件
use [Northwind]
exec sp_dropsubscription @publication = N’NwindCustomers’, @subscriber = N’NBI7′, @destination_db = N’test’, @article = N’all’
exec sp_dropsubscription @publication = N’NwindCategory’, @subscriber = N’NBI7′, @destination_db = N’test’, @article = N’all’
exec sp_droppublication @publication = N’NwindCustomers’
exec sp_droppublication @publication = N’NwindCategory’
exec sp_replicationdboption @dbname = N’Northwind’, @optname = N’publish’, @value = N’false’
*/
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 系統資料庫取出該交易後的指令，重新傳到訂閱端同步：
可以在訂閱端資料庫測試 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’
可以知道訂閱端最後執行的交易：
看來，回復訂閱端是最為容易的…
先前做了Distribution DB 因故毀掉，但從備份回復的資料庫內容較舊之處理。
測試 Publication DB 因故毀掉，從備份回復的資料庫內容較舊的狀況，找不到好的解法…
從複寫監視器看到的錯誤訊息如下：
除了重新建立發行集外(這不是一切重來嗎？)，還有其他更好的解法嗎？
這 2005 就提供了，其優缺點可以參照 Books Online http://msdn.microsoft.com/en-us/library/ms151705(SQL.90).aspx
–要先設定發行集的"訂閱選項"中，允許從備份檔案初始化為 True
接著可執行以下的語法
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 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’
–為 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
–放棄既有的交易紀錄標示
–若執行失敗，錯誤訊息如下，要停掉 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
偶有所得 