~楓花雪岳~: [SQL] 交易式複寫-設定發行

該筆記延續 [SQL] 交易式複寫-設定資料庫權限，根據教學課程：設定兩個完全連線的伺服器之間的複寫 (異動) 文章來設定散發，該文章作法是利用複寫精靈來設定發行集

建立發行集並定義發行項

SSMS => 複寫 => 本機發行集 => 滑鼠右鍵 => 新增發行集

發行集精靈說明

設定發行集資料庫，已事先還原 AdventrueWorks2019 至 Node1 上

設定發行集類別，選擇 [交易式發行集]

發行項 (Articles)，選擇 Product Table 並排除 StandardCost 和 ListPrice 欄位

篩選資料表的資料列，可以設定資料條件，只發行符合條件的資料

篩選條件：SafeStockLevel < 500 的資料

篩選條件設定完成

快照集代理程式，根據 Techdays Taiwan 2013 - 跨洲際多點同時服務的資料庫解決方案，使用SQL Server 點對點的資料同步內，老師說明大型資料庫都是透過備份來進行快照集初始化，但這篇練習是使用 [立即建立快照集，並保留快照集為可使用狀態，以初始化訂閱] 選項來進行

代理程式安全，選擇 Snapshot Agent 和 LogReader Agent 帳號，並取消 [使用快照集代理程式的安全性設定]

以 LogReader Agnet 設定為例說明， Snapsot Agent 就省略囉

設定結束後進行動作，順道產生 Script 來了解

設定產生 Script 的位置

發行集精靈設定完成

SQL Server Agent 內會多出兩個 Job，分別為

NODE1\SQL2019-AdventureWorks2019-1：LogReader 使用
NODE1\SQL2019-AdventureWorks2019-Advworksproducttrans-1：Snapshot 使用

精靈產生的 Script 檔案

-- 正在啟用複寫資料庫
use master

-- 在 AdventureWorks2019 上啟用交易式複寫
exec sp_replicationdboption 
	@dbname = N'AdventureWorks2019', 
	-- optname 參數為 merge publish、publish、subscribe 和 sync with backup
	@optname = N'publish', 
	@value = N'true'
GO

-- 執行 sp_addlogreader_agent 來建立 Agent Job
exec [AdventureWorks2019].sys.sp_addlogreader_agent 
	@job_login = N'Node1\repl_logreader', 
	@job_password = null, 
	-- 預設使用 Windows 整合驗證連發行者
	@publisher_security_mode = 1
GO

-- 建立交易式發行集
use [AdventureWorks2019]
exec sp_addpublication 
	@publication = N'Advworksproducttrans', 
	@description = N'來自發行者 ''NODE1\SQL2019'' 的資料庫 ''AdventureWorks2019'' 交易式發行集。', 
	@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, 
	@ftp_login = N'anonymous', 
	@allow_subscription_copy = N'false', 
	@add_to_active_directory = N'false', 
	-- continuous 和 snapshot，continuous 為預設值
	@repl_freq = N'continuous', 
	@status = N'active', 
	@independent_agent = N'true', 
	@immediate_sync = N'true', 
	@allow_sync_tran = N'false', 
	@autogen_sync_procs = 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'Advworksproducttrans', 
	@frequency_type = 1, 
	@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 = 0, 
	@active_end_date = 0, 
	@job_login = N'Node1\repl_snapshot', 
	@job_password = null, 
	-- 預設使用 Windows 整合驗證連發行者
	@publisher_security_mode = 1

-- 將 SQL Login 加入發行集存取清單 (PAL)
exec sp_grant_publication_access @publication = N'Advworksproducttrans', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'Advworksproducttrans', @login = N'NODE1\Administrator'
GO
exec sp_grant_publication_access @publication = N'Advworksproducttrans', @login = N'NT SERVICE\Winmgmt'
GO
exec sp_grant_publication_access @publication = N'Advworksproducttrans', @login = N'NT SERVICE\SQLWriter'
GO
exec sp_grant_publication_access @publication = N'Advworksproducttrans', @login = N'NT Service\MSSQL$SQL2019'
GO
exec sp_grant_publication_access @publication = N'Advworksproducttrans', @login = N'NT SERVICE\SQLAgent$SQL2019'
GO

exec sp_grant_publication_access 
	@publication = N'Advworksproducttrans', 
	@login = N'distributor_admin'
GO

-- 加入交易式發行項
use [AdventureWorks2019]
exec sp_addarticle 
	@publication = N'Advworksproducttrans', 
	@article = N'Product', 
	@source_owner = N'Production', 
	@source_object = N'Product', 
	@type = N'logbased', 
	@description = N'', 
	@creation_script = N'', 
	@pre_creation_cmd = N'drop', 
	@schema_option = 0x000000000803509F, 
	@identityrangemanagementoption = N'manual', 
	@destination_table = N'Product', 
	@destination_owner = N'Production', 
	@status = 24, 
	@vertical_partition = N'true', 
	@ins_cmd = N'CALL [sp_MSins_ProductionProduct]', 
	@del_cmd = N'CALL [sp_MSdel_ProductionProduct]', 
	@upd_cmd = N'SCALL [sp_MSupd_ProductionProduct]', 
	@filter_clause = N'[SafetyStockLevel] < 500'

-- 正在加入發行項的分割區資料行
exec sp_articlecolumn 
	@publication = N'Advworksproducttrans', 
	@article = N'Product', 
	@column = N'ProductID', 
	@operation = N'add', 
	@force_invalidate_snapshot = 1, 
	@force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'Name', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'ProductNumber', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'MakeFlag', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'FinishedGoodsFlag', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'Color', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'SafetyStockLevel', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'ReorderPoint', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'Size', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'SizeUnitMeasureCode', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'WeightUnitMeasureCode', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'Weight', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'DaysToManufacture', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'ProductLine', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'Class', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'Style', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'ProductSubcategoryID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'ProductModelID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'SellStartDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'SellEndDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'DiscontinuedDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'rowguid', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Advworksproducttrans', @article = N'Product', @column = N'ModifiedDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

-- 加入發行項篩選
exec sp_articlefilter 
	@publication = N'Advworksproducttrans', 
	@article = N'Product', 
	@filter_name = N'FLTR_Product_1__55', 
	@filter_clause = N'[SafetyStockLevel] < 500', 
	@force_invalidate_snapshot = 1, 
	@force_reinit_subscription = 1

-- 加入發行項同步處理物件
exec sp_articleview 
	@publication = N'Advworksproducttrans', 
	@article = N'Product', 
	@view_name = N'SYNC_Product_1__55', 
	@filter_clause = N'[SafetyStockLevel] < 500', 
	@force_invalidate_snapshot = 1, 
	@force_reinit_subscription = 1
GO

檢視快照集產生的狀態