文章會和各位分享在發行端的資料表新增欄位時 ,
訂閱端(也就是複寫的目的端會不會自動建立出新的欄位 ,
如果我不想讓該欄位同步到訂閱端我又該怎麼做???

正文開始 ↓↓↓
首先和各位報告下環境大概是這樣

發行端
SERVERNAME : NODE1
DBNAME : GAME

訂閱端
SERVERNAME : NODE2
DBNAME : GAME_REPORT

接著我來輪流建立2座資料庫的資料表 , 為了模擬實際情況 , 發行以及訂閱端的欄位數量我會弄得不一樣。

1. 建立資料表 , 交易式複寫Table要有PK , 發行我給5個欄位 , 訂閱我給3個欄位。

-- NODE1
USE [GAME]
GO

CREATE TABLE Ticket (
	ID INT PRIMARY KEY ,
	CreateTime DATETIME NOT NULL,
	Col1 VARCHAR(16) NOT NULL DEFAULT(''),
	Col2 VARCHAR(32) NOT NULL DEFAULT(''),
	Col3 VARCHAR(64) NOT NULL DEFAULT('')
)

-- NODE2
USE [GAME_REPORT]
GO

CREATE TABLE Ticket (
	ID INT PRIMARY KEY ,
	CreateTime DATETIME NOT NULL,
	Col1 VARCHAR(16) NOT NULL DEFAULT(''),
)

2. 建立發行集 , 重點在下圖 , 我只複寫勾起來的這三個欄位 , 產生出指令碼後執行(通常可以看到更詳細的結果訊息)。

use [GAME]
exec sp_replicationdboption @dbname = N'GAME', @optname = N'publish', @value = N'true'
GO
-- 正在加入交易式發行集
use [GAME]
exec sp_addpublication @publication = N'Ticket', @description = N'來自發行者 ''NODE1'' 的資料庫 ''GAME'' 交易式發行集。', @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'Ticket', @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 = 0, @publisher_login = N'高權限帳號', @publisher_password = N'高權限密碼'


use [GAME]
exec sp_addarticle @publication = N'Ticket', @article = N'Ticket', @source_owner = N'dbo', @source_object = N'Ticket', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Ticket', @destination_owner = N'dbo', @vertical_partition = N'true', @ins_cmd = N'CALL sp_MSins_dboTicket', @del_cmd = N'CALL sp_MSdel_dboTicket', @upd_cmd = N'SCALL sp_MSupd_dboTicket'

-- 正在加入發行項的分割區資料行
exec sp_articlecolumn @publication = N'Ticket', @article = N'Ticket', @column = N'ID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Ticket', @article = N'Ticket', @column = N'CreateTime', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'Ticket', @article = N'Ticket', @column = N'Col1', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

-- 正在加入發行項同步處理物件
exec sp_articleview @publication = N'Ticket', @article = N'Ticket', @view_name = N'SYNC_Ticket_1__54', @filter_clause = null, @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO

3. 建立訂閱 , 習慣上同步模式我選擇不初始化(自己手動先把2邊資料弄到一致) , 重點參數 @sync_type = N'replication support only' , 用UI產生指令碼後執行。

-----------------開始: 在發行者 'NODE1' 端執行的指令碼-----------------
use [GAME]
exec sp_addsubscription @publication = N'Ticket', @subscriber = N'NODE2', @destination_db = N'GAME_REPORT', @sync_type = N'replication support only', @subscription_type = N'pull', @update_mode = N'read only'
GO
-----------------結束: 在發行者 'NODE1' 端執行的指令碼-----------------

-----------------開始: 在訂閱者 'NODE2' 端執行的指令碼-----------------
use [GAME_REPORT]
exec sp_addpullsubscription @publisher = N'NODE1', @publication = N'Ticket', @publisher_db = N'GAME', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1

exec sp_addpullsubscription_agent @publisher = N'NODE1', @publisher_db = N'GAME', @publication = N'Ticket', @distributor = N'NODE2', @distributor_security_mode = 0, @distributor_login = N'高權限帳號', @distributor_password = '高權限密碼', @enabled_for_syncmgr = N'False', @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 = 20250702, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------結束: 在訂閱者 'NODE2' 端執行的指令碼-----------------

4. 寫入一筆資料後 , 確認複寫是正常傳遞指令的 , 到這裡就是基本的複寫建立而已 , 後面我要開始本篇的重點。

INSERT INTO [GAME].[dbo].[Ticket] VALUES (1, GETDATE(), 'A', '->', 'B')

SELECT * FROM [GAME].[dbo].[Ticket]
SELECT * FROM [SECONDARY].[GAME_REPORT].[dbo].[Ticket]

如果今天開一筆新的需求我要在 Ticket表上新增一個欄位 , 請問我在發行端新增欄位時 , 訂閱端會自動新增嗎?
若不會我要怎麼樣讓2邊的資料表同時新增欄位 , 不光是欄位要處理 , 複寫所使用到的任何SP(預存程序)也都要修改 , 否則傳遞的指令也會出錯。

5. 使用T-SQL查詢該複寫的系統表或是點開UI來查看複寫設定 , 這張系統表會記錄在這個DB內所使用到的發行表的基本屬性 , 重點欄位 replicate_ddl 的值為1表示同步資料結構至訂閱端 ,為0表示不同步資料結構, 在步驟2中執行這個SP時 exec sp_addpublication , 其中這個參數(@replicate_ddl)就已經出現過了 , 使用UI對發行集右鍵->屬性->訂閱選項->複寫結構描述變更顯示True就代表是同步。

-- NODE1
USE [GAME]
GO

SELECT  description, name, replicate_ddl
FROM syspublications
WHERE name = 'Ticket';

6. 驗證以上的設定 , 我現在在發行新增欄位 Col4 , 接著再寫入一筆資料 , 結果和預期的一樣 , 非常好。

-- NODE1
-- 新增欄位
ALTER TABLE [GAME].[dbo].[Ticket] ADD Col4 VARCHAR(128) NOT NULL DEFAULT('')

-- 寫入資料
INSERT INTO [GAME].[dbo].[Ticket] VALUES (2, GETDATE(), 'C', '->', 'D', '->')

-- 驗證
SELECT * FROM [GAME].[dbo].[Ticket]
SELECT * FROM [SECONDARY].[GAME_REPORT].[dbo].[Ticket]

7. 如果我今天新增欄位時不想同步資料結構 , 就把設定改成False , 或是執行以下sp也可以達到同樣效果。

EXEC sp_changepublication 
    @publication = N'Ticket', 
    @property = N'replicate_ddl', 
    @value = 0;

8. 此時我再對發行的資料表新增一個欄位 , 訂閱端就不會同步資料結構了。

-- NODE1
-- 新增 Col5欄位
ALTER TABLE [GAME].[dbo].[Ticket] ADD Col5 VARCHAR(128) NOT NULL DEFAULT('')

-- 驗證
SELECT * FROM [GAME].[dbo].[Ticket]
SELECT * FROM [SECONDARY].[GAME_REPORT].[dbo].[Ticket]

小結 :
使用到的系統預存程序 sp_changepublication , 可以自由操控資料結構是否同步 , 小小的一個設定背後會執行非常多script的修正 , 比如下圖的三支sp(在訂閱端的資料庫) , 交易式複寫主要就是依賴這三支sp在做資料的增刪修 , 下篇在和各位分享複寫架構下還可能遇到哪些比較大的問題 , 如有問題請直接指正 , 謝謝。