[SQL Server][Replication]：複寫使用者正在插入至 NOT FOR REPLICATION 識別欄位時...遭遇錯誤

如果交易式複寫，可以成功的使用快照集初始化訂閱，但卻失敗於從備份初始化交易式訂閱。

有可能就是踩到 觸發程序(Trigger) 這個雷...


若已經確認在訂閱者端(Subscriber)的資料表上，有設定 NOT FOR REPLICATION。
接下來，請檢查資料表上是否有觸發程序(Trigger)。

若資料表上有觸發程序(Trigger)，請與 RD 討論其影響：

在單向的交易式複寫機制下，是否可以停用此觸發程序(Disable Trigger)，或是加入設定 NOT FOR REPLICATION 等。

遭遇的錯誤

-- 01_Error_NOT_FOR_REPLICATION

-- 02_在訂閱者端(Subscriber)，停用觸發程序(Trigger)

-- 03_複寫機制恢復正常運作

---

發生錯誤的情境

採取單向的交易式複寫機制，資料由發行者端(Publisher)複寫到訂閱者端(Subscriber)。但資料表的 IDENTITY_INSERT 屬性設定為 ON，因此需要明確提供此識別欄位(identity column)的資料值。


可能的原因

1. 在訂閱者端(Subscriber)的資料表，沒有設定 NOT FOR REPLICATION。
2. 在訂閱者端(Subscriber)的資料表，有設定 NOT FOR REPLICATION，但資料表上有使用者觸發程序(Trigger)。
3. 採用從備份初始化交易式訂閱(Initialize a Transactional Subscription from a Backup )，但資料表上有使用者觸發程序(Trigger)。

初步觀察 - 交易式複寫

1. 失敗：從備份初始化交易式訂閱
2. 成功：使用快照集初始化訂閱

---

錯誤訊息

Explicit value must be specified for identity column in table tablename either 
when IDENTITY_INSERT is set to ON or when a replication user is inserting 
into a NOT FOR REPLICATION identity column.

當 IDENTITY_INSERT 設定為 ON 或複寫使用者正在插入至 NOT FOR REPLICATION 
識別欄位時，必須為資料表 'xyz_log' 的識別欄位指定外顯值。

可行的解決方案
  
已知需求：採取單向的交易式複寫

1. 不採用從備份初始化交易式訂閱，改用快照集(Snapshot)方式來初始化複寫。
2. 確認在訂閱者端(Subscriber)的資料表上，需要設定 NOT FOR REPLICATION。
3. 若已確認在訂閱者端(Subscriber)的資料表上，已有設定 NOT FOR REPLICATION。
• 接下來，請檢查資料表上是否有觸發程序。
• 若有，請與RD其影響，確認是否可以停用、設定NOT FOR REPLICATION等。

-- 04_資料表設定：不可複寫

-- 05_資料表_NOT FOR REPLICATION

-- 06_在訂閱者端(Subscriber)，停用觸發程序(Trigger)

-- 07_發行項，預設：不包含「複製使用者觸發程序」

---

檢視遭遇的錯誤訊息

當 IDENTITY_INSERT 設定為 ON 或複寫使用者正在插入至 NOT FOR REPLICATION 
識別欄位時，必須為資料表 'xyz_log' 的識別欄位指定外顯值。

執行批次指令時發生錯誤，請嘗試個別執行指令。

正在初始化

嘗試執行的命令:
declare @event_type SMALLINT = 1, @distributor_major_version SMALLINT = 13, 
@distributor_minor_version SMALLINT = 0, @distributor_build_number SMALLINT = 4202,
 @totalruntime INT = 75094, @totalworktime INT = 0, @totalnumtrans INT = 0, 
@numtranspersec REAL = 0.00, @totalnumcmds INT = 0, @numcmdspersec REAL = 0.00, 
@totalskippedcmds INT = 0, @totalidletime INT = 0 
if object_id(N'sys.sp_repl_generate_subscriber_event') 
is not null exec sys.sp_repl_generate_subscriber_event @event_state = @event_type,@distribut

(交易序號: 0x0000004800000140004200000000，命令識別碼: 1)

錯誤訊息:
當 IDENTITY_INSERT 設定為 ON 或複寫使用者正在插入至 NOT FOR REPLICATION 
識別欄位時，必須為資料表 'xyz_log' 的識別欄位指定外顯值。 
(來源: MSSQLServer，錯誤號碼: 545)
取得說明: http://help/545

當 IDENTITY_INSERT 設定為 ON 或複寫使用者正在插入至 NOT FOR REPLICATION 
識別欄位時，必須為資料表 'xyz_log' 的識別欄位指定外顯值。 
(來源: MSSQLServer，錯誤號碼: 545)
取得說明: http://help/545

-- 08_Error_NOT_FOR_REPLICATION

-- 09_Error_NOT_FOR_REPLICATION

---

建立或修改觸發程序，加入 NOT FOR REPLICATION


NOT FOR REPLICATION
適用於： SQL Server 2008 至 SQL Server 2017。

範例：
ALTER TRIGGER [dbo].[Update_Stock] 
   ON  [dbo].[Orders]
   AFTER  INSERT
NOT for Replication — '標註不用於複寫'
...

---

參考資料

從備份初始化交易式訂閱 (複寫 Transact-SQL 程式設計)
https://msdn.microsoft.com/zh-tw/library/ms147834(v=sql.130).aspx

為交易式發行集啟用使用備份的初始化 (SQL Server Management Studio)
https://msdn.microsoft.com/zh-tw/library/ms152556(v=sql.130).aspx

複寫識別欄位
https://msdn.microsoft.com/zh-tw/library/ms152543.aspx

停用複寫的檢查條件約束
https://msdn.microsoft.com/zh-tw/library/ms190235.aspx

使用 NOT FOR REPLICATION 控制條件約束、識別和觸發程序
https://technet.microsoft.com/zh-tw/library/ms152529(v=sql.105).aspx

Marking Identity Columns as “Not For Replication” in existing Publication
EXEC sys.sp_identitycolumnforreplication
https://blogs.msdn.microsoft.com/repltalk/2012/03/05/marking-identity-columns-as-not-for-replication-in-existing-publication/

All about “Not for Replication”
https://blogs.msdn.microsoft.com/repltalk/2010/02/22/all-about-not-for-replication/

CREATE TRIGGER (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms189799.aspx

Problem with replicating identity column
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/12f48fb2-e5a0-445c-93ac-62c23cd294fe/problem-with-replicating-identity-column?forum=sqlreplication

使用快照集初始化訂閱
https://msdn.microsoft.com/zh-tw/library/ms151795.aspx

不使用快照集初始化交易式訂閱
https://msdn.microsoft.com/zh-tw/library/ms151705.aspx