啟動交易紀錄 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

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: